Brackets.

[Gaz_Thornton]

I'm using an 'IF' argument and I want the 'NO' response to provide a quantity rounded up to the next multiple of 10.

I've got as far as "=IF(E5>80,0,"

My E5 quantity is less than 80 and I want the E5 value to be used, rounded up to the next multiple of 10.

Should I use the 'ISO CEILING' function or the 'CEILING.MATH' function to complete the NO argument, and where do I put the brackets? 

[#2]

@Gaz_Thornton 

Perhaps you mean this:

=IF(E5>80,0,CEILING.MATH(E5,10))

[#3]

@Gaz_Thornton 

To achieve your desired functionality in the "NO" argument of your IF formula, you can use either the CEILING.MATH or ROUNDUP function, placed within brackets after the comma separator. Here's how:

Using CEILING.MATH:

Excel

=IF(E5>80,0,CEILING.MATH(E5,10))

This formula checks if the value in cell E5 is greater than 80. If yes, it returns 0. Otherwise, it applies the CEILING.MATH function, which rounds up the value in E5 to the nearest multiple of 10.

Using ROUNDUP:

Excel

=IF(E5>80,0,ROUNDUP(E5,-1))

This formula works similarly, but using the ROUNDUP function. However, since ROUNDUP rounds to the nearest integer by default, we need to specify an argument of -1 to round up to the nearest multiple of 10.

Which function to use?

Both functions accomplish the same goal, but there's a subtle difference:

• CEILING.MATH always rounds up, even if the number is already a multiple of 10. For example, rounding 50 with CEILING.MATH(50,10) returns 60.
• ROUNDUP only rounds up if the number is not already a multiple of 10. For example, rounding 50 with ROUNDUP(50,-1) returns 50.

Choose the function based on your desired behavior:

• Use CEILING.MATH if you always want to round up to the next multiple of 10, even for multiples already.
• Use ROUNDUP if you want to keep exact multiples of 10 unchanged and only round up numbers not already multiples.

Remember to adjust the cell references (E5) and your specific condition in the IF statement based on your needs.

[#4]

@Gaz_Thornton 

As for the formula style, better is to use spaces and/or multiline to avoid extra errors with formula parts

=IF(E5>80,0,CEILING.MATH(E5,10))

=IF(E5 > 80, 0, CEILING.MATH(E5, 10) )

=IF(
    E5 > 80,
    0,
    CEILING.MATH(E5, 10)
)

Variant of above could be

=10*(E5<=80)*(INT((E5-1)/10)+1)

=10 * (E5 <= 80) * (INT((E5 - 1) / 10) + 1)

=10 *
 (E5 <= 80) *
 ( INT((E5 - 1) / 10) + 1 )

[#5]

Thnx, Riny - I think I've sorted it. (Was changing bracket types for the 2nd calculation)

[#6]

Thnx - I think I've sorted it. (Was changing bracket types for the 2nd calculation)