Jan 04 2021 01:50 PM
I am having trouble with the averageif function on Excel. What I'm doing is trying to get the average postsecondary years of students who have been elected. The postsecondary years and the elected column are on the same table, but I am trying to get the averageif function into a different table on the same worksheet. I also want it to go into only one cell, R8. When I try it, I keep getting syntax errors and spill errors. Can anyone please help? I have attached the file for you to look at.
Jan 04 2021 04:38 PM - edited Jan 04 2021 04:43 PM
@Seanm1525 One problem at a time....
You are using AVERAGEIF, not AVERAGEIFS. The syntax for AVERAGEIF (no "S") is:
AVERAGEIF(range, criteria, [average_range])
Note that when "range" and "average_range" are different, the range to average is the 3rd parameter, not the 1st, as you wrote it.
Also, the criteria parameter is a string that contains an optional comparison and a value.
So the formula in R8 should be:
=AVERAGEIF(StudentRepresentatives[Elected], "Yes", StudentRepresentatives[Post-Secondary Years])
or
=AVERAGEIFS(StudentRepresentatives[Post-Secondary Years], StudentRepresentatives[Elected], "Yes")
The equal sign -- which would be written "=Yes" -- is optional because it is implied.
-----
PS.... It would be prudent to wrap IFERROR around the AVERAGEIF[S] expression, just in case there are no rows that meets your criterion. For example:
=IFERROR(AVERAGEIFS(StudentRepresentatives[Post-Secondary Years], StudentRepresentatives[Elected], "Yes"), "")
Jan 05 2021 07:54 AM
Thank you, @Joe User! I didn't realize I had put averageifs instead of averageif. That was a typo. I'll try your suggestion on the iferror.
Jan 05 2021 09:15 AM
As @Joe User mentioned you may use both, just order of parameters will be different.