I have a situation where I need to calculate shipping values based on the length of the supply chain. Easy, however I need to have instances where an increased amount is required based on specific date criteria.
My example is as follows:
Date1= 1/1/2013 (Jan) - ship 50% more than usual
Date2= 2/1/2013 (Feb) - ship 25% more than usual
Date3= 3/1/2013 (Mar) - ship 25% more than usual
Supply chain lengthis:
- June - October 100 days
- November - March 140 days
- April - June 100 days
The issue I have is that as there is an increase in the number of days, my formula:
IF( Date1-(Supply chain length + any extra days)=today's date, shipvalue+(shipvalue X 50%), IF( Date2-(Supply chain length + any extra days)=today's date, shipvalue+(shipvalue x 50%) IF( Date2-(Supply chain length + any extra days)=today's date, shipvalue+(shipvalue x 50%), IF( preceding cell<>0,shipvalue, 0) ) ) )
Now the problem with this is that if the length of the supply chain increases then the formula misses all but the 1st increase. So, I thought of adding a variable that would be incremented and checked every time you made an increased shipping amount.
So, how do I do both the calculation for the increased shipping value, and set the variable in one part of the IF statement?