Using values returned by formulas in other formulas
Hi,
I have a user populated field (using a dropdown) where the options range from “1-Innocence” to “5-Excellence” – this makes more sense to a user than simply having a 1-5 available for selection. To summarise these ratings per theme, focus area, assessment, etc. I obviously need to make use of the 1,2,3,4,5 values in the string. I extract that from the string using =left(), easy enough. But can then do no form of calculation, such as averaging, on the returned value.
I need to copy the result from the =left() function and paste it as a value before the =AVG() formula sees it for what it is. Obviously I want to set up the formulas and summaries to refer to the returned values and not have to manually do something every time someone makes a change.
Hope I'm making a mistake and that there's an easy fix. Thanks
Best Answer
-
Wrap the call of the left with the value function such as :
=VALUE(LEFT(mycolumm4, 1))
This will convert the result into a number and allowing further operations.
Answers
-
Wrap the call of the left with the value function such as :
=VALUE(LEFT(mycolumm4, 1))
This will convert the result into a number and allowing further operations.
-
So simple, thanks for helping out so quickly!
-
Thank you I was able to convert my fields to values, but when I try to SUM those values in another field I keep getting 0?? How do I need to write my SUM formula when I select these converted fields?
-
Hi @Shayila Bernard,
Can you paste the formula you're using?
Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
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.
-
I figured it out.
My individual task had a formula to convert the value to a number.
=VALUE(LEFT(IF(Status@row = "Done", "100%", (IF(Status@row = "To Do", "0%", "")))))
Then I have a separate field to show me the % of those task that are completed.
=SUM([% Complete 1= 100%]3:[% Complete 1= 100%]22) / 20
I'm happy with the results. If there is a more efficient way to accomplish this calculation please let me know.
Thanks.
-
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.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!