# Attempting to calculate an average based on multiple criteria using an array formula

1. ## Attempting to calculate an average based on multiple criteria using an array formula

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.

2. Typically you can't use AND or OR in these sort of formulas because those functions return a single result rather than a result for each cell in the range.

The other non-working formula

=AVERAGE(IF(B2:B115="Emp Only",IF(B2:B115="Emp/Spouse",IF(B2:B115="Emp/Child",IF(B2:B115="Emp/Children",IF(B2:B115="Family",A2:A115))))))

will only average column A when all the other conditions are true, obviously none of the cells can equal more than one of those text strings so you'll always get #DIV/0! error.

I'd use this formula

=AVERAGE(IF(B2:B115<>"Not Eligible",IF(B2:B115<>"Waived",A2:A115)))

although you could also use either of these

=AVERAGE(IF(ISNA(MATCH(B2:B115,{"Not Eligible","Waived"},0)),A2:A115))

or

=AVERAGE(IF(ISNUMBER(MATCH(B2:B115,{"Emp Only","Emp/Child","Emp/Children","Emp/Spouse","Family"},0)),A2:A115))

3. Thanks, that makes sense.

I've never used the "<>" operator before, and I've also never had a need for the MATCH function before, so this is good to know.

4. This is another one using name ranges and the & to concatenate criteria.

Name Ranges
Status = B2:B115
Age = A2:A115

Cell B2 = Not Eligible
Cell B4 = Waived

``Please Login or Register  to view this content.``
Regards,
nrage21

5. Originally Posted by nrage21
SUMIF(Status,"<>"&B2&B4,Age)/COUNTIF(Status,"<>"&B2&B4)
That formula will just give the average age of all employees, because none of them have the status "Not EligibleWaived"

6. lol you are correct daddylonglegs.

Regards,
nrage21

7. ok daddaylonglegs this should work now... I was trying to create as short a formula as possible. Another plus, is that it is not an array formula so no need to commit w/ C+S+E

Named ranges
A2:A115 = Age
B2:B115 = Sta

``Please Login or Register  to view this content.``
I took advantage of the wildcard in the criteria since both Family and Emp have an "m" in the middle. It was just a matter of time.

Regards,
nrage21

8. As a longtime devotee of short formulas I have to say I like it, nrage, although, to paraphrase a mild criticism I once received, you may be sacrificing transparency on the altar of compactness

I agree with your statement... there are no numeric values and it is not clear at first pass where the WF is being applied. However, if the parameters are well documented near to where the formula is, then it should be peanuts to decipher, specially for season excel users.

Regards,
nrage21

10. ## Just one small suggestion..

The codes provided by Nrage and Daddylonglegs are really good, but in order to have an expanding list you can use the Name ranges

Istead of using the Absolute reference, we can make them Dynamic by simply using the OFFSET function instead of A2:A115 & B2:B115
Ex-

Age=OFFSET(\$A\$2,0,0,COUNTA(\$A:\$A)-1,1)

Sta=OFFSET(\$B\$2,0,0,COUNTA(\$B:\$B)-1,1)

This would help when the List expand beyong row115.

all4excel

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1