Complicated formula... can't get rid of DIV/0! Error.

[Idaho Cowboy]

Howdy again. Glad I found this site.

Anyway, stats class... trying to get some averages dependent on a lot of variables. I don't know how to copy excel onto the forum, so will do my best to explain.

I have 4 columns of data

Wages, Industry, Occupation, Experience

I have 100 entries in my sample.

I am trying to get wage averages based on 0-15 years experience, and over 16 years experience, by industry and occupation.

I got this formula to work for me, except for a few cells which continue to give me DIV/0 error.

{=AVERAGEIFS($A$2:$A$101,$D$2:$D$101,"<16",$B$2:$B$101,L47,$C$2:$C$101,M47)}

This formula is for all entries that have less than 16 years experience, and meet the criteria in my frequency table for each Industry and Occupation

B column is Industry. C column is Occupation, A is wages. D is Experience.

CNTR+SHIFT+ENTER was used.

The cells in question, giving me the error are:
1)
<table style="width: 556px; height: 36px;" border="0" cellpadding="0" cellspacing="0"><col width="64"><col width="124"><col width="106"><col width="137"><tbody><tr height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" align="right" height="20" width="64">49974</td> <td class="xl65" style="width: 93pt;" width="124"> Manufacturing</td> <td class="xl65" style="width: 80pt;" width="106"> Management</td> <td style="vertical-align: top;">
</td><td class="xl65" style="width: 103pt;" align="right" width="137">26</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr></tbody></table>
2)
<table border="0" cellpadding="0" cellspacing="0" width="431"><col width="64"><col width="124"><col width="106"><col width="137"><tbody><tr height="20"> <td style="height: 15pt; width: 48pt;" align="right" height="20" width="64">33411</td> <td style="width: 93pt;" width="124">Manufacturing</td> <td style="width: 80pt;" width="106">Sales</td> <td style="width: 103pt;" align="right" width="137">20</td> </tr></tbody></table>
3)
<table border="0" cellpadding="0" cellspacing="0" width="431"><col style="width: 48pt;" width="64"> <col style="width: 93pt;" width="124"> <col style="width: 80pt;" width="106"> <col style="width: 103pt;" width="137"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" align="right" height="20" width="64">30133</td> <td style="width: 93pt;" width="124">Construction</td> <td style="width: 80pt;" width="106">Other</td> <td style="width: 103pt;" align="right" width="137">27</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">49898</td> <td class="xl65">Construction</td> <td class="xl65">Other</td> <td class="xl65" align="right">33</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">46646</td> <td>Construction</td> <td>Other</td> <td align="right">44</td> </tr> </tbody></table>

All of these fields have 0 entries for less than 15 years of experience, so the formula is giving me the division by zero error. I have tried inserting another IF function in every combination I can think of and I can't figure out how to make it go away, or just give me a 0 value, even just blank.

I tried:

{=IF(D2:D101<16, AVERAGEIFS($A$2:$A$101,$D$2:$D$101,"<16",$B$2:$B$101,L47,$C$2:$C$101,M47),0)}

That will give me a 0 value, but it also zero's out the cells that are working properly. Even using cntl+shift+Enter.

I also get the DIV/O error on the opposite side for one single entry that has less than 16 years experience.

What am I doing/not doing wrong here?

 

[wut]

What is in L47 and M47?

 

[wut]

I recreated the sheet based on your explanation and got exactly the same error. I deleted teh back half of your array and the error went away. So I'm wondering if it's got soemthing to do with the last two criteria.

 

[Idaho Cowboy]

What is in L47 and M47?

The B and C columns of the sample tell which industry or occupation the person is, respectively.

L47 and M47 would be that specification on my chart.

so the chart goes something like


(L column) (M column)
Industry Occupation 0-15 16+

Other Service
Other Clerical
Other Professional
Manufacturing Service
Manuf.. Clerical

etc
etc
etc

There are 5 total occupations and 3 industries in the sample of 100. So the L47,M47 is one of the logical values for performing the function. So I want the average wage for all people with less than 15 years experience AND in industry other AND in occupation Service. Repeat for each frequency unit.

Does that answer you question?

 

[Idaho Cowboy]

I recreated the sheet based on your explanation and got exactly the same error. I deleted teh back half of your array and the error went away. So I'm wondering if it's got soemthing to do with the last two criteria.

Well, all the criteria in the formula is needed for the answer, unless I just go back and break up the sample into each separate category. But that's a lot of extra work I really don't want to do. BTW, What was the answer you got? It should have been 0, since those cells with errors are false. It's giving me the error, and what I need is 0 or just blank. It's trying to divide by zero because there are zero entries that meet the qualifications.

For now, until someone can help me figure it out, I simply deleted those error cells and entered 0.

 

[Idaho Cowboy]

Here is the table I am working with. Hopefully it pastes in okay.

<table border="0" cellpadding="0" cellspacing="0" width="502"><col style="width: 110pt;" width="147"> <col style="width: 86pt;" width="115"> <col style="width: 104pt;" width="138"> <col style="width: 77pt;" width="102"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 110pt;" height="20" width="147"> </td> <td class="xl65" style="width: 86pt;" width="115"> </td> <td colspan="2" class="xl65" style="width: 181pt;" align="center" width="240"> Mean Wages</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl67" style="height: 15.75pt;" height="21">Industry</td> <td class="xl67">Occupation</td> <td class="xl69"> 0 - 15 years</td> <td class="xl69">16+</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20"> </td> <td class="xl70"> </td> <td class="xl66"> </td> <td class="xl66"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">Other</td> <td class="xl66">Clerical</td> <td class="xl68">$26,663</td> <td class="xl68">$28,339</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">Other</td> <td class="xl66">Management</td> <td class="xl68">$35,388</td> <td class="xl68">$41,180</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">Other</td> <td class="xl66">Other</td> <td class="xl68">$24,760</td> <td class="xl68">$40,974</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">Other</td> <td class="xl66">Professional</td> <td class="xl68">$41,936</td> <td class="xl68">$44,525</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">Other</td> <td class="xl66">Sales</td> <td class="xl68">$24,067</td> <td class="xl68">$28,878</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">Other</td> <td class="xl66">Service</td> <td class="xl68">$19,253</td> <td class="xl68">$23,560</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">Manufacturing</td> <td class="xl66">Clerical</td> <td class="xl68">$35,185</td> <td class="xl68">$24,064</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">Manufacturing</td> <td class="xl66">Management</td> <td class="xl68">$0</td> <td class="xl68">$49,974</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">Manufacturing</td> <td class="xl66">Other</td> <td class="xl68">$24,346</td> <td class="xl68">$23,521</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">Manufacturing</td> <td class="xl66">Sales</td> <td class="xl68">$0</td> <td class="xl68">$33,411</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">Manufacturing</td> <td class="xl66">Service</td> <td class="xl68">$19,284</td> <td class="xl68">$0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">Construction</td> <td class="xl66">Other</td> <td class="xl68">$0</td> <td class="xl68">$42,226</td> </tr> </tbody></table>

The area with $0 is where the errors occurred and I just substituted 0 for now. These are the mean wages for each category in the 100 unit sample.

Hopefully that makes it more clear what the L47 and M47 refer to. Industry is L and Occupation is M. Manufacturing - Management is L47,M47.

 

[Marcelo Branco]

Hi,

I believe its a normal behavior of AVERAGEIFS to produce a #DIV/0! error when no item is found.

So, i think all you have to do is to wrap your formula with IFERROR function. Something like this
=IFERROR(yourformulahere,"NOT FOUND")

By doing so, the formula will produce a message - NOT FOUND in the example above - when no item (row) satisfies all the conditions

I also think you dont need Ctrl+Shift+Enter with AVERAGEIFS.

HTH

M.

 

[Idaho Cowboy]

Hi,

I believe its a normal behavior of AVERAGEIFS to produce a #DIV/0! error when no item is found.

So, i think all you have to do is to wrap your formula with IFERROR function. Something like this
=IFERROR(yourformulahere,"NOT FOUND")

By doing so, the formula will produce a message - NOT FOUND in the example above - when no item (row) satisfies all the conditions

I also think you dont need Ctrl+Shift+Enter with AVERAGEIFS.

HTH

M.

I'll try that deviation with the IFERROR. However, I do need the Ctrl+shift+enter to make the averageif formula work alone, otherwise it won't work.

 

[Idaho Cowboy]

That worked! Thanks.

 

[Marcelo Branco]

That worked! Thanks.

You are welcome!

Just for curiosity, AVERAGEIFS worked or not without CSE?

M.