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. :-)

Find more posts tagged with

Accepted answers

Kelly Moore

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

All comments

Kelly Moore

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

Jose Munoz

I entered that formula and I get #DIVIDE BY ZERO

By the Way I appreciate your time on helping me with this.

Kelly Moore

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

Jose Munoz

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

Jose Munoz

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.

Kelly Moore

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

Jose Munoz

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!!!!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.