# Cells above certain standard deviation formatted conditionally

If certain cells are above the standard deviation, they should be formatted conditionally.

How to achieve this? I've seen in the conditional formatting an option "higher than the average", but that is not quite what I wanted, obviously.

Standard deviation would allow me to have some margin of error for higher and lower values, but marking those which are extremely higher or extremely lower too.

EDIT: I found the formula for standard deviation and applied it in conditional formatting as "Cell value more than" STDEVA(F3:F45) but not all cells were marked as expected (see cell with 0,99 1,74 and 1,02 respectively).

EDIT2: I checked the range and the last cell is F10, so that's not the root of the problem)

edit retag close merge delete

Sort by » oldest newest most voted

Seems you are not using absolute references, this carry that the range is adapted to every cell analyzed with the CF.

Please test with STDEVA($F$3:$F$45)

If it doesn't work please attach a sample file, editing your question.

more

I suggest to calculate =STDEVA($F$3:$F$45) only once in one formula cell and in the conditional format reference that cell instead. Otherwise the standard deviation will be unnecessarily calculated again and again for each and every conditional format.