Adding Values across sheets
we have a Survey response sheet that has a column with total minutes for each setup we are trying to calculate the Average minutes by adding all the Values and divide them by the total of records.
I have a Dashboard calculation sheet and Im trying to add all the numbers of minutes in the column so I can dive it by the total count of received records and
Technology Equipment Onboard Experience Su Range 3 is the reference to the other sheet's whole column with all minutes listed.
=SUM({Technology Equipment Onboard Experience Su Range 3})
is there an easy way to calculate the average minutes from a reference column?
Your help will be greatly appreciated. :-)
Best Answer
-
I use the VALUE function to force (or try to force) a string to a value. Try this in front of your minute-extraction formula
=VALUE(LEFT([Setup Time]@row,2)
Let me know how this turns out.
Kelly
Answers
-
Hi
Smartsheet does have a function for Average.
If I understood correctly, your minutes are in another sheet. Try this cross-referenced formula
=AVG({Technology Equipment Onboard Experience Su Range 3})
Be sure to change the cross reference range name if this isn't the actual range you need referenced.
If you are licensed user, you may want to explore the Solution center (the + in your left menu panel) for the smartsheet formula sheet. Search using 'formula'. Check back to it periodically as they update the template when new functions are available.
Hope this helps.
Kelly
-
I entered that formula and I get #DIVIDE BY ZERO
By the Way I appreciate your time on helping me with this.
-
Hey
If I understood correctly, the cross reference range does refer to the minutes column in the source sheet? I'm not sure why you're getting this error. If you tried copy paste of the formula before, please try instead to enter the formula by typing in the =AVG and clicking the link to insert the formula.
If it persists, a screenshot of your inserted formula would be helpful.
Kelly
-
Im not sure if this could be an issue, but the column that have that Im calculating is called Mints and it has a formula to bring only the number value of the Setup Time Survey Response field. I may add that the column type is text/number
-
There have to be a bug on the way Smartsheet looks at these values converted from a string
60 min extracting the numeric value with this formula =LEFT([Setup Time]@row, 2) on a different column and still the same error, but I tried entering the numbers manually in another column and your formula works.
I get 65 as result which is right.
My question now will be, there a different way to build the formula to format the string into a number? I think that still looks at it as a text even thou is a number.
-
I use the VALUE function to force (or try to force) a string to a value. Try this in front of your minute-extraction formula
=VALUE(LEFT([Setup Time]@row,2)
Let me know how this turns out.
Kelly
-
I found what the issue was, these are records coming from a Survey generated in FORMS (Office 365) and when the value comes into smartsheet converts all numbers into Text type
I double clicked one of the fields and you can see the (') in front of the number. I applied your new formula and it worked 😀
thanks very much for your help!!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!