If statement <> 25%

[Skinnybob]

Hi All,

I’m trying to create a replenishment tool, and I want to come as close to shipping master cases as possible.
Current replenishment = 190
Master Case = 105

I am trying to create a formula that looks at the current replenishment (190), and if it is within 25% of however many master cases (210), then return the 210. If it’s over the 25% threshold, then return the 190.

My challenge is the master case quantity may mary. i.e. it may take anywhere from 1-5 master cases to replenish.

Any help would be appreciated

 

[BiocideJ]

I'm relatively sure I understand what you are trying to do.
This formula assumes the current replenishment is in cell B2 and the Master Case is in B3 (adjust as necessary)
=IF(AND(B2>=ROUND(B2/B3,0)*B3*0.75,B2<=ROUND(B2/B3,0)*B3*1.25),ROUND(B2/B3,0)*B3,B2)

Please test this formula out and if there are instances where it is incorrect, it would be helpful for you to give a few examples of given input and expected output.
You didn't explicitly state the output in your example, but since 190 is within 25% of 210, I believe the output is supposed to be 210.

 

[JAndy_CO]

Hi Skinnybob,

Set up your worksheet like this:[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]Master Case
[/TD]
[TD]105
[/TD]
[/TR]
[TR]
[TD]Replenishment
[/TD]
[TD]195
[/TD]
[/TR]
[TR]
[TD]Items to Order
[/TD]
[TD]210
[/TD]
[/TR]
[TR]
[TD]Master Cases needed
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]

The function for Items to order is:

=IF((QUOTIENT(B2,B1)+1)-B2/B1>0.25,B2,B1*(QUOTIENT(B2,B1)+1))

The function for Master Cases Needed is: =B3/B1

This basically tells excel to check to see what the remainder of the quotient is, and if it is less than 25%, it returns the number of items to order in quantities of master cases. If it's more than 25%, it returns the replenishment value and a decimal value for cases needed to be ordered.

Hope this helps!

 

[JAndy_CO]

Biocide,

Interesting way of looking at the problem.

 

[JAndy_CO]

Also, you can use the formula: =QUOTIENT(B3,B1) to get the number of full master cases you need to order
AND you can use the formula: =MOD(B3,B1) to get the number of individual items you need to order to meet full replenishment

 

[Skinnybob]

BiocideJ, JAndy_CO

both work really well. Thank you very much for your help.