Need help with a Nested IF question in Excel

October 2, 2015 at 07:37:02
Specs: Macintosh
I am trying to write a nested IF function that pulls from cells B2,D2,E2, and F2. I have the formula written and all of those cells are highlighted when I am working on the formula, but it will only pull from E2,F2. Any help? The dates are arbitrary right now, but Im hoping to eventually use this with actual dates.

=IF(F2>1/1/2000,(WORKDAY(F2,5,A13:B34)),IF(F2<1/1/2000,(WORKDAY(E2,5,A13:B34)),IF(E2<1/1/2000,(WORKDAY(D2,5,A13:B34)),IF(D2<1/1/2000,(WORKDAY(B2,5,A13:B34)),F))))

message edited by mvh


See More: Need help with a Nested IF question in Excel

Report •

#1
October 2, 2015 at 08:40:11
I figured out the formula that works!

=IF(E2>1/1/2000,(WORKDAY(E2,2,A13:B34)),IF(D2>1/1/2000,(WORKDAY(D2,1,A13:B34)),IF(C2>1/1/2000,(WORKDAY(C2,11,A13:B34)),IF(B2>1/1/2000,(WORKDAY(B2,1,A13:B34)),FALSE))))


Report •

#2
October 2, 2015 at 09:06:22
In an IF() statement, processing STOPS when it hits a True value,
now since your first statements is

IF F2 > 01/01/2000 and IF F2 < 01/01/2000

One of those two statements should return True, so processing Stops
UNLESS you happen to use the Date 1/1/2000 in which case, since your using JUST the Greater Than and Less Than symbols both statements return FALSE and you continue to the next clause.

I would suggest Two changes, turn either
the Greater Than symbol >
or the Less Than symbol <
into a Greater Than or Equal to symbol >=
or a Less Than or Equal symbol <=

Also change your Date from the value 1/1/2000,
which in the formula evaluates out to the Number 0.005,
by using the DATE function, like DATE(2000,1,1)
this will avoid any ambiguity in the formula.

So something like:

=IF(F2>=DATE(2000,1,1),(WORKDAY(F2,5,A13:B34)),
IF(F2<DATE(2000,1,1),(WORKDAY(E2,5,A13:B34)),
IF(E2<DATE(2000,1,1),(WORKDAY(D2,5,A13:B34)),
IF(D2<DATE(2000,1,1),(WORKDAY(B2,5,A13:B34)),F))))

MIKE

http://www.skeptic.com/


Report •

#3
October 2, 2015 at 09:08:22
In your second formula:

=IF(E2>1/1/2000,(WORKDAY(E2,2,A13:B34)),IF(D2>1/1/2000,(WORKDAY(D2,1,A13:B34)),IF(C2>1/1/2000,(WORKDAY(C2,11,A13:B34)),IF(B2>1/1/2000,(WORKDAY(B2,1,A13:B34)),FALSE))))


You never check the F2 cell, so now I have no idea what your doing.

MIKE

http://www.skeptic.com/


Report •
Related Solutions


Ask Question