Conditional formatting with original data

[philro]

I"m trying to create a heat map in Excel by essentially finding the Z statistical value of normal distribution and coloring values far from the mean using conditional formatting. I calculate the Z as
=ABS(AI2-AVERAGE(AI$2:AI$71))/STDEV(AI$2:AI$71) in each cell from the derived data above.
The values in the heat map are generally between 0-3 as you would suspect. But the colored heat map is over the calculated Z values.
Can i have the colored cells with the original derived data not the Z values?

 

[rline101]

Hi there,
I hope this will do what you want. I think you just need to put the abs value formula in the conditional formatting itself.

Column A contains some numbers (generated by 10*rand() in each cell).
Column C is your Z formula applied to each cell in column A (purely to show that the highlighted z values are correct). You don't need anything in column C.

But the conditional formatting is applied to column $A$1:$A$10 as shown.

Formulas are

Red: =ABS(A1-AVERAGE(A$1:A$20))/STDEV(A$1:A$20)>=3
Orange: =AND(ABS(A1-AVERAGE(A$1:A$20))/STDEV(A$1:A$20)>=2,ABS(A1-AVERAGE(A$1:A$20))/STDEV(A$1:A$20)<3)
Yellow: =AND(ABS(A1-AVERAGE(A$1:A$20))/STDEV(A$1:A$20)>=1,ABS(A1-AVERAGE(A$1:A$20))/STDEV(A$1:A$20)<2)
Green: =AND(ABS(A1-AVERAGE(A$1:A$20))/STDEV(A$1:A$20)>=0,ABS(A1-AVERAGE(A$1:A$20))/STDEV(A$1:A$20)<1)

Apologies if this doesn't do what you wanted.

 

[philro]

I think it's a great answer. Thank you very much. I'll give it a try shortly. Very much appreciate your time.