Conditional Formatting Formula help

[danielleeck]

I would like to format a spreadsheet where a cell is highlighted if it is above or below the average for that column. But I would also like a value added to that so lets say I would like a cell highlighted if it is 30,000 dollars below or above the average for that column

 

[Faseeh]

Hi danielleeck,


Welcome to the forums. Try this, select Between rule from conditional formatting and set it to =AVERAGE(YourData)+30000 and =AVERAGE(YourData)-30000. Hopefully it is done.

 

[kaushik03]

danielleeck,


Welcome to the forum and we glad to have you here.


Suppose your data ranges from A2 to A6.


Open CF dialogue box, select "use a formula to determine which cells to format", write the below formula in the box (under "format value where this formula is true"):


=OR($A2>AVERAGE($A:$A),$A2<AVERAGE($A:$A))


set the format as you need....hit ok.


In apply to box set the range to apply the CF in all the cells as =$A$2:$A$6


Kaushik

 

[danielleeck]

Ok the first one didn't work and the second one I am confused lets say the column is z5 through z22 how would I write that?

 

[amrit1186]

Hi Daniel


Can you please try the below formula in CF.


If your data is in range A1:A10, use the following formula


=A1>=average(A1:A10)+30000

and

=A1<=average(A1:A10)-30000


Amritansh

 

[bobhc]

Good day danielleeck


If your range is Z5:Z22 then alter the ranges in the posted formulas to that, for example in kaushik03's code =OR($A2>AVERAGE($A:$A),$A2<AVERAGE($A:$A)) you would change it to =OR($Z2>AVERAGE($Z2:$Z22),$Z2<AVERAGE($Z2:$Z22))

 

[kaushik03]

danielleeck,


Please follow as advised by bobhc.


Let us know in case of any issue.


Kaushik