2

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?

J. Mini
  • 282
Da Bajan
  • 21

1 Answers1

1

You need to look into writing a Select Case statement within VBA on the developer ribbon. As of right now you have 4 if statements all trying to work off of one another and as you are finding out, this just leads to problems. If you start writing this code within VBA you will have plenty of coding power to accomplish what you want to do.

Private Function shippingvalue()
Select Case cell.value
    Case Date1-(Supply chain length + any extra days)
        today's date,shipvalue+(shipvalue X 50%)
    Case your next case
        your next assigned value
End Select
End Function
Jared
  • 349