Having trouble with IF AND OR string

[lc130]

Folks,

I'm doing something wrong with this forumla:

=IF(E$21<$H$16,IF(OR(HLOOKUP(E$19,Intg.xlsm!ENDYRAGE,2)>=65,HLOOKUP(E$19,Intg.xlsm!ENDYRAGE,3)>=65),0,1)))

If E$21 is less than H$16 then I want the formula to evaluate whether either person is at or over 65 if year end. If E$21<H$16 and either person is at or over 65 I want the formula to return 0 otherwise I have another formula to insert. I'm getting FALSE at the moment. I suspect it's associated with the OR statement which I'm not used to using.

Any help is appreciated!

Kelly

[ben_hensel]

No, the FALSE is returned from the first IF (you left the term implicit so it returns boolean by default), it's not even assessing the OR.

You've also got one-too-many right parens on the end.

[Jonmo1]

Hope this helps

=IF(E$21<$H$16,IF(OR(HLOOKUP(E$19,Intg.xlsm!ENDYRAGE,2)>=65,HLOOKUP(E$19,Intg.xlsm!ENDYRAGE,3)>=65),0,1),whattodoifE21isnotlessthanH16goeshere)

[Speshul]

What ben is saying is:

=IF(E$21<$H$16,IF(OR(HLOOKUP(E$19,Intg.xlsm!ENDYRAGE,2)>=65,HLOOKUP(E$19,Intg.xlsm!ENDYRAGE,3)>=65),0,1),"E$21 is NOT < $H$16")

TEST
TEST WAS TRUE
TEST WAS FALSE


You are missing the FALSE bit

[lc130]

How would I write it such that its evaluating the first term (E21<H16) and then looks to see if one of the next two (either one being over 65) is true. If first term and either second or third is true it returns 1 or else 0. I can enter formulas into the 1 or 0?

Many thanks. My head hurts from banging it on the key board.

Kelly

[Speshul]

Please Login or Register  to view this content.

It already does that, but it is only going to evaluate the bolded portion of the formula if E21 is indeed less than H16.

You can replace the 1 and 0 with further formulas if necessary.

Just keep in mind, if E21 is NOT less than H16, NONE of the bold tests will happen, it will immediately execute the FALSE statements. (your original formula did not have a statement for FALSE, so it defaulted to the boolean to return FALSE as a value. I added it in red in the formula here and above, you can replace it to whatever you need the formula to do if the original test (E21<H16) comes back FALSE)

[ben_hensel]

evaluating the first term (E21<H16) and then looks to see if one of the next two (either one being over 65) is true. If first term and either second or third is true it returns 1 or else 0. I can enter formulas into the 1 or 0?

You mean something like this?

Please Login or Register  to view this content.

1 AND (2 OR 3)

that kind of logic test?

[lc130]

Yes. If first term is true (E21<H16) then check to see if either of the second two terms are true. If either of those second terms are true (along with the first term) return one value, else, return another value.

I've never had a very good understanding of these conditional statements.

Thank you

Kelly