Project Assessment Sheet
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
-
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!