Forum Discussion

Seanm1525's avatar
Seanm1525
Copper Contributor
Jan 04, 2021

averageif help

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.

excel
Formulas and Functions
office 365

3 Replies

Sort By
  • JoeUser2004's avatar
    JoeUser2004
    Bronze Contributor
    Jan 05, 2021

    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"), "")

     

     

Resources