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?

 

[Idaho Cowboy]

By itself, it does not work without using CSE.

But imbedded in a formula, like the IFERROR, it worked fine.

I tested it on each row and none of the values changed, except where the error was. So it is working perfectly. So now instead of DIV/0 error, I just see a " - "


Thanks for the help.