Excel: Formula not calculating 1

[LizSara]

I'm a bit of a newbie at excel formulas and i have a question.

I have a formula that is meant to be working out if something falls on a weekday. if it does then it's meant to be putting an amount sourced from a different sheet (same workbook) into the field and if not then it's meant to be leaving it at 0.

the formula i have is =IF(ISNA((VLOOKUP((WEEKDAY(B9)),day,1,FALSE))),Control!$G$29,0)

This isn't working at all it's leaving everything at 0.

Any help gratefully appreciated.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"Whoever battles with monsters had better see that it does not turn him into a monster. And if you gaze long into an abyss, the abyss will gaze back into you. ~ Nietzsche"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[RivetHed]

Your parentheses look wrong, this is correct syntax assuming that day is a named range.

=IF(ISNA(VLOOKUP(WEEKDAY(B9),day,1,FALSE)),Control!$G$29,0)

 

[xlbo]

You could also simplify - if you just need to test whether B9 is a weekday then

=if(WEEKDAY(B9,2)<6,Control!$G$29,0)

the ,2 in the weekday formula makes it start from Monday = 1 so you know that 6=sat & 7=sun so you just need to test for <6 to ensure it is a weekday

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[LizSara]

Day is a named range, it's telling me it's an inconsistent formula though

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"Whoever battles with monsters had better see that it does not turn him into a monster. And if you gaze long into an abyss, the abyss will gaze back into you. ~ Nietzsche"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[xlbo]

Hi Liz - based on your response (looks like a cross posting), you should be able to use my simplified formula

Excel may tell you its an inconsistent formula as it is now different to the others that surround it...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[LizSara]

Perfect, thank you

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"Whoever battles with monsters had better see that it does not turn him into a monster. And if you gaze long into an abyss, the abyss will gaze back into you. ~ Nietzsche"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~