Logical (If) formula for safety stock consumption

sanket_sk

Board Regular
Joined
Dec 27, 2016
Messages
65
Office Version
  1. 365
Platform
  1. Windows
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

Could you please help develop this formula.

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
Joined
Jan 15, 2007
Messages
23,985
=(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)))
 

Watch MrExcel Video

Forum statistics

Threads
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?

Disable AdBlock

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
Top