combining formula to round a sum

R.Silber

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)

Jeff K.

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

 

 

R.Silber

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 page-and-a-half per hour?

Jeff K.

http://help.smartsheet.com/customer/portal/articles/775363-using-formulas#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

JohnHinkle

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

R.Silber

Thanks! -R

Jeff K.

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

Taylor F

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.