Round up/down to closest multiple

[DJarvis1]

Hi, i have some excel knowledge but a bit stumped here... an example in cells are
A1 16
B1 a formula that ends up with 39
B2 a formula that ends up with 41

The goal is to have B1 & B2 move up or down to the nearest multiple of A1

So B1 will return 32 & B2 will return 48.

Is there a any way of telling B2 to move to its next 'lower multiple' IF cell B1 had to go 'Down' to hit it's closest multiple of A1.

I don't wish to use vba....
Hopefully a single cell solution.

Thanks.

 

[Rick Rothstein]

Hi, i have some excel knowledge but a bit stumped here... an example in cells are
A1 16
B1 a formula that ends up with 39
B2 a formula that ends up with 41

The goal is to have B1 & B2 move up or down to the nearest multiple of A1

So B1 will return 32 & B2 will return 48.

Is there a any way of telling B2 to move to its next 'lower multiple' IF cell B1 had to go 'Down' to hit it's closest multiple of A1.

I don't wish to use vba....
Hopefully a single cell solution.

It is not clear where the B1 and B2 values have come from, but if they are constants, then you will need vba (a cell can only contain a constant or a formula, not both). If the values come from a formula, then you can wrap the MROUND function around your formula. Let's say your formula is =X1, then change the formula to this...

=MROUND(X1,A1)

If the user is typing in the constant, then they will have to type a formula in instead. So, if they wanted to type in 39, they would have to type this in instead...

=MROUND(39,A1)

 

[dcardno]

Use the MROUND() function

 

[DJarvis1]

No, b1 & b2 have formula that could result in all kinds of numbers./i just used these numbers as a example) The only constant, or plain number is in a1