averageif help

%3CLINGO-SUB%20id%3D%22lingo-sub-2030982%22%20slang%3D%22en-US%22%3Eaverageif%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2030982%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20having%20trouble%20with%20the%20averageif%20function%20on%20Excel.%20What%20I'm%20doing%20is%20trying%20to%20get%20the%20average%20postsecondary%20years%20of%20students%20who%20have%20been%20elected.%20The%20postsecondary%20years%20and%20the%20elected%20column%20are%20on%20the%20same%20table%2C%20but%20I%20am%20trying%20to%20get%20the%20averageif%20function%20into%20a%20different%20table%20on%20the%20same%20worksheet.%20I%20also%20want%20it%20to%20go%20into%20only%20one%20cell%2C%20R8.%20When%20I%20try%20it%2C%20I%20keep%20getting%20syntax%20errors%20and%20spill%20errors.%20Can%20anyone%20please%20help%3F%20I%20have%20attached%20the%20file%20for%20you%20to%20look%20at.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2030982%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2031428%22%20slang%3D%22en-US%22%3ERe%3A%20averageif%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2031428%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F917693%22%20target%3D%22_blank%22%3E%40Seanm1525%3C%2FA%3E%26nbsp%3B%26nbsp%3B%20One%20problem%20at%20a%20time....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20using%20AVERAGEIF%2C%20not%20AVERAGEIF%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3ES%3C%2FSTRONG%3E%3C%2FFONT%3E.%26nbsp%3B%20The%20syntax%20for%20AVERAGEIF%20(no%20%22S%22)%20is%3A%3C%2FP%3E%3CP%3EAVERAGEIF(range%2C%20criteria%2C%20%5B%3CFONT%20color%3D%22%23FF0000%22%3Eaverage_range%3C%2FFONT%3E%5D)%3C%2FP%3E%3CP%3ENote%20that%20when%20%22range%22%20and%20%22average_range%22%20are%20different%2C%20the%20range%20to%20average%20is%20the%203rd%20parameter%2C%20not%20the%201st%2C%20as%20you%20wrote%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20the%20criteria%20parameter%20is%20a%20string%20that%20contains%20an%20optional%20comparison%20and%20a%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20the%20formula%20in%20R8%20should%20be%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DAVERAGEIF(StudentRepresentatives%5BElected%5D%2C%20%22Yes%22%2C%20StudentRepresentatives%5BPost-Secondary%20Years%5D)%3C%2FP%3E%3CP%3Eor%3C%2FP%3E%3CP%3E%3DAVERAGEIF%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3ES%3C%2FSTRONG%3E%3C%2FFONT%3E(StudentRepresentatives%5BPost-Secondary%20Years%5D%2C%20StudentRepresentatives%5BElected%5D%2C%20%22Yes%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20equal%20sign%20--%20which%20would%20be%20written%20%22%3DYes%22%20--%20is%20optional%20because%20it%20is%20implied.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

3 Replies

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

 

 

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.

@Seanm1525 

As @Joe User mentioned you may use both, just order of parameters will be different.