Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
combining formula to round a sum
I'd like to know if I can combine the two following formulas in some way so that I only need one column:
In the first below, I'm calculating the average number of minutes an editor spends on each page of a document. But, because I don't want to report the number with such precision, I have a second column with a formula to round the sum to the nearest whole number.
I'd rather not have to maintain and hide/unhide the extra column. Can I combine these two formulas?
=SUM(([# hours spent by contractor]28 * 60) / [actual # pages editor received]28)
=ROUND([Avg. min/page]28, 0)
Comments

Why do you even need the SUM formula if you are only multiplying/dividing?
SUM is used by adding two or more values. ex: =SUM(15,2) will result in 17
For one column formula:
=ROUND([# hours spent by contractor]28 * 60) / [actual # pages editor received]28, 0)
Also, by the math, it's probably pretty close to a few minutes to 0 minutes per page, because there usually is more pages than minutes unless an editor really spends 30 minutes a page or something. Its probably more helpful (although I'm not too sure) to calculate pages per minute...

Thanks so much! Exactly what I need. And, yes, am also tracking pages/hour in a separate column and will use same info you suggest above. Do you know if Smartsheet supports "MROUND" like in Excel so round to (.25, .50, .75) of hour or page... or even to just a whole number and a X.5  so I could track to the X pageandahalf per hour?

http://help.smartsheet.com/customer/portal/articles/775363usingformulas#numeric
Search for ROUND
I'm not too sure if Smartsheet has rounding to exact decimal. You may have to customize by an IF statement
That will be more tricky, as you need the original value from the division, say 2.345.
extract the integer value (and save it in a column), subtract it with the original value and keep it in a column (to get the decimal values only).
Then use an IF statement to round it to whatever you want.
Finally, add the integer value and the rounded decimal value (from the IF statement) together in your final column

This might help,
WWith the ROUND function, you can state how many decimal places you want it rounded to. For example:
=ROUND(Cell1, 2)
will round to this:
x.xx
While this:
=ROUND(Cell1, 1)
will round to this:
x.x

Thanks! R

yes, but it differs from the MROUND that excel has. SIlber wishes to round to the nearest 0.25, 0.50, 0.75, etc.
Only way to do that is to calculate through a variety of IF statements

Hello everyone,
I just created a formula that will round to the nearest 5th integer.
=IF(Cost1  ROUND(Cost1, 1) >= 2.5, ROUND(Cost1, 1) + 5, IF(Cost1  ROUND(Cost1, 1) < (2.5), ROUND(Cost1, 1)  5, ROUND(Cost1, 1)))
Essentially, It takes the original amount and subtracts it by a round that is rounding to the nearest 10th integer (not tenth decimal). Then if the result is greater than or equal to 2.5 it will add 5 to the round, if it is is less than or equal to 2.5 it will subtract 5 from the round. If not, it will just round it to the nearest 10th.