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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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

E
  • Question
IF statement
Replies
1
Views
584
KillerOfGiants
K
L
  • Question
How to Repeat shifts and have it auto fill after giving first line.
Replies
2
Views
123
LPHBELLO
L
R
  • Solved
Combination Sumif and/or Countif on a condition
Replies
3
Views
173
Fluff
Fluff
SaraWitch
  • Solved
VLOOKUP formula with multiple IF statements
Replies
2
Views
528
SaraWitch
SaraWitch
K
  • Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
298
Eric W
Eric W

Forum statistics

Threads
1,216,036
Messages
6,128,432
Members
449,452
Latest member
Chris87

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