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
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 | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Spare description | Year | Half Year | Quarter | Last Month | Safety Stock | Remarks | ||
| 2 | FENDER W/O HOLE LH | -7 | -6 | -6 | -7 | -7 | IN G3 -Formula with yearly consumption | ||
| 3 | FENDER W/O HOLE RH | -6 | -6 | -3 | -5 | IN G4 -Formula with Half year consumption | |||
| 4 | FENDER W/HOLE LH | -4 | -2 | -3 | IN G5 -Formula with quarter consumption | ||||
| 5 | FENDER W/HOLE RH | 4 | 4 | IN G6 -Formula with month consumption | |||||
Sheet1 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F2 | F2 | =(B2*30%)+(C2*30%)+(D2*25%)+(E2*15%) |
| F3 | F3 | =(C3*25%)+(D3*50%)+(E3*25%) |
| F4 | F4 | =(D4*50%)+(E4*50%) |
| F5 | F5 | =(E5*100%) |
