Conditional formatting using multiple IF statements- I think??

[majandra22]

Hello guys,

I need help getting a cell to choose between different actions based on some data.

I have a row of 11 cells with numerical data, and in the 12th cell I already have a formula set to average the 11 previous cells.

However, if any of the previous cells contain a 0, 1, or -, I want the 12th cell to display "0" and turn the cell red rather than averaging the cells.

Basically, if anything in my test scored as 0, 1, or - it is eliminated, so I don't care about an average, "it flunked out".

I attached a graphic to better explain the situation.

I'd really appreciate any help with this, it would save me days of work manually converting the data.

average.PNG

[FlameRetired]

Hi majandra22.

Try this formula for the conditional averages in cell II6 and fill down.

Please Login or Register  to view this content.

Highlight / select the cells in column II to format. From Format Manager choose 'New Rule' > 'Format only cells that contain' and then choose the settings from the drop downs.

Check the attached.

[majandra22]

This is awesome Dave, thank you! Is there any way to add the '-' into the average formula in order to make it return a '0' as well? It seems to think I'm trying to make a math problem...

[FlameRetired]

Yes. Try

Please Login or Register  to view this content.

I couldn't tell in post #1 if - meant literal - or was understood to be negative numbers. I guessed wrong. My apologies.

[majandra22]

No worries, thank you so much for the help, you have no idea how much (monotonous) work you have saved me from!