Conditional formatting with IF/AND/OR (array's?)

[anghicole]

Hi!

Once again I have a question I'am not able to solve.

In my data set I want to format the cells that are below one standard deviation from the mean to "green", but NOT if the cell value is 0.
And I also want to format the celles that are above one standard deviation from the mean to "yellow". So if the cells are within one standard dev. in either direction they can stay white, and also if they are zero.

I attached an example of my workbook here. The bottom table I've used for trying out my formula for true/false. And I want the conditional formatting to apply for the midle table.

PS! there will never be negative values.


Thank you for any help

[martindwilson]

=E41*(IF(NOT(ISBLANK(E41)),IF(E41=0,E41)))=E41 array entered why is that? i cant see what it is doing
NOT(ISBLANK(E41)is the same as just E41<>""

[anghicole]

That didn't work :/

If you put multiple IF's in a forumla I thought you have to use array, or is that not always true?

I tried making one formula that formatted the cells if the cell value was zero, that worked. But when i added more rules, the rule which format the cells below one standard dev. from the mean green, also formatted the zeros to green. I tried mixing up the rule order in any different ways, but it didn't help :/
I thought the upper most rule was the one who should override?

Anyways, i figured if I put the "not zero" and the "below one standard dev." in the same formula I should be okey. The formula should look like this i think:
"Statement is true only if cell value is below one standard dev. from the mean (this is given in the sheet, row 31), but only if the cell value is NOT zero.

I have yet to figure out how to write this..

I've tried something like this: =D41(IF(D41<D$31,IF(NOT(D41=0),D41))) but it doesn't work :/

[martindwilson]

thats not true at all! you do not need to array enter IF
i think you need something like
=AND(D41<>"",D41<>0,D41<D$31)

[anghicole]

Okey, good to know thanks!

That formula worked perfectly well, thank you so much!

[Pepe Le Mokko]

FYI, although not relevant in this case, all CF are treated as array formula