Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Help needed with nested IF statement - Excel 2000

    I need some help with a formula, please. The scenario sounds complicated, but isn't really! I will describe it in words, and then attach a file. Hopefully someone will enjoy the challenge, and help me out in the process...

    My model is designed to track costs of hired plant. Plant hire companies typically have a weekly hire rate for items, and an e/o rate for additional days can be derived, normally by dividing the weekly rate by 5. In the simplest scenario, you can enter the on hire date, the off hire date, the weekly rate, and my model will calculate the hire charge incurred. My model does this by calculating the total weeks and e/o days of the hire period, and using multiplying these by the weekly rate (for full weeks) and the dervied daily rate for the e/o days.

    But things get complicated, and there are two specific scenarios I need to deal with. The main one is that I need to calculate the hire charges up to a defined "cut off" date which may come part way through the hire period (for a period end accounting exercise) and the second is that there may be a suspended hire period, typically over the Xmas period, where the hire company suspends the hire charge, although the plant is still on site.

    The attached file should be fairly self explanatory, if the above description makes sense.

    The function I need help with is that in Column V.

    This function is designed to calculate the number of chargeable days up to a period end cut off date. It needs to be able to cope with 5 different scenarios, and I have used a nested if statement to achieve this. I think it is working with 4 of the scenarios, but not the fifth.

    These are the scenarios, in the order that they are covered in my existing formula:-

    1 - If the cut off date is later than the off hire date, return the total hire days less the suspended period hire days

    2 - If the cut off date is later than the suspended period finish date, return the cut off date, less the on hire date, less the suspended period days (if any)

    3 - If the cut off date is later than the suspended period start date (ie. if falls within the suspended hire period), return the suspended period start date less the on hire date

    4 - If the cut off date is earlier than (any) suspend hire date, return the cut off date less the on hire date

    5 - (and this is the one I need help with!) If the cut off date is earlier than the on hire date, return zero

    The formula is not working because it is currently returning a negative value for scenario 5, whereas I want it to return a zero.

    I have attempted to add in an additional if statement to cover the fifth scenario, but it still returns a negative.

    Many thanks in anticipation, and sorry my explanation is a bit long-winded!

    Neil
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The Negative in row 21 comes because it is calculating as a "2" due to the blank in H21. The negative in 19 since it calculates as not a 1,2 or 3, and thus by default it is a "4" (your equation has no 5th option.

    Check option 5 first so the formula is not fooled by the blanks:

    =IF($C$2<B7,0,IF($C$2>C7,D7-I7,IF($C$2>H7,$C$2-B7-I7,IF($C$2>G7,G7-B7,$C$2-B7))))

    You still may need some test for Option4 since 5, 1,2, and 3 are explicitly tested. In the formula above, 4 comes from not meeting any of the other criteria

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    neil (2011-05-13)

  4. #3
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Steve

    Firstly apologies for the delay in replying - this is the first chance I have had to test your suggestion and I'm pleased to say it works!

    I'm glad (and grateful) that you weren't put off by my rather wordy opening post - I couldn't find a way to explain what I was trying to do in any less words (though I did try!).

    But most of all, I really appreciate the solution you offered and the trouble you went to.

    Many thanks.

    Cheers!

    Neil

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I am glad I could help. I am generally not put off by the lengthy explanations if it helps me understand. Too often we get questions of the type: "my car doesn't start what is wrong" and we have to wade through dozens of things it is not (no gas, no key, battery dead, etc) before we understand.

    A comment for future questions
    Explicitly Speciify the cells which have "bad answers" and explain what the value should be and why. Also you should try to include at least one example with each of your sets. If you can find some that work and also some that don't work, that is also good, but I suspect you only had issues with the 4s and 5s...

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •