Senin, 15 Juni 2015

Re: [ExcelVBA] Inline if will not evaluate second condition


> ... the 1 condition is never evaluated if condition 2 is true ...

I assume by "condition 2" you mean the first test, which returns a value of
2. If so, this is correct. The first condition has evaluated as true,
which selects the next argument ("2"), and that's the end of it.

Breaking it down ...

IIf([Finish]<=Date() And [% Complete]<>100,2,

If the project is late and it is not complete, then return 2.

IIf([Start]<=Date() And [% Complete]=0,1,

Otherwise, if it should already have started, but has not, then return 1

IIf([% Complete]=100,3,0)))

Otherwise, if the project is complete, then return 3.

And if none of the above, then return 0.

This presumably is wrong, as I'd have thought the FIRST test should be for
the project being complete, but you haven't actually said what the tests are
supposed to be doing, so I'm just guessing.

I also suspect that trying to combine % complete tests with start and end
dates in the one statement is not a practical way to go. The tests should
probably be separated, and various messages based on the degress of
completeness (e.g. past end date but 95% complete is better than overdue and
not yet started).

I recommend that you not use IIF statements of this complexity - you will
find it easier to read and debug if you use proper IF statements.

Regards, Dave S

----- Original Message -----
From: [ExcelVBA]
Sent: Tuesday, June 16, 2015 6:52 AM
Subject: [ExcelVBA] Inline if will not evaluate second condition

I have a project plan where I have created an if statement however second
condition is not being evaluated. IF I change the order of the if
statements, the first condition is evaluated but not the second.

Correct syntactically but incorrect results the second condition is not
being evaluated so the 1 condition is never evaluated if condition 2 is true
so I think I need to swap the two and add an additional "and condition to
also evaluate the start and finish dates and %complete. I think I need
another and condition but it breaks when I that logic to the formula.
Any suggestions ?

IIf([Finish]<=Date() And [% Complete]<>100,2,IIf([Start]<=Date() And [%

IIf([% Complete]=100,3,0)))

No virus found in this message.
Checked by AVG -
Version: 2015.0.5961 / Virus Database: 4360/10017 - Release Date: 06/14/15


Posted by: "David Smart" <>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (3)
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: to enter the ezine, then search the ARCHIVES for EXCEL VBA.

Visit our ExcelVBA group home page for more info and support files:

More free tutorials and resources available at:




Tidak ada komentar:

Posting Komentar