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

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?

-
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

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%)