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

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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

  • Jose Munoz
    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
    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
    Jose Munoz ✭✭✭✭
    edited 11/09/20

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

  • Jose Munoz
    Jose Munoz ✭✭✭✭
    edited 11/09/20

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!