Andrew has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Excel Advanced - For Power Users course
Excel Dashboards for Business Intelligence course
AVERAGEIFS
Hi,
Can you help me with the following please?
The formula immediately below works perfectly well and gives me the average length of service based on values in Column O, with employees in Cost Code (YA00).
=AVERAGEIFS('Report - Headcount'!$O:$O,'Report - Headcount'!$A:$A,"YA00",'Report - Headcount'!$J:$J,"*Regular")
I'm trying to add a second criteria (Cost Code) so it looks at PA00 as well but without success. Can you see what's wrong with my formula? Should I be using an OR statement? If yes then how do I incorporate it into the formula?
=AVERAGEIFS('Report - Headcount'!$O:$O,'Report - Headcount'!$A:$A,"YA00",'Report - Headcount'!$J:$J,"*Regular",'Report - Headcount'!$A:$A,"PA00",'Report - Headcount'!$J:$J,"*Regular")
RE: AVERAGEIFS
Hi Andrew,
Thank you for your post. The Averageif function tends to break sometimes when you use the same criteria range more than once. One possible solution is shown below. I've tested it and it works on my sheet.
=AVERAGE(AVERAGEIFS('Report - Headcount'!$O:$O,'Report - Headcount'!$A:$A,"YA00",'Report - Headcount'!$J:$J,"*Regular"),AVERAGEIFS('Report - Headcount'!$A:$A,"PA00",'Report - Headcount'!$J:$J,"*Regular")).
I hope this helps
Kind regards
Marius Barnard
Excel Trainer
RE: AVERAGEIFS
Hi Marcus,
I've run the formula within the same worksheet this time so it's easier to read. It still doesn't work as it says I've entered too few arguments for this function. This is the updated formula with your suggestion incorporated into it:
=AVERAGE(AVERAGEIFS($O:$O,A:A,"YA00",$J:$J,"*Regular"),AVERAGEIFS($A:$A,"PA00",$J:$J,"*Regular"))
RE: AVERAGEIFS
Hi Andrew,
I notice that in your formula, the first instance of A:A needs to read $A:$A.
This will probably solve it.
Kind regards
Marius
RE: AVERAGEIFS
It still says too few arguments.
=AVERAGE(AVERAGEIFS($O:$O,$A:$A,"YA00",$J:$J,"*Regular"),AVERAGEIFS($A:$A,"PA00",$J:$J,"*Regular"))
Here is a sample of what is contained in each of the 3 columns.
Col O = Length of service in years e.g. 4.6
Col A = PA00, YA00 and many others
Col J = Full-Time Regular etc
As I'm trying to find the average length of service in both YA and PA, should I be using a sumif and countif combination? If so, how would the formula look?
RE: AVERAGEIFS
Hi Andrew,
Looking at your formula again, I also notice that in the second Averageifs, it's lacking the $O:$O before the $A:$A. This, of course, is the column containing the values. Sorry, I didn't spot this earlier.
Kind regards
Marius
