Multiple Conditional Formula Formating

[st471438]

Good morning everyone,

I have used this forum in past and this is a life saver site

I hope to get answer this time as well..........

So.. here what I am trying to do.. I am using Excel 2010 and I have average of performance that is in percentage example 18% on C3 and the rest of the column have individual's performance percentage and I want to apply 5 different conditions based on the average (18%) percentage so basically

So if the cell value is +1% then value of C3 (18%) then light green
If cell value is +2% of C3 = Green
If cell value is +3% of C3 = Dark green

If cell value is -1% of C3 = Yellow

and so on...

Here is the example of data

Name	OCt Sales	Nov Sales
Average	18%	17%
Sales Person 1	19%	16%
Sales Person 2	16%	18%
Sales Person 3	20%	15%

<tbody>
</tbody>

Please let me know what type of formula I should use? I tried using everything nothing is working for me.

I really appreciate everyone's help in this regards.

 

[Andrew Poulsom]

What conditional formatting formulas have you tried that don't work?

 

[st471438]

Hi Andrew,

I am kind of lost now.. I tried using their new conditional format option but not sure what should i put.. i tried the option where it says if this formula is true and in that formula box i put =C3+1% then GREEN... i tried that just to give a shot and all other option but i am lost.

 

[st471438]

Also there are options where we can change color of cells based on greater than cell reference etc but it's not about greater than, because its about +1,+2,+3 and -1,-2,-3% etc. Does this make sense?

 

[Andrew Poulsom]

Try simply:

=C3=1%

If your percentages are formatted to show no decimals rather than rounded, you may need:

=ROUND(C3,2)=1%

 

[st471438]

=C3=1%?

I want to see if value of cells is +1% of (C3) if value in cells is +2% of (C3) and so on.. so basically if C3 is 18% then if cell contains 19% then green.... 20% then dark green and so on..

 

[Andrew Poulsom]

Do you mean?

=(D3-$C3)=1%

 

[st471438]

No.

I apologize for the confusion and I am not able to explain. I will try again..

I want to apply conditional format to all the column based on value on Cell C3 = so in our example C3 is 18% right now so I want to see in entire column (A) if cell value is C3+1% (so basically it will be 18%+1% = 19%) then green (B) if cell value is C3+2% (it will be 18%+2% = 20%) then dark green

(C) if cell value is C3-1% (So 18%-1% = 16%) then Yellow

Does this make sense? Where should I enter the formula and what should I enter as formula?

I am so sorry for confusion but I really appreciate for your patience thank you in advance Sir

 

[Andrew Poulsom]

If your sample data is in the range B2:D6, select C3:D6 and try the conditional formatting formulas:

=ROUND(C4-C$3,2)=1%
=ROUND(C4-C$3,2)=2%
etc

In the Conditional Formatting New Formatting dialog choose 'Use a formula to determine which cells to format' and enter the formula in the box below. Here's a short tutorial:

Format Entire Row based on cell value

 

[st471438]

Thank you very much.. My data is in entire column. On top of each column have average of each column and that average will be base of the formating for the entire column.

SO I should choose the entire column (except the base reference value) right? and add coma sign (,) between different formula is it correct?