Formula for rounding up a number

Hi Megiddo,

The synatax for CEILING is:

CEILING(num-to-round,multiple-factor)

Jerry has provided an example in which the number to be rounded is 4.76, and the multiple factor is 0.05 (ie. to the next nickel). The result is 4.80.

For your use, the number to be rounded is the result of your discount formula, (Retail Prices :: 4x5 Dark Red*0.8)

Substitute that expression for the number 4.76, and you have the formula that will work for you.

One thing that bothers me about CEILING is shown in the table below.

Column B, labeled retail contains the retail prices, all in even dollars.

Column C, labeled discount has your 20% discount applied. As the retail prices here are all even dollar amounts, the discount is a multiple of 20 cents, and the prices, extended to a third decimal place, all end with at least two zeroes.

Column D shows the same calculation, with CEILING applied to the results. Note that for most amounts, CEILING has moved the amount up despite the original calculation having a result that is already a multiple of 0.05. The only exceptions are when the original (retail) price is a power of 2.

In column E, I've added another step, and rounded the result of the discount calculation to two decimal places before applying CEILING. For all the examples (except row 7, where the retail price is $5), this provides what I'd see as a more correct result.

Formulas:

Column C (discounted): =B*0.8

Column D (Ceiling applied): =CEILING(B*0.8,0.05)

Column E (rounded, then CEILING applied): =CEILING(ROUND(B*0.8,2),0.05)

"This is my first numbers project. :-)"

Then the best advice available is to download and spend some time reading the two excellent resources available through the Help menu in Numbers.

The numbers '09 User Guide will give you an overview of the application itself. I'd recommend reading at least the first three chapters, then diipping into the rest when the need arises.

The iWork Formulas and Functions User Guide is a great reference when you're trying to write formulas. It lists all of the functions supported by numbers, with a description of each, suggestions regarding where it is useful, and at least one example for each function.

Regards,

Barry

megiddo777 wrote:

Thanks Jerry

I'm trying this:

=Retail Prices :: 4x5 Dark Red*0.8 =CEILING(4.76, .05)

FALSE keeps coming up in the cell when I use the formula above. Would it still work if I plugged in a new retail price on the Retail Price sheet?

Sorry to be so dense. This is my first numbers project. :-)

Meg,

Everyone has a first project, in whatever field, and so you're brave to come here for help and to get a good start.

When I used the expression: "=CEILING(4.76, .05)", I used literal, or constant, values in the parameter fields of the CEILING function. I could just as well used variables, where a cell location where the data is stored is given and whatever value is in that cell is used in the function. Further, the parameter can be expressed as a formula containing other math and other functions.

CEILING requires two inputs: First, the value to be rounded up, and Second, the multiple to be rounded to. The two values are separated by a comma to keep them distinct from one another. In my example I used your first sample value, 4.76, for the first parameter. I also could have used the name of a cell where the 4.76 is stored, or I could have used the equation for the amount calculation, your "Retail Prices :: 4x5 Dark Red*0.8".

I hope you find this interesting and easy enough for you to keep up the learning. Programming is fun.

Jerry

m,

To round 4.76 to the next .05 up, use:

=CEILING(4.76, .05)

Regards,

Jerry

I think Barry was trying to say to enter formulas as follows:

Column C (discounted): =B*0.8

Column D (Ceiling applied): =CEILING(B*0.8,0.05)

Column E (rounded, then CEILING applied): =CEILING(ROUND(B*0.8,2),0.05)

C2=B*0.8

D2=CEILING(B*0.8,0.05)

E2=CEILING(ROUND(B*0.8,2),0.05)

this notation means select C2, then type (or paste) "=B*0.8" WITHOUT the double quotes

then you can select C2 through E2, then grap (click and hold) on the little circle at the bottom right corner of the selection (the cursor will change to a bold "+"), then drag straight down to fill the formula to other cells.

=Retail Prices :: 4x5 Dark Red*0.8 =CEILING(4.76, .05)

you performed a test to see if "Retail Prices :: 4x5 Dark Red*0.8" is equal to "CEILING(4.76, .05)" (after they are evaluted to a numerical value).

Then you typed: