# Logical (If) formula for safety stock consumption

#### sanket_sk

##### Board Regular
Hi All,

Greetings!!!

I am working on safety stock levels of spares which is based on Year, Half Year, Quarter & month consumption of particular spare.

I have given some weightage to each consumption period.

Actual formula looks like this - =(C3*30%)+(D3*30%)+(E3*25%)+(F3*15%) - it is basically sum of 30% weightage of Year, 30% Weightage of half year, 25% weightage of quarter and 15% weightage of Month.

However there are cases where we introduce new spares and consumption for these spares consumption may not available for all 4 type of period and I want to add different logics in same formula like if we do not have yearly value, formula should calculate value based on Half year, Quarter and month consumption and so on.

Logics will be like this

If Year value is not available logic should be this =(C3*25%)+(D3*50%)+(E3*25%)

If Year and half year value is not available logic should be this =(D4*50%)+(E4*50%)

If Year ,half year and quarter value is not available logic should be this =(E5*100%)

I want this single dynamic formula where excel should look for value and switch formula accordingly

Details as below
Book1
ABCDEFG
1Spare descriptionYearHalf YearQuarterLast MonthSafety StockRemarks
2FENDER W/O HOLE LH-7-6-6-7-7IN G3 -Formula with yearly consumption
3FENDER W/O HOLE RH-6-6-3-5IN G4 -Formula with Half year consumption
4FENDER W/HOLE LH-4-2-3IN G5 -Formula with quarter consumption
5FENDER W/HOLE RH44IN G6 -Formula with month consumption
Sheet1
Cell Formulas
RangeFormula
F2F2=(B2*30%)+(C2*30%)+(D2*25%)+(E2*15%)
F3F3=(C3*25%)+(D3*50%)+(E3*25%)
F4F4=(D4*50%)+(E4*50%)
F5F5=(E5*100%)

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### mikerickson

##### MrExcel MVP
=(B2*.3) + C2*IF(B2=0,.25,.3) + D2*(IF(C2=0,.5,IF(B2=0,.5,.25)) + E2*(IF(D2=0,1,IF(C2=0,.5,IF(B2=-,.25,.15)))

Replies
0
Views
54
Replies
2
Views
97
Replies
1
Views
429
Replies
1
Views
50
Replies
5
Views
253

1,129,919
Messages
5,638,989
Members
417,061
Latest member
thematulaak

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back