Difficulty with Roundup

[jkharms]

I've created a formula: Roundup(H3*L3,0,). I also have the Numbers option in the Format/Cell dropdown list set to 2 decimal places. The formula is producing a number calculated to two decimal places.

By the same token, if I set the Numbers option in the Format/Cell dropdown to 0 decimal places and the formula is changed to Roundup(H3*L3,2), the number that's calculated has no decimal places.

How do I set the spread sheet so that the Roundup formula is controlling what's calculated?

 

[fhqwgads]

roundup is controlling what's calculated
cell format controls what is being displayed

take this table below

Book1
	I	J	K
17	1.51	2	2
18	1.51	2	2
19	3.02	4	3
Sheet34

Cell Formulas
Range	Formula
J17	=ROUNDUP(I17,0)
J18	=ROUNDUP(I18,0)
J19	=J18+J17
K17	=I17
K18	=I18
K19	=K18+K17
I19	=I18+I17

is column I i have the numbers 1.51 and 1.51 and their sum 3.02
if you use roundup on those numbers like in column J they become 2+2=4
in column K i've change the cell format to zero decimal places which displays 1.51 as 2. but when you add the cells together is comes out as 3 instead of 4 because it still treats the numbers as 1.51+1.51=3.02 even though it displays them as 2+2=3

EDIT: sorry that's probably a bad example since using roundup on 3.02 would come out as 4 anyway. what i wanted to illustrate was that the value in J17 is now 2 whereas if you just change the format like in K17 it shows 2 but the value in the cell is still 1.51

 

[joeu2004]

I think you are asking for the format (number of decimal places) to automatically adjust according to the precision in the ROUNDUP expression. That is, if you write =ROUNDUP(...,0), you want the format to display zero decimal places. But if you write =ROUNDUP(...,2), you want the format to display 2 decimal places. Is that right?

If so, technically we cannot do that. The General format might be close to what you want. However, if ROUND(...,2) happens to result in an integer (x.00), the General format will display the integer without the 2-digit zero decimal fraction. Also, if your column is not wide enough, or if the result is more than 11 digits, the General format will not display the value in the manner that you expect.

If I have misunderstood your requirements, I suggest that you provide concrete examples (always!) that show the values in H3 and L3 (15 significant digits), the actual result of the ROUNDUP expression, and the desired appearance of the result that you want.

 

[jkharms]

Thank you. I understand your example. If I may, another two question?:
There are three calculation in a single formula. The first in line is designated for Roundup (i.e., to determine how much material is needed when material can only be purchased in full lengths). The second determines the total cost of material The third then divides that cost by the number of pieces that will be made from the total material purchased. The final number needs to be displayed in two decimals.
Question one: Will using Rooundup as described ensure the quantity of purchased material is correctly calculated?
Question two: Is setting the cell to display two decimals the proper way to show the correct calculated price?

 

[jkharms]

Thank you for that information. I understand what you're saying.

 

[jkharms]

(I may be sending this a second time. If so, I apologize for the confusion.)
Thank you. I understand your example. If I may, another two question?:
There are three calculation in a single formula. The first in line is designated for Roundup (i.e., to determine how much material is needed when material can only be purchased in full lengths). The second determines the total cost of material The third then divides that cost by the number of pieces that will be made from the total material purchased. The final number needs to be displayed in two decimals.
Question one: Will using Rooundup as described ensure the quantity of purchased material is correctly calculated?
Question two: Is setting the cell to display two decimals the proper way to show the correct calculated price?

 

[fhqwgads]

Question one: Will using Rooundup as described ensure the quantity of purchased material is correctly calculated?
Question two: Is setting the cell to display two decimals the proper way to show the correct calculated price?

from the sounds of it maybe roundup? could you provide any sample data like how material measurements get calculated into full lengths and stuff? there might be other formulas that would work better