Can it be done? Round "-" IF +

Joe Goetschel

I can do this with a metic sheet but I would like to try and do it all in 1 formula. NOT sure it can be done!

I currently have a formula that gives me a number. Let's call it 17.35

I then round the number to split them up into 2 different cells

Row 1 = 17.35

Row 2 =ROUND([Column1]1, 0) Answer is 17 (In Excel I use TRUNC)

Row 3 =[Column1]1 - [Column1]2 Answer is 0.35

Row 4 =IF([Column1]3 < 0.25, [Column1]2, IF([Column1]3 > 0.75, [Column1]2 + 1, [Column1]2 + 0.5)) Answer is 17.50

Dossy Shiobara

It sounds like you want MROUND() which rounds to the nearest multiple, but your specific example's boundaries defined by your IF() is slightly different than MROUND()'s behavior.

Try:

=MROUND([Column1]1, 0.5)

When [Column1]1 = 17, your Row 4 value will be 17 and so will the MROUND() value.

When [Column1]1 = 17.25, both will be 17.5.

When [Column1]1 = 17.76, both will be 18.

When [Column1]1 = 17.74, both will be 17.5.

BUT! When [Column1]1 = 17.75, your Row 4 value will be 17.5 while the MROUND() value will be 18.

If you need the exact behavior of your IF expression, you can still do it in a single formula but it will be very long:

=ROUNDDOWN([Column1]1, 0) + IF([Column1]1 - ROUNDDOWN([Column1]1, 0) < 0.25, 0, IF([Column1]1 - ROUNDDOWN([Column1]1, 0) > 0.75, 1, 0.5))

Hope this helps,

Dossy

Dossy Shiobara

It sounds like you want MROUND() which rounds to the nearest multiple, but your specific example's boundaries defined by your IF() is slightly different than MROUND()'s behavior.

Try:

=MROUND([Column1]1, 0.5)

When [Column1]1 = 17, your Row 4 value will be 17 and so will the MROUND() value.

When [Column1]1 = 17.25, both will be 17.5.

When [Column1]1 = 17.76, both will be 18.

When [Column1]1 = 17.74, both will be 17.5.

BUT! When [Column1]1 = 17.75, your Row 4 value will be 17.5 while the MROUND() value will be 18.

If you need the exact behavior of your IF expression, you can still do it in a single formula but it will be very long:

=ROUNDDOWN([Column1]1, 0) + IF([Column1]1 - ROUNDDOWN([Column1]1, 0) < 0.25, 0, IF([Column1]1 - ROUNDDOWN([Column1]1, 0) > 0.75, 1, 0.5))

Hope this helps,

Dossy