# If statement <> 25%

#### Skinnybob

##### New Member
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

### 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
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
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
Biocide,

Interesting way of looking at the problem.

#### JAndy_CO

##### New Member
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

##### New Member
BiocideJ, JAndy_CO

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

Replies
5
Views
159
Replies
3
Views
238
Replies
4
Views
111
Replies
6
Views
380
Replies
2
Views
164

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