If statement <> 25%

Skinnybob

New Member
Joined
Jul 11, 2018
Messages
5
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
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

New Member
Joined
May 8, 2018
Messages
5
Hi Skinnybob,

Set up your worksheet like this:
A
B
Master Case
105
Replenishment
195
Items to Order
210
Master Cases needed
2

<tbody>
</tbody>

The function for Items to order is:
Code:
=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

New Member
Joined
May 8, 2018
Messages
5
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,004
Messages
5,526,225
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top