Excel 2010 - more than 1 calculation within an IF() statement

Posted by Da Bajan on Super User See other posts from Super User or by Da Bajan
Published on 2012-07-06T12:59:55Z Indexed on 2012/07/06 15:18 UTC
Read the original article Hit count: 174

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:

  • Shipvalue = 100
  • 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 length is:

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

© Super User or respective owner

Related posts about excel-2010

Related posts about worksheet-function