Average Formula Issue
Hi there, i am trying to average cells that were built up by formulas.
So basically the Column 1-7 are the scores when the task is done , it will turns into 100 when the task is complete, and where the right handside column would need to average the score of column 1-7.
When I tried to average , it turns out "UNPARSEABLE" ; Below is the formula I tried to create.
(I also tried Average ISERROR, if also didn't work)
Answers
-
Hi Hoi
In smartsheet the word Average is not defined as a formula to calculate the averages. Instead, try the following:
=Avg([rangestart]:[rangefinish])
Best :)
Nasir (Zealvert)
-
Hi @Hoi Man Cheung,
To add to Nasir's excellent advice.
Here's more information:
I hope that helps!
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi , I am Afraid even I changed to AVG , it still shows "divide by ZERO error".
-
@Hoi Man Cheung Strange!
Where are the linked values coming from and what format are they?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
The Values in column 1-7 are formula as I mentioned, please refer to the below which I capture the formula contains.
-
i'd be happy to take a quick look.
Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Hoi Man Cheung Remove the quotes from around the numbers in your formula.
=IF(VLOOKUP(Marsha8, {ERS 2020 Photo Wave 1 LAND-IT Report Range 1}, 13, false) = "complete", 100, 0)
You will still get the #DIVIDE BY ZERO error until there is at least one value greater than zero within the row. To avoid this, you can wrap your AVG formula in an IFERROR to replace the error message with the number 0.
=IFERROR(AVG([Milestone 1 - Submit Property Coaches]8:[Ready To Go-Live]8), 0)
-
@Hoi Man Cheung, I saw that Paul answered already!
@Paul Newcome, Nice catch!
Let me know if I can help with anything else!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
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!