# Thread: Help with IF statement (2000 sp3)

1. ## 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,

2. ## Re: Help with IF statement (2000 sp3)

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

3. ## 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....

4. ## 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. ## 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,

6. ## 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. ## 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,

8. ## 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. ## 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.

10. ## 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,