Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with IF statement (2000 sp3)

    All,
    Greetings! I have a calculation that I need some help with. Basically, I am counting the number of days a line item is OPEN based upon a look up table. The issue I have is that once the line 'closes' the day count continues. I have a counter in a prevoius cell. (0 zero = closed, (1) = open). how can I stop the counter if the prior cell = zero?

    Current forumla in B2:
    =VLOOKUP(TODAY(),Calendar!$A$1:$C$3489,3)-VLOOKUP(G1,Calendar!$A$1:$C$3489,3)

    Thanks,
    Brad

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with IF statement (2000 sp3)

    Can you post a small demo workbook, with an explanation of what is what?

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with IF statement (2000 sp3)

    Hans,
    Here you go! The date fields are Received and ARA close date. The look up looks at the info on the calendar tab.

    You might remember this workbook....

    Brad

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with IF statement (2000 sp3)

    Thanks. Now, could you explain clearly and in detail what you expect the formula to do, based on the workbook you posted?

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with IF statement (2000 sp3)

    Hans,
    I need for column B to stop counting the open days, and calculate the number of days open for the two dates. Based upon the column A being 1 (open) or (0 being closed).

    Thanks,
    Brad

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with IF statement (2000 sp3)

    Please provide a concrete example. I don't have the slightest idea what you want at this point.

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with IF statement (2000 sp3)

    Hans,
    I would like Column B to count the number of days open until the counter in column A is set to 1. Once the counter is set to 1, then stop days open field at that point and base the count on ARA closed - Received. The formula for counting in column B is

    =VLOOKUP(TODAY(),Calendar!$A$1:$C$3489,3)-VLOOKUP(G1,Calendar!$A$1:$C$3489,3)

    Where G1 is the date received. The dates bounce against the calendar information.


    Hopefully this is more clear.
    Thanks,
    Brad

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with IF statement (2000 sp3)

    > Hopefully this is more clear.

    No, not at all. Could you please provide a specific example of the calculation you expect? Just repeating the formula doesn't help, since I don't understand it.

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with IF statement (2000 sp3)

    First, I think that formula has an error in it for the formula in cell B2, the reference to cell G1 in the second VLOOKUP function should be G2. Then the formula should be copied down.

    Second, You could save yourself a lot of problems if you could use the NETWORKDAYS function that comes in the Analysis Toolpak addin that comes with Excel. You would have to go to "Add-Ins..." on the Tools menu and put a tick mark next to "Analysis Toolpak" before you can use this function. If you use NETWORKDAYS, then instead of your Calendar sheet, you would just have to come up with table of holidays which would be a lot easier to maintain than your Calendar.

    Third, if you still want to use your Calendar worksheet, then I think that this formula does what you want:

    <code>
    =IF(A2<>0,VLOOKUP(TODAY(),Calendar!$A$1:$C$2193,3)-VLOOKUP(G2,Calendar!$A$1:$C$2193,3,FALSE),VLOOKUP( TODAY(),Calendar!$A$1:$C$2193,3,FALSE)-VLOOKUP(X2,Calendar!$A$1:$C$2193,3))
    </code>


    If you would also like to eliminate the use of column A, then I this should also work:

    <code>
    =IF(X3="",VLOOKUP(TODAY(),Calendar!$A$1:$C$2193,3)-VLOOKUP(G3,Calendar!$A$1:$C$2193,3,FALSE),VLOOKUP( TODAY(),Calendar!$A$1:$C$2193,3,FALSE)-VLOOKUP(X3,Calendar!$A$1:$C$2193,3))
    </code>

    One additional comment. Did you realize that you have Calculations set to Manual in that workbook? That threw me for a real loop for a while trying to figure out why I couldn't get the VLOOKUPs to work like I thought they should.
    Legare Coleman

  10. #10
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with IF statement (2000 sp3)

    Legare,
    Thanks for the details. I inherited the spreadsheet and have been forever trying to get it up and running correctly. I will investigate your comments. Also, I will adjust the spreadsheet per your reccomendations.

    Will let you know the results.

    Thanks,
    Brad

Posting Permissions

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