Project Assessment Sheet

Scotty H

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.

Ide AirTrunk

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?

Scotty H

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

Ide AirTrunk

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

Scotty H

Hi,

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

Regards,

Scott

Ide AirTrunk

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

Scotty H

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.