Null value IF?

Caleab

Board Regular
Joined
Jul 13, 2005
Messages
64
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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Try adding a Value_If_False argument:

=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,""))))
 
Upvote 0
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))))
 
Upvote 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.
 
Upvote 0
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,"")))),"")
 
Upvote 0
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
 
Upvote 0
gotta couple of meetings that will take me into this afternoon.
I'll try the suggestions and see what happens.

thanks!
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top