Rounding Threshold

[jws]

Hello,

I am trying to build a calculation that rounds up customer requests in increments of 12, based off a set percentage threshold to roundup at.

Column A would have percentage % to round up to the next 12, example 0.01% would roundup the next 12 at customer requests >= 12.12

Threshold Percentage to round up to the next 12 as follows:

0.01% would roundup at 12.12 to 24, below would get 12
0.25% would roundup at 15.00 to 24, below would get 12
0.50% would roundup at 18.00 to 24, below would get 12
0.76% would roundup at 21.12 to 24, below would get 12

If the request is not clear please let me know.

Thanks for looking

 

[mhillmann]

I am not entirely sure I understand what you want but this is what I came up with.

Column A - percentages
Column B - number customer ordered

=IF(MOD(B2, 12)>(12*A2), B2-MOD(B2,12)+12, B2-MOD(B2,12))

 

[jws]

This works very well, thanks!

solved

 

[jws]

Hello - I am revisting this since I did notice there is an issue, at least one I am experiencing. The values highlighted in red are not going up to the next value based on the percentage/rounding point.

Example, a value of 9 rounded in increments of 4, at a 25% threshold should return 12 in this example. Is there an order of operation, or parenthesis I am misssing possible?

 

[jws]

Ok - I found the issue

I needed to add an “ = “ sign below, now it returns the values expected for me.

=IF(MOD(L7, $F$1)>=($F$1*$F$3), L7-MOD(L7,$F$1)+$F$1, L7-MOD(L7,$F$1))


SOLVED AGAIN, thanks