# Averaging # based on several criteria in a range

1. ## 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.  Register To Reply

2. ## 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)  Register To Reply

3. ## 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.  Register To Reply

4. ## 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  Register To Reply

5. ## 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!  Register To Reply