Ranking by Conditional Formatting
 

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.

Ranking by Conditional Formatting
 

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

"Liz" wrote:

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.

Ranking by Conditional Formatting
 

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?


"Mike H" wrote:

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

"Liz" wrote:

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.

Ranking by Conditional Formatting
 

Liz,

That may be enough

=SUMPRODUCT(--(B1:B6A1: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

"Liz" wrote:

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?


"Mike H" wrote:

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

"Liz" wrote:

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.

Ranking by Conditional Formatting
 

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

"Mike H" wrote:

Liz,

That may be enough

=SUMPRODUCT(--(B1:B6A1: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

"Liz" wrote:

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?


"Mike H" wrote:

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

"Liz" wrote:

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.

Ranking by Conditional Formatting
 

Liz,

You add an extra condition to the formula I gave you

SUMPRODUCT((A1:A6="Store 1")*(C1:C6B1:B6))

In Practice I would use a cell reference

SUMPRODUCT((A1:A6=D1)*(C1:C6B1:B6))

Where D1 contained the name of the store.

Mike

"Liz" wrote:

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

"Mike H" wrote:

Liz,

That may be enough

=SUMPRODUCT(--(B1:B6A1: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

"Liz" wrote:

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?


"Mike H" wrote:

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

"Liz" wrote:

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.