# Null value IF?

#### Caleab

##### Board Regular
Sorry about the subject, not sure what else to call it.
I have a formula that works great for what I want to do, except I need it gives a #DIV/0! if there is no data. This is causing a problem because it's not averaging the scores over the months/quarters due to the #DIV/0!.
Is there a way around this on either end?

The formula I'm using is:
=AVERAGE(IF(ScoreData!\$F\$2:\$F\$1500=\$A3,IF(ScoreData!\$B\$2:\$B\$1500=F\$1,IF(ScoreData!\$H\$2:\$H\$1500,ScoreData!\$H\$2:\$H\$1500))))

where: A3=Team Member's Name
F1 = Month

Of course, if there is an easier way to do this or more information is needed, please let me know.

Thanks for all the help =)

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

=AVERAGE(IF(ScoreData!\$F\$2:\$F\$1500=\$A3,IF(ScoreData!\$B\$2:\$B\$1500=F\$1,IF(ScoreData!\$H\$2:\$H\$1500,ScoreData!\$H\$2:\$H\$1500,""))))

Not sure if this is exactly what you mean, but you can try this. I just added the <>"" to include only non blanks.

=AVERAGE(IF(ScoreData!\$F\$2:\$F\$1500=\$A3,IF(ScoreData!\$B\$2:\$B\$1500=F\$1,IF(ScoreData!\$H\$2:\$H\$1500<>"",ScoreData!\$H\$2:\$H\$1500))))

Tried both and both returned #DIV/0!.

Perhaps I'm going about it wrong.
There should only be one score for each team member for each month.
The original format was going to be using different kinds of scores (routine, training, practice, etc), but that's defunct now.

Is there an easier way to pull this data from the ScoreData table?
It really doesn't need to AVERAGE.

ScoreData worksheet basically is a simple 10 column data sheet.
A = Supervisor
B = Month
C = Year
D = QTR
E = Audit Type
F = Team Member
G = PreScore
H = Final Score
I = Difference
J = Skill Set

Each month the scores are logged for each team member, then I have seperate sheets that roll up by supervisor, department average, etc.
I'm now trying to create a sheet that has all the team members in Column A with the months broke out by quarters across the top. (pretty basic/simple format). I just need their scores to auto-populate.

Try:

=IF(SUMPRODUCT(--(ScoreData!\$F\$2:\$F\$1500=\$A3),--(ScoreData!\$B\$2:\$B\$1500=F\$1)),AVERAGE(IF(ScoreData!\$F\$2:\$F\$1500=\$A3,IF(ScoreData!\$B\$2:\$B\$1500=F\$1,IF(ScoreData!\$H\$2:\$H\$1500,ScoreData!\$H\$2:\$H\$1500,"")))),"")

Not sure if it is faster or slower, but here's another way:

=IF(ISNUMBER(MATCH(\$A3,ScoreData!\$F\$2:\$F\$1500,0))+ISNUMBER(MATCH(F\$1,ScoreData!\$B\$2:\$B\$1500,0))=2,AVERAGE(IF(ScoreData!\$F\$2:\$F\$1500=\$A3,IF(ScoreData!\$B\$2:\$B\$1500=F\$1,ScoreData!\$H\$2:\$H\$1500))),"")

Confirmed with CTRL+SHIFT+ENTER not just ENTER

gotta couple of meetings that will take me into this afternoon.
I'll try the suggestions and see what happens.

thanks!

Replies
5
Views
219
Replies
2
Views
149
Replies
2
Views
223
Replies
3
Views
279
Replies
1
Views
188

1,218,746
Messages
6,144,258
Members
450,533
Latest member
xoxo1998

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back