I've already figured out how to get the correct result, but I'm hoping someone can explain to me why I get different (incorrect) answers using different formulas that (in my mind) should do the same thing.
I've attached the worksheet I'm talking about. In it, I'm trying to get an average for the ages (column A) that have either "Emp Only," "Emp/Child", "Emp/Children", "Emp/Spouse", or "Family" in Column B. Basically, I want to average all entries except those that have "Waived" or "Not Eligible" in column B.
The first formula is this: =AVERAGE(IF(NOT(B2:B115="Not Eligible"),IF(NOT(B2:B115="Waived"),A2:A115)))
This gets me the correct answer (38.65)
Now I'm confused as to why the subsequent formulas either get me a divide-by-zero error, an incorrect answer, or a zero.
Thanks for the help.
Bookmarks