+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    01-17-2008
    Posts
    10

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    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. #3
    Registered User
    Join Date
    01-17-2008
    Posts
    10
    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.

    Thanks for your help!

  4. #4
    Registered User
    Join Date
    01-12-2004
    Location
    NY
    Posts
    92
    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
    Last edited by nrage21; 03-20-2008 at 12:50 AM.

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Quote 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. #6
    Registered User
    Join Date
    01-12-2004
    Location
    NY
    Posts
    92
    lol you are correct daddylonglegs.

    Every1 please disregard my post

    Regards,
    nrage21

  7. #7
    Registered User
    Join Date
    01-12-2004
    Location
    NY
    Posts
    92
    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. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    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

  9. #9
    Registered User
    Join Date
    01-12-2004
    Location
    NY
    Posts
    92
    daddylonglegs,
    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. #10
    all4excel
    Guest

    Smile 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
    Last edited by all4excel; 03-22-2008 at 06:34 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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