Averageif and match for summary of info

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([email protected], {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

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    =AVERAGEIF({Quiz 1 Completion Avg}, {ID }, [email protected])

    thinkspi.com

  • Sarah Lanahan
    edited 09/02/21

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


    =AVERAGEIF({Stats Quiz 1 Completion Avg}, {Stats CDDS EID}, [email protected])


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

    NOTE: EID is the same as ID.

  • Paul Newcome
    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}, [email protected], {Stats Quiz 1 Completion Avg})

    thinkspi.com

  • 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
    Paul Newcome ✭✭✭✭✭✭

    Are you still getting an error?

    thinkspi.com

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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



    thinkspi.com

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


  • Paul Newcome
    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.

    thinkspi.com

  • Sarah Lanahan
    edited 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
    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. 👍️

    thinkspi.com