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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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,""))))
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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))))
 

Caleab

Board Regular
Joined
Jul 13, 2005
Messages
64

ADVERTISEMENT

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.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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,"")))),"")
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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
 

Caleab

Board Regular
Joined
Jul 13, 2005
Messages
64
gotta couple of meetings that will take me into this afternoon.
I'll try the suggestions and see what happens.

thanks!
 

Forum statistics

Threads
1,136,584
Messages
5,676,661
Members
419,638
Latest member
GlenMc52

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
Top