Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
Or, is there an easier way to do this? Am I just to stuck in my own formula? –  Da Bajan Jul 6 '12 at 13:03
    
Do you intend Date1 to be the whole month (Jan) or only the 1st of Jan? –  datatoo Jul 6 '12 at 14:01
    
Just a specific date. –  Da Bajan Jul 6 '12 at 14:18
    
I was thinking that if I could set a variable on the sheet and increment it every time I made a larger than normal shipment that would do it, but then I would need to do multiple commands in one line of an IF statement - i.e. IF(big shipment) THEN calculate shipment value in this cell AND increment the Variable cell..... Is there any way to do that? –  Da Bajan Jul 6 '12 at 14:21
add comment

1 Answer

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
share|improve this answer
    
Ok, I understand basic programming - but not in the context of Excel. Would this code have to be called in every cell? Lets say we are mapping the shipping cost by day over 3 years, and I want to have several dates where I will have a larger than normal requirement. Do I 'call' this sub() in every cell? And how do I do this? –  Da Bajan Jul 6 '12 at 16:32
    
You can declare a range of cells as a variable and then use your select case function to assign the proper value. superuser.com isn't the best place to ask this type of question, I suggest trying on Stackoverflow.com The VBA experts there will help you develop your code. Check out this post that I had about a select case that I was trying to get working, it may help show you how to approach your problem stackoverflow.com/questions/11215790/… –  Salmonerd Jul 7 '12 at 2:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.