+ Reply to Thread
Results 1 to 5 of 5

Averaging # based on several criteria in a range

  1. #1
    Forum Contributor
    Join Date
    05-02-2017
    Location
    NoVa, United States
    MS-Off Ver
    Microsoft 365
    Posts
    152

    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
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    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
    Forum Contributor
    Join Date
    05-02-2017
    Location
    NoVa, United States
    MS-Off Ver
    Microsoft 365
    Posts
    152

    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
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    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
    Forum Contributor
    Join Date
    05-02-2017
    Location
    NoVa, United States
    MS-Off Ver
    Microsoft 365
    Posts
    152

    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)

Similar Threads

  1. Averaging random cells based on criteria
    By barneyrubble1965 in forum Excel General
    Replies: 1
    Last Post: 10-10-2018, 05:17 AM
  2. [SOLVED] Summing/Averaging Totals Based off Date Criteria
    By KoolKatelyn in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-06-2017, 03:18 PM
  3. [SOLVED] Need your help averaging based on month & other criteria
    By hennakao in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 02-14-2017, 10:13 AM
  4. [SOLVED] Averaging a user input defined range based on values in columns
    By smit.etha in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-14-2014, 02:21 AM
  5. Averaging data if cells in range meet criteria
    By gobbledok in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-24-2013, 02:28 AM
  6. [SOLVED] Excel 2007 : Averaging across rows based on some criteria
    By cde1983 in forum Excel General
    Replies: 5
    Last Post: 05-15-2010, 07:06 AM
  7. averaging a column based on criteria andputting result in new worksheet
    By BadLilBrat in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-20-2007, 03:01 PM

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