Averageif and match for summary of info

SarahML · 09/02/21

I'm trying to average the the percent of submissions for an assignment on one Smartsheet based on a teachers ID in the current sheet. Here's the formula I've tried

=AVERAGEIF({Quiz 1 Completion Avg}, MATCH(ID@row, {ID }))

The results that are populating in my sheet do not match with are not correct.

We have one sheet (the Tracker Sheet) that has all the information by teacher by section and I'm trying to get a summary to show in our Teacher Summary Sheet for the course. I've looked at avg(collect( and AverageIf. There are more rows in the Tracker Sheet than the Teacher Summary Sheet.

Thanks!

Sarah

Paul Newcome · 09/02/21

You don't need the MATCH function. Try this instead...

=AVERAGEIF({Quiz 1 Completion Avg}, {ID }, ID@row)

SarahML · 09/02/21

Paul, every time I do that formula I get Inccorrect Argument.

=AVERAGEIF({Stats Quiz 1 Completion Avg}, {Stats CDDS EID}, EID@row)

我敢肯定没有任何额外的空格或commas in the equation.

NOTE: EID is the same as ID.

Paul Newcome · 09/02/21

My apologies. I had the syntax backwards. I am used to the functions such as SUMIFS where the range to sum comes first. AVERAGEIF is more like SUMIF (without the "S" on the end) where the range to average comes last. Try this...

=AVERAGEIF({Stats CDDS EID}, EID@row, {Stats Quiz 1 Completion Avg})

SarahML · 09/02/21

It still doesn't like it.

I'm wondering if it doesn't like it because the ID/EID is not a number, it's alphanumeric.

Paul Newcome · 09/03/21

Are you still getting an error?

SarahML · 09/03/21

@Paul Newcomeyep. I'm getting Incorrect Argument Set now.

Paul Newcome · 09/03/21

Are you able to provide a screenshot of the formula within the sheet similar to the screenshot below?

SarahML · 09/03/21

@Paul NewcomeHere you go. I can't share much more of the sheet unfortunately.

Screen Shot 2021-09-03 at 9.55.27 AM.png

Paul Newcome · 09/03/21

No worries. This is just a little befuddling. Ugh. It SHOULD be working.

Did you already look through both of those ranges to see if that same error is anywhere in any of the cells referenced?

Did you double check the references to make sure they are covering the entire column? Sometimes if you select a range too quickly before the reference sheet has time to fully load, when it does finally load completely the selection will revert back to the top right cell without warning which can cause that same error.

SarahML · 09/08/21

I don't know why but a few days away from it and I put the formula in again and now it's working! I also selected the column and waited for a bit for it to load if it was taking it's sweet time. Thanks for all the help@Paul Newcome!

Paul Newcome · 09/09/21

Haha. Of course it is working now leaving us just assuming it was some kind of temporary glitch instead of being able to pinpoint and prevent a specific issue to keep it from happening again.

Glad it's working for you finally.️

Averageif and match for summary of info

Answers

Help Article Resources

Categories