rounding up

Gary Keen

Hi All. 

We are trying to make an order form that calculates a number typed by our clients to a box rate. To do this we have tried dividing the order by the amount per box  then rounding up to a full box.

we have been advised to try this.add a flat value of 0.5 to anything returned by the above formula. This will force any value to be able to get rounded to the number you’re looking for.

 The problem is that it adds the .5 even if there is a zero number in the column (or nothing in the cell). The clients need to choose from a list of products, at the moment the round up function generates an order of one box when nothing has been typed over the hole list. is there a way to incorporate a rule that a zero number means the .5 is not added or no roundup takes place. Our original order form was done in Excel. =ROUNDUP(+G5*M5/2,0) The formula was quite simple.

I know there is a lot of smarter people out there than me so perhaps someone has a much better way to achieve this.  

Regards

Gary 

Jenny Lam

Hi Gary,

 

To leave the calculated value blank, use IF formula to skip calculation when the cell is not set:

 

IF (cell is blank or value is  0, 0, calculation_goes_here)

 

Here's the formula I have come up with for your situation, try and see if this formula works in your case (The calculation is on row 1 of "Column Name"):

 

=IF(OR(ISBLANK([Column Name]1), [Column Name]1 = 0), 0, ROUND([Column Name]1 + 0.49, 0))

 

To use the forumla, change "Column Name" to the column on your sheet, and the number "1" to the row number that needs to be calculated.

Gary Keen

Thanks so much, I would never of got that far, will try it out straight away.

regards

Gary

J. Craig Williams

In your Excel formula, what are G5 and M5?

If G5 is amount ordered (example 3) and M5 is the 1/(amount per box) (example 1)

then

3 * 1 / 2 = 1.5 rounded up to 2 -- which is wrong.

If M5 is amount per box (example 1)

then I still get 

3 * 1 / 2 = 1.5 rounded up to 2 -- which is still wrong.

 

Oh wel..

 

Here's another way to do the same as Jenny's solutiion:

 

=IF(INT([Amount / Amountperbox]15) = ([Amount / Amountperbox]15), ([Amount / Amountperbox]15), INT([Amount / Amountperbox]15) + 1)

 

Where the [Amount / Amoutperbox] column is the calculation of Amount / Amount-per-box (the same as Jenny's Column Name). 

 

INT will take the integer portion of the division -- rounding down in effect.

In this matches the division itself (or in math terms, there is no remainder), then we either keep that number or increment the integer value (the +1)

I don't need to worry about the blanks or the 0s because the math works this out by itself.

YMMV

 

Craig

 

 

Gary Keen

Hi Craig

thanks for getting back, Ill try to explain what we  do so you see the full pictur and the way it needs to work. First we sell stone product so people may have two stone pillars to build. So our sheet would say how many meters needed in flats5 * the number required in numbers5 / box size which is .8 m2 per box giving us number rounded to the closesest full box. We got that working ok with this =ROUND(Flats10 * Number10 / 0.8 + 0.5, 0)  but that couldant cope with zero numbers in some rows. Thanks for you input and patience, I know im well outside my confort zone here. I should keep to making stone 

 

 

 

We have a column called flats5 which is the 

J. Craig Williams

Flats units of measure is meters.

Number is dimensionless?

Box size unit of measure is square meters.

 

Flats * Numbers / Box Size unit of measure is 1/meter?

I'm still missing something.

 

Craig

Gary Keen

Yes for example sake I was tying to discribe the situation, so people order by m2 but a box only contains .8 m2. eg. stone order is 12.5 m2 so customer will get 12.5/.8 = 15.6

which would round up to 16 boxes for order.

Elan.Saltman

Rounding Up to the Nearest 10

 

I was tasked recently to embed a SmartSheet onto a website. One of the columns was supposed to take the number of employees and round up to the nearest 10. As a control for the potential for too few samples being sent, we needed to only round up. Therefore a 1 or a 2 would still round to 10.

 

Here is the formula I used: =ROUND([# of Employees]1 + 4, -1)

 

The first #1 shows that it is being pulled from the first row in '# of Employees'.

Adding the flat four was what made the Round Up jump to the the nearest ten.

The -1 dictates decimal place and changes the value to the nearest 10. 

 

Regards,

Elan