We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Cells above certain standard deviation formatted conditionally

asked 2018-10-13 19:58:50 +0200

Tobruk gravatar image

updated 2018-10-13 20:11:56 +0200

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)

image description

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-10-14 00:39:50 +0200

m.a.riosv gravatar image

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.

edit flag offensive delete link 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.

erAck gravatar imageerAck ( 2018-10-14 20:24:30 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-10-13 19:58:50 +0200

Seen: 547 times

Last updated: Oct 14 '18