Rounding With Formula Issue

See the syntax of ROUNDUP, with examples, here.

You don't mention what you are trying to do, but the formula may look something like this in the formula editor:

You would type this to enter the formula:

=ROUNDUP(B2*5,5)

Note that there should be no leading = visible within the editor. You type a = in a cell to call up the formula editor, but then don't add another = .

As illustrated you will use , in the formula if . is the decimal separator used in your region.

If instead your regions uses , as a decimal separator then use ; instead of . the formula.

SG

Create3D,

One thing that might help is to read the description of the ROUNDUP function. When you are creating a formula, a list of the functions will be on the right and the description of the selected function will be at the bottom. Find ROUNDUP on that list and read about how to use it. You will see why the first formula in your latest screenshot did not give you the answer you wanted.

The problem with the second formula is just math. You divided by 5, rounded to no decimal places, then multiplied by 4. Multiplying by 4 after dividing by 5 is unlikely to get you to where you want to be if you want to "multiply by 4 then round up to a multiple of 5". To do exactly what you stated it would be

=ROUNDUP(B3*4/5,0)*5

HI C,

ROUNDUP is not the function for what you want to do.

From the description for ROUNDUP in the Function Browser:

The ROUNDUP function returns a number rounded away from zero to the specified number of places. Both arguments are number values.

ROUNDUP(num-to-round, digits)

num-to-round: The number to be rounded.

digits: The number of digits you want to retain, relative to the decimal point. A positive number represents digits (decimal places) to the right of the decimal point to include. A negative number specifies digits to the left of the decimal point to replace with zeros (the number of zeros at the end of the number).

The formula shown in your first post should drop the = sign when the Formula Editor is closed (by clicking the green checkmark button, but will return this error message:

Numbers does not recognize the last 5 in your formula as it is not separated from the first

argument with a comma. Inserting that comma changes the formula to this:

Which returns the correct result, and in this case, the one you want.

Cell C2 contains a similar formula. It differs from the formula above in only two specifics:

1. The parentheses enclosing B2*5 are removed, as they are not necessary to ensure the order of calculations.
2. The multiplier is changed from 5 to 4 to avoid a product that 'is already a multiple of 5.

This formula also returns a correct result.

ROUNDUP is told to ROUND the product of 1 and 4 to the nearest value of that product that is equal to or greater than the actual product and can be expressed with a number including (up to) five digits after the decimal point.

That value is 4.00000. The trailing zeroes are not displayed.

From your comments, though, this is not the result you are trying to get.

You want the result to be the nearest integer value that is a multiple of 5 and is greater than or equal to the actual result of the multiplication.

MROUND will take you part way there:

Results of the multiplication are shown in column C. The rounded result is shown in column D.

The result is rounded to the nearest multiple of 5, but not always to the nearest multiple of five that is equal to or greater than the actual product.

Getting there requires adding a bias to the amount to be rounded. The resulting formula and its results are shown below in column E.

Note that each of the formulas shown is independent of the others, snd references only the data in column B.

Text version of formula for Copy & Paste: MROUND(B2*4+5/2,5)

Regards,

Barry

= MROUND(B2,5) will round to the closest multiple of 5 (up or down). Positive numbers only.

The formula given below will round "up" (away from zero) to the nearest multiple of 5. Positive and negative numbers allowed.

=ROUNDUP(B2÷5,0)×5

Create3D wrote:

if I were to multiply the whole number of B2 by 4 and wanted Numbers to round up to a multiple of 5 is that possible?

Why not just try it. After the * type 4 instead of 5.

SG