Ranking by Conditional Formatting

  • Thread starter Thread starter Liz
  • Start date Start date
L

Liz

If I have a spreadsheet where I used colors for conditional formatting, how
would I rank those items. For example

store 1 green yellow yellow green
store 2 yellow green green green

I want to use a formulat that would tell me how many times the green
formatting is in store 1 and 2 and then perform a rank.
 
Liz,

Because the colour is a result of a conditional format it's not as
straightforward as it may seem and may require quite involved code.

We may be able to devise a formula if you tell us the CF conditions that
cause the cells to change colour.

Mike
 
There are 6 columns of data different conditional formatting based on the
goals below.

1st column 3.5 5.20
2nd column .5 1.00
3rd column 40% 61.54%
4th column 37% 42%
5th column 65% 93.3%
6th column 26% 31.5%

now store 1s results are directly above.

I need to rank multiple store results and I need to rank them by the number
of times they meet the goals. So for example, the store above would meet all
the goals. Does that make sense?
 
Liz,

That may be enough

=SUMPRODUCT(--(B1:B6>A1:A6))

Lets say the data you gave are in A1 - A6 & B1 - B6
The above formula returns 6 i.e. the number of time column B is > column A

Is that enough or is your actual data layout more complicated.

Mike
 
Let me try and clarify how my spreadsheet is set up

column a column B column c
store name store result goal

the store result and goal repeat 6 times for different criteria. How would I
get the formula to work in that instance?

Elizabeth
 
Liz,

You add an extra condition to the formula I gave you

SUMPRODUCT((A1:A6="Store 1")*(C1:C6>B1:B6))

In Practice I would use a cell reference

SUMPRODUCT((A1:A6=D1)*(C1:C6>B1:B6))

Where D1 contained the name of the store.

Mike
 
Back
Top