# Solved: Ignoring blank cells in formula

Not open for further replies.

Joined:
Dec 1, 2009
Messages:
15
I made an Excel worksheet that needs to allow from 1 to 7 supervisors to enter info each month and average results. My sheet only works if all 7 supervisor colums have data entered. In addition, I need to have the average scrore updated monthly and show up in red if the average falls below 70%. That part I think I got, I just don't know how to create a formula to ignore the colums where no data is input. HELP PLEASE! I'm very new to Excel formulas. Thanks.

File size:
93 KB
Views:
76
2. ### The Villan

Joined:
Feb 20, 2006
Messages:
2,255
Try this

=IF(ISERROR(AVERAGE(E3:E12))," ",AVERAGE(E3:E12))

Joined:
Dec 1, 2009
Messages:
15
It only worked when data was filled in for the 1st supervisor. I entered data in the 2nd supv. column and the score didn't change. Also, the average score I created was 80% and Q16 turned red. (It's only supposed to show up red if the score is 70% or less.)

4. ### The Villan

Joined:
Feb 20, 2006
Messages:
2,255
Did you copy the formula across into the other cells?

=IF(ISERROR(AVERAGE(E3:E12))," ",AVERAGE(E3:E12))
=IF(ISERROR(AVERAGE(H3:H12))," ",AVERAGE(H3:H12))
=IF(ISERROR(AVERAGE(K3:K12))," ",AVERAGE(K3:K12))
etc etc

Joined:
Dec 1, 2009
Messages:
15
You're right...my bad, DUH! That worked GREAT! You're a Genius.

I also need to make cell #Q16 show up in Red if the average score is less than 70 percent. I've been trying all sorts of ways with Conditional Formatting and can't seem to get it to work now. Can you help me fix this?

Thanks,

6. ### The Villan

Joined:
Feb 20, 2006
Messages:
2,255
Select Q16 and then apply theses rules.

Cell Value <0.7 Format Red Applies to \$Q\$16

Joined:
Dec 1, 2009
Messages:
15
Thank you so much for your help! That worked great.

8. ### The Villan

Joined:
Feb 20, 2006
Messages:
2,255
You are very welcome.

As Seen On