nested IF returns "false" instead of if false

K

konkrash

New Member
Joined
Aug 11, 2016
Messages
10
hi guys, kinda new to xl and i have this problem with a somewhat complicated nested IF.

the formula:
=IF(A120=IFNA(VLOOKUP(A120,$R$7:$R$57,1,FALSE),A120+1),
IF(AND(WEEKDAY(A118)=7,WEEKDAY(A119)=7,WEEKDAY(A120)=1),A120,
IF(AND(WEEKDAY(A118)=7,WEEKDAY(A119)=1,WEEKDAY(A120)=1),A120,
IF(AND(WEEKDAY(A118)=1,WEEKDAY(A119)=1,WEEKDAY(A120)=1),A120+1,


IF(AND(WEEKDAY(A118)=1,WEEKDAY(A119)=1,WEEKDAY(A120)=2),A120,
IF(AND(WEEKDAY(A118)=1,WEEKDAY(A119)=2,WEEKDAY(A120)=2),A120,
IF(AND(WEEKDAY(A118)=2,WEEKDAY(A119)=2,WEEKDAY(A120)=2),A120+1,


IF(AND(WEEKDAY(A118)=2,WEEKDAY(A119)=2,WEEKDAY(A120)=3),A120,
IF(AND(WEEKDAY(A118)=2,WEEKDAY(A119)=3,WEEKDAY(A120)=3),A120,
IF(AND(WEEKDAY(A118)=3,WEEKDAY(A119)=3,WEEKDAY(A120)=3),A120+1,


IF(AND(WEEKDAY(A118)=3,WEEKDAY(A119)=3,WEEKDAY(A120)=4),A120,
IF(AND(WEEKDAY(A118)=3,WEEKDAY(A119)=4,WEEKDAY(A120)=4),A120,
IF(AND(WEEKDAY(A118)=4,WEEKDAY(A119)=4,WEEKDAY(A120)=4),A120+1,


IF(AND(WEEKDAY(A118)=4,WEEKDAY(A119)=4,WEEKDAY(A120)=5),A120,
IF(AND(WEEKDAY(A118)=4,WEEKDAY(A119)=5,WEEKDAY(A120)=5),A120,
IF(AND(WEEKDAY(A118)=5,WEEKDAY(A119)=5,WEEKDAY(A120)=5),A120+1,


IF(AND(WEEKDAY(A119)=7,WEEKDAY(A120)=1),A120,
IF(AND(WEEKDAY(A119)=1,WEEKDAY(A120)=1),A120+1,
IF(AND(WEEKDAY(A119)=1,WEEKDAY(A120)=2),A120,
IF(AND(WEEKDAY(A119)=2,WEEKDAY(A120)=2),A120+1,
IF(AND(WEEKDAY(A119)=2,WEEKDAY(A120)=3),A120,
IF(AND(WEEKDAY(A119)=3,WEEKDAY(A120)=3),A120+1,
IF(AND(WEEKDAY(A119)=3,WEEKDAY(A120)=4),A120,
IF(AND(WEEKDAY(A119)=4,WEEKDAY(A120)=4),A120+1,
IF(AND(WEEKDAY(A119)=4,WEEKDAY(A120)=5),A120,
IF(AND(WEEKDAY(A119)=5,WEEKDAY(A120)=5),A120+1,
IF(AND(WEEKDAY(A119)=5,WEEKDAY(A120)=6),A120,
IF(AND(WEEKDAY(A118)=5,WEEKDAY(A119)=6,WEEKDAY(A120)=6),A120,IF(AND(WEEKDAY(A118)=6,WEEKDAY(A119)=6,WEEKDAY(A120)=6),A120+1,
IF(AND(WEEKDAY(A118)=6,WEEKDAY(A119)=7,WEEKDAY(A120)=7),A120,IF(AND(WEEKDAY(A118)=6,WEEKDAY(A119)=6,WEEKDAY(A120)=7),A120,
IF(AND(WEEKDAY(A118)=7,WEEKDAY(A119)=7,WEEKDAY(A120)=7),A120+1,"?"))))))))))))))))))))))))))))))))


so, what im trying to do here is;
in column A i have a date, and for each day i have 2 or 3 rows depending on a few parameters.
i need to check if:
(a) this date is a work day, i.e. sunday through thuesday.
(b) if its a weekend, i.e. friday or saturday.
(c) if its a holiday, which is in column R.

if its a normal workday, sun - thu, than there should be only 2 rows with the same date, but if its either a weekend or a holiday than there should be 3 rows.

the formula works great if is a holiday, but if its not it just returns "FALSE" instead of the false argument of the formula..
ive cracked my head open on this and still havent figured out whats went wrong here.

any help would be greatly appreciated :)
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Still trying to wade through that monster, but for a start, if you need to test for Sun-Thur, this will do it for you...
=WEEKDAY(Q11,1)<6
This will return TRUE for Sun-Thur
obviously change the cell-ref
What this is doing, is returning a number representing the day of the week, with Sunday = 1 and Saturday = 7
 
Upvote 0
In this opening statement...
=IF(A120=IFNA(VLOOKUP(A120,$R$7:$R$57,1,FALSE),A120+1),...
you have something wrong with the syntax.

=IF(A120=IFNA(VLOOKUP(A120,$R$7:$R$57,1,FALSE),A120+1),
the vlookup function

=IF(A120=IFNA(VLOOKUP(A120,$R$7:$R$57,1,FALSE),A120+1),
the IFNA statement
So you are stating IF(A120=an error, then......................
(and btw, unless that has been included in a later excel version - leter then 2010 which I am currently on - then there is no IFNA function)
 
Upvote 0
I'm sure we can come up with a simpler formula. Any time there's as much pattern as you have in yours, there is usually a way to take advantage of that. WORKDAY.INTL for example allows you to include the holidays as part of the function.

Could you post a small sample of your sheet, and what results you expect?

I suspect that the reason you're getting FALSE is because you have around 40 nested IFs, and you don't specify a false argument for all of them, meaning you get the default of FALSE.
 
Upvote 0
Assuming that's a direct copy/paste from your formula...
YOu have a couple of typos...
Code: 
[COLOR=#333333]=IF(A120=IFNA(VLOOKUP(A120,$R$7:$R$57,1,FALSE),A120+1),[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A118)=7,WEEKDAY(A119)=7,WEEKDAY(A120)=1),A120,[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A118)=7,WEEKDAY(A119)=1,WEEKDAY(A120)=1),A120,[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A118)=1,WEEKDAY(A119)=1,WEEKDAY(A120)=1),A120+1,[/COLOR]


[COLOR=#333333]IF(AND(WEEKDAY(A118)=1,WEEKDAY(A119)=1,WEEKDAY(A120)=2),A120,[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A118)=1,WEEKDAY(A119)=2,WEEKDAY(A120)=2),A120,[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A118)=2,WEEKDAY(A119)=2,WEEKDAY(A120)=2),A120+1,[/COLOR]


[COLOR=#333333]IF(AND(WEEKDAY(A118)=2,WEEKDAY(A119)=2,WEEKDAY(A120)=3),A120,[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A118)=2,WEEKDAY(A119)=3,WEEKDAY(A120)=3),A120,[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A118)=3,WEEKDAY(A119)=3,WEEKDAY(A120)=3),A120+1,[/COLOR]


[COLOR=#333333]IF(AND(WEEKDAY(A118)=3,WEEKDAY(A119)=3,WEEKDAY(A120)=4),A120,[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A118)=3,WEEKDAY(A119)=4,WEEKDAY(A120)=4),A120,[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A118)=4,WEEKDAY(A119)=4,WEEKDAY(A120)=4),A120+1,[/COLOR]


[COLOR=#333333]IF(AND(WEEKDAY(A118)=4,WEEKDAY(A119)=4,WEEKDAY(A120)=5),A120,[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A118)=4,WEEKDAY(A119)=5,WEEKDAY(A120)=5),A120,[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A118)=5,WEEKDAY(A119)=5,WEEKDAY(A120)=5),A120+1,[/COLOR]


[COLOR=#333333]IF(AND(WEEKDAY(A119)=7,WEEKDAY(A120)=1),A120,[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A119)=1,WEEKDAY(A120)=1),A120+1,[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A119)=1,WEEKDAY(A120)=2),A120,[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A119)=2,WEEKDAY(A120)=2),A120+1,[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A119)=2,WEEKDAY(A120)=3),A120,[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A119)=3,WEEKDAY(A120)=3),A120+1,[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A119)=3,WEEKDAY(A120)=4),A120,[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A119)=4,WEEKDAY(A120)=4),A120+1,[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A119)=4,WEEKDAY(A120)=5),A120,[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A119)=5,WEEKDAY(A120)=5),A120+1,[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A119)=5,WEEKDAY(A120)=6),A120,[/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A118)=5,WEEKDAY(A119)=6,WEEKDAY(A120)=6),A120,IF(AND(WEEKDAY(A118)=6,WEEKDAY(A119)=6,WEEKDAY(A120)=6),[/COLOR][COLOR=#ff0000]A12 0+1,[/COLOR][COLOR=#333333][/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A118)=6,WEEKDAY(A119)=7,WEEKDAY(A120)=7),A120,IF(AND(WEEKDAY(A118)=6,WEEKDAY(A119)=6,WEEKDAY(A120)=7),[/COLOR][COLOR=#ff0000]A12 0,[/COLOR][COLOR=#333333][/COLOR]
[COLOR=#333333]IF(AND(WEEKDAY(A118)=7,WEEKDAY(A119)=7,WEEKDAY(A120)=7),A120+1,"?"))))))))))))))))))))))))))))))))[/COLOR]
 
Upvote 0
FDibbins said:
(and btw, unless that has been included in a later excel version - leter then 2010 which I am currently on - then there is no IFNA function)
Click to expand...

ifna()
First argument is a formula
If the formula DOESN'T evaluate to #NA it simply returns the result of the formula
if the formula DOES result in #NA then it returns argument 2.

In this case, if the VLookup returns NA, it returns A120+1 which then results in "If(A120=A120+1,...)" which will always be false.
 
Upvote 0
ok, so 1 at a time, @FDibbins, havent had a chance to try your of weekday(q11,1)<6, will post when i do.
@Eric W, i know workday.intl, correct me if im wrong, but in my case in wich i need to identify all the days of the week including weekends, and not ignore them, may not be on much help..
@sstiebinger, the 2 typos you marked in red are just that, copy/paste typos. the original formula is ok.
as for the ifna formula, its this so as if the date is not a holiday it would indeed return false, which then, in theory, should return the false argument, later in the formula, as follows:

=IF(A120=IFNA(VLOOKUP(A120,$R$7:$R$57,1,FALSE),A120+1),
'if vlookup is true than ---->
IF(AND(WEEKDAY(A118)=7,WEEKDAY(A119)=7,WEEKDAY(A120)=1),A120,
IF(AND(WEEKDAY(A118)=7,WEEKDAY(A119)=1,WEEKDAY(A120)=1),A120,
IF(AND(WEEKDAY(A118)=1,WEEKDAY(A119)=1,WEEKDAY(A120)=1),A120+1,


IF(AND(WEEKDAY(A118)=1,WEEKDAY(A119)=1,WEEKDAY(A120)=2),A120,
IF(AND(WEEKDAY(A118)=1,WEEKDAY(A119)=2,WEEKDAY(A120)=2),A120,
IF(AND(WEEKDAY(A118)=2,WEEKDAY(A119)=2,WEEKDAY(A120)=2),A120+1,


IF(AND(WEEKDAY(A118)=2,WEEKDAY(A119)=2,WEEKDAY(A120)=3),A120,
IF(AND(WEEKDAY(A118)=2,WEEKDAY(A119)=3,WEEKDAY(A120)=3),A120,
IF(AND(WEEKDAY(A118)=3,WEEKDAY(A119)=3,WEEKDAY(A120)=3),A120+1,


IF(AND(WEEKDAY(A118)=3,WEEKDAY(A119)=3,WEEKDAY(A120)=4),A120,
IF(AND(WEEKDAY(A118)=3,WEEKDAY(A119)=4,WEEKDAY(A120)=4),A120,
IF(AND(WEEKDAY(A118)=4,WEEKDAY(A119)=4,WEEKDAY(A120)=4),A120+1,


IF(AND(WEEKDAY(A118)=4,WEEKDAY(A119)=4,WEEKDAY(A120)=5),A120,
IF(AND(WEEKDAY(A118)=4,WEEKDAY(A119)=5,WEEKDAY(A120)=5),A120,
IF(AND(WEEKDAY(A118)=5,WEEKDAY(A119)=5,WEEKDAY(A120)=5),A120+1,
'vlookup true statement ends here.

'if vlookup is false, its results in NA, wich leads to a120=a120+1, and SHOULD lead to the following part of the formula --->
IF(AND(WEEKDAY(A119)=7,WEEKDAY(A120)=1),A120,
IF(AND(WEEKDAY(A119)=1,WEEKDAY(A120)=1),A120+1,
IF(AND(WEEKDAY(A119)=1,WEEKDAY(A120)=2),A120,
IF(AND(WEEKDAY(A119)=2,WEEKDAY(A120)=2),A120+1,
IF(AND(WEEKDAY(A119)=2,WEEKDAY(A120)=3),A120,
IF(AND(WEEKDAY(A119)=3,WEEKDAY(A120)=3),A120+1,
IF(AND(WEEKDAY(A119)=3,WEEKDAY(A120)=4),A120,
IF(AND(WEEKDAY(A119)=4,WEEKDAY(A120)=4),A120+1,
IF(AND(WEEKDAY(A119)=4,WEEKDAY(A120)=5),A120,
IF(AND(WEEKDAY(A119)=5,WEEKDAY(A120)=5),A120+1,
IF(AND(WEEKDAY(A119)=5,WEEKDAY(A120)=6),A120,
IF(AND(WEEKDAY(A118)=5,WEEKDAY(A119)=6,WEEKDAY(A120)=6),A120,IF(AND(WEEKDAY(A118)=6,WEEKDAY(A119)=6,WEEKDAY(A120)=6), A120+1,
IF(AND(WEEKDAY(A118)=6,WEEKDAY(A119)=7,WEEKDAY(A120)=7),A120,IF(AND(WEEKDAY(A118)=6,WEEKDAY(A119)=6,WEEKDAY(A120)=7),A120,
IF(AND(WEEKDAY(A118)=7,WEEKDAY(A119)=7,WEEKDAY(A120)=7),A120+1,"?"))))))))))))))))))))))))))))))))

hopes this simplifies things
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Formula Error: Error applying formula with VBA even though it works manually
Replies
3
Views
83
6StringJazzer
6StringJazzer
R
  • Solved
Ignore blanks for a SUM
Replies
2
Views
108
Rex987456
R
A
  • Question Question
Get enddatetime if X no. of working hours is added to startdatetime
Replies
1
Views
430
myall_blues
myall_blues
W
  • Question Question
Copy formula across 28 day dynamic shift calendar
Replies
4
Views
313
Wolfgang17
W
J
  • Question Question
filling cells with using values in helper cell
Replies
0
Views
172
jbrown021286
J

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top