Rounding to a quantity divisible by another listed quantity

[DSEIDEN76]

I'm having a math/excel problem. I have a sheet of values. In column A I have package quantities, column B a value that our system says to order. In column C I'd like to produce a value that is the nearest divisible number to what shows in column A.

A B C
PACKAGE SYS ORDER
50 20 (NEED FORMULA FOR THIS TO = 50)
40 60 (THIS WOULD NEED TO = 80)
35 40 (THIS WOULD NEED TO = 35)

The sheet will have a variable amounts of rows so i'll need to copy that to the last row as well.

 

[mole999]

https://support.office.com/en-us/article/CEILING-function-0A5CD7C8-0720-4F0A-BD2C-C943E510899F

 

[mikerickson]

Up until the 35,40 >> 35 example, I though that you were rounding up.
If you are rounding either direction, wouldn't the 50,20 desired result be 0?

 

[DSEIDEN76]

Good point. I think the majority of the time I'll want to round up since we're ordering product and don't want to run out, but if we're slighting above the package quantity needed we'd want to round down. I guess a round up formula would be okay and then we could manually look at it from there.

 

[Marcelo Branco]

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

PACKAGE​

[/td][td]

SYS​

[/td][td]

ORDER​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

50​

[/td][td]

20​

[/td][td]

50​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

40​

[/td][td]

60​

[/td][td]

80​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

35​

[/td][td]

40​

[/td][td]

35​

[/td][/tr]
[/table]

Formula in C2 copied down
=IF(CEILING(B2,A2)-B2>ABS(B2-A2),FLOOR(B2,A2),CEILING(B2,A2))

M.

 

[DSEIDEN76]

The CEILING function worked perfectly. THANK YOU!

 

[Marcelo Branco]

You are welcome. Glad to help

M.