Averageif and match for summary of info

SarahML

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

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})

Paul Newcome

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

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

SarahML

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

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

I'm pretty sure there aren't any extra spaces or commas in the equation.

NOTE: EID is the same as ID.

Paul Newcome

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

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

Are you still getting an error?

SarahML

@Paul Newcome yep. I'm getting Incorrect Argument Set now.

Paul Newcome

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

SarahML

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

Paul Newcome

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

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

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. 👍️