Project Assessment Sheet

Scotty H
Scotty H ✭✭
edited 10/04/24 in Formulas and Functions

Hi Smartsheet Community,

I am a new member to the community. I have been using Smartsheet for two years but I am only just starting to build my own sheets and workflows.

My current project is to automate a current internal process which involves three members of our team to score (on a scale of 1 - 5) a set of questions to determine whether we proceed with a project or not. I have created a form to setup the project (including selecting the team members) and a workflow to send a second form out to the nominated team members to provide their scores to the questions.

My hurdle that I have now is to average the results for each question, which will then lead on to the next step of displaying the results in a dashboard.

MS Copilot and Smartsheet AI have both suggested creating a helper grid and using the AVERAGEIF function but I am receiving constant formula error messages.

Can anyone provide help / guidance on this please.

Thanks,
Scott

My main sheet is named "Results", the data I'm trying to average is in column "Q1", and I am using the "Project Name" column as the Project ID field to filter out the results from assessment responses for other projects.

Answers

  • JIDEATTURRA
    JIDEATTURRA ✭✭✭✭✭✭

    Hi @Scotty H,

    The formula should look something like this:

    =AVERAGEIF({Results Project Name}, Project@row, {Q1})

    If I do this, I get the result:

    What error are you getting? Are the columns number/text?

  • Hi @JIDEATTURRA ,

    Thank you for your reply. Unfortunately I still receive an error which makes me wonder if there is a background setting which may be incorrect? The two types of errors I am receiving are "#INVALID REF" or "#UNPARSEABLE".

    To clarify some details:

    I have two sheets (grids) - one named "Results" where all the data from forms is received into. The second sheet is named "Summary" which I am using as my calculation helper sheet. My "Q1" column is in the "Results" sheet.

    Screenshot of "Results" sheet

    Screenshot of "Summary" sheet

    I would appreciate your further thoughts.

    Thanks,

    Scott

  • JIDEATTURRA
    JIDEATTURRA ✭✭✭✭✭✭

    Hi,

    On the Summary Sheet type the following formula (do not change any column names)..

    =AVERAGEIF({Results Project Name}, [Project Name]@row, {Q1})

    Whereby {Results Project Name} is a cross-sheet reference looking at the Project Name column in the 'Results' sheet and {Q1] is a cross-sheet reference looking at the Q1 column in the 'Results' sheet.

    If this does not work, email me at Jamie.ide@atturra.com so I can send a teams invite to fix.

    Jamie

  • Hi,

    Still didn't work unfortunately. I will email you now. Screenshot of new error as below.

    Regards,

    Scott

  • JIDEATTURRA
    JIDEATTURRA ✭✭✭✭✭✭

    You need to add Project 1, or any of the project names, in the left column in the same row as the formula.

  • For those reading this in the future, Jamie was very helpful and sorted me out via Teams call.

    My issue was I had the set data referenced as different names.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!