+ Reply to Thread
Results 1 to 5 of 5

Averaging # based on several criteria in a range

  1. #1
    Registered User
    Join Date
    05-02-2017
    Location
    NoVa, United States
    MS-Off Ver
    2010
    Posts
    96

    Averaging # based on several criteria in a range

    I'm not entirely sure that title is as clear as I intend. In any event, I'm trying to do an AVERAGEIFS based on several criteria - all of which entail an AND/OR. These formulas are on the 'Data' tab and the criteria are on the 'Position' tab. Using these criteria I want to average the salaries found on the various other tabs (4-30-19, 5-31-19, etc).

    So average the salary if it's in Job 1 (which is one of several jobs on a different tab) AND if it's in Loc 1 (which is one of several locations on a different tab).

    Right now my formula doesn't work, it's: {=AVERAGEIFS(INDIRECT("'"&$K$3&"'!$G:$G"),INDIRECT("'"&$K$3&"'!$C:$C"),Position!$A$2:$A$3,INDIRECT("'"&$K$3&"'!$E:$E"),Position!$A$10:$A$11)}

    Which gives me 100,000, which is not correct - it should give me 137500.

    I haven't been able to find something online that's precisely like this.

    Any help is appreciated.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    972

    Re: Averaging # based on several criteria in a range

    given the OR nature of your test you would need to revert to something like the below, array-entry, formula:

    =AVERAGE(IF(ISNUMBER(MATCH(INDIRECT("'"&$K$3&"'!$C:$C"),Position!$A$2:$A$3,0)),IF(ISNUMBER(MATCH(INDIRECT("'"&$K$3&"'!$E:$E"),Position!$A$10:$A$11,0)),INDIRECT("'"&$K$3&"'!$G:$G"))))
    confirmed with CTRL + SHIFT + ENTER

    by splitting into 2 IFs you reduce a little overhead (i.e. you only calc 2nd if 1st returns TRUE) -- if the odds for TRUE are significant lower for one test over the other use this as the 1st IF

    Note: I would (strongly) advise against using entire column ranges in your INDIRECT ranges, performance will be impacted, restrict the range as much as you can (reasonably)

  3. #3
    Registered User
    Join Date
    05-02-2017
    Location
    NoVa, United States
    MS-Off Ver
    2010
    Posts
    96

    Re: Averaging # based on several criteria in a range

    That is very interesting. I'm still trying to noodle what you've written through. It works and that's one thing - that I appreciate greatly - the other thing that I'm trying to get better at understanding is why it works. It's two if statements that, as you suggest, only work (return true) if both are met.

    The next part isn't exactly clear to me - it seems as though you convert the criteria to a number (if it MATCHES these criteria then count it as a NUMBER). So by the time of the second IF, if it meets both criteria it's counted as a number (each instance of 'true'). I'm not entirely sure how it's averaging.

    Average(A, B)

    A=the instances that have met both criteria in the IF statements (the amount of them).
    B = the salary of those in A.

    How is the formula telling B to only look for those that have the criteria in A? I feel as though I'm missing something very obvious.


    I hear you about the ranges and will make that note.

  4. #4
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    972

    Re: Averaging # based on several criteria in a range

    The calc is basically

    =AVERAGE(IF(test1=TRUE,IF(test2=TRUE,numbers)))

    if we dumbed down to say three rows of data, e.g.

    =AVERAGE(IF(A1:A3="a",IF(B1:B3="b",C1:C3)))

    and further assume

    A1, A2 & A3 are "a"
    B1 is "z" whilst B2 & B3 are "b"
    C1 to C3 are 10, 20 & 30 respectively

    then this will evaluate to

    =AVERAGE(IF({TRUE;TRUE;TRUE},IF({FALSE;TRUE;TRUE},{10;20;30})))

    which, upon further evaluation would be

    =AVERAGE({FALSE;20;30})

    which would evaluate to 50/2 -> 25

    ...it seems as though you convert the criteria to a number (if it MATCHES these criteria then count it as a NUMBER)
    the ISNUMBER is being used to generate the requisite Boolean response - i.e. to verify that the MATCH is valid and thus either a) proceed to 2nd IF or b) proceed to number

    if, using same values as outlined above, we applied:

    =MATCH("c",A1:A3,0)

    we would get #N/A given no "c" values exist in the precedent range whereas, conversely, if we were to change "c" to "a" the above would return 1 ("a" being found in first row)

    so, just to reaffirm...

    the ISNUMBER tests are being applied to the MATCH(es) to generate Boolean arrays, only where both arrays return TRUE would we use the associated value from C1:C3 in the AVERAGE

    HTH

  5. #5
    Registered User
    Join Date
    05-02-2017
    Location
    NoVa, United States
    MS-Off Ver
    2010
    Posts
    96

    Re: Averaging # based on several criteria in a range

    Thank you for the explanation! I wish I could give you another reputation point for that!

+ 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