IF Function

[themanwithnoshoes]

Hi All,

I just posted a thread in relation to gaining an IF formula and it is almost working however, having just one issue.

=IF(OR(K2="YES",K2=" "),"N","Y")=IF(J2-I2>14,"Y","N")

The above function is working however, instead of returning Y or N it returns False and True.

Any ideas of how I can sort this?

Thank You!
Justin

[bebo021999]

Your formula:
=IF(OR(K2="YES",K2=" "),"N","Y")=IF(J2-I2>14,"Y","N")
Could be:
="Y"="Y" ==> TRUE
="N"="N" ==> TRUE
="Y"="N" ==> FALSE
="N"="Y" ==> FALSE
Hoe does it could be "Y" or "N"?

[FinlayH]

The first part of your formula is saying if K2 is blank or says yes then say no. Other wise say yes.
The second part says if J2-I2 is greater than 14 then say yes, otherwise say no.

I think you are missing another IF here. Do you want the formula to return true if the two halves equal each other and false if they don't?

[Jonmo1]

It's hard to understand which conditions you want a Y or N...

But taking a guess, try

=IF(AND(J2-I2 < =14,OR(K2="YES",K2=" ")),"N","Y")

If this isn't it, explain which conditions return Y or N...
You have 2 basic conditions, with 4 possible combinations..

1. K2 = Yes or " ", AND J2-I2 > 14
2. K2 = Yes or " ", AND J2-I2 < = 14
3. K2 DOES NOT = Yes or " ", AND J2-I2 > 14
4. K2 DOES NOT = Yes or " ", AND J2-I2 < = 14

Please show either Y or N for each of the 4 possible combinations of your conditions.

[themanwithnoshoes]

Hi there, thank you for your time. I see what you're saying. Here are the conditions below.

1. If K2 is "YES" then the cell must show "N", no matter what.
2. If K2 is blank, I need the cell to query my other IF function.
3. Other IF is - If the time between J2 - I2 is more than 14 days, the cell must show "N"
4. if the time between J2 - I2 is less than 14 days, and K2 is blank, the cell must show "Y"

Does the above make sense at all :S ?

[Jonmo1]

Try

=IF(K2="Yes","N",IF(J2-I2>14,"N","Y"))


Technically, this doesn't test for K2 to be blank, instead it assumes it's blank if it is NOT "Yes"

[FinlayH]

What should it show if K is no?

[themanwithnoshoes]

Where in the world do you get this wisdom? Works perfectly.

Thank you Jonmo1! All rep given. Perfect.

Have a good day

Justin

[bebo021999]

So:

=IF(K2="YES","N",IF(K2=" ",IF(J2-I2>14,"N","Y"),"What you need if K2 is not blank"))

[themanwithnoshoes]

Is there a way of making it so if one of the J or I is blank, the cell is blank?

[Jonmo1]

Try

=IF(K2="Yes","N",IF(COUNT(I2:J2)=2,IF(J2-I2>14,"N","Y"),""))

[FinlayH]

=IF(K2="Yes","N",IF(OR(J2="",I2=""),"",IF(J2-I2>14,"N","Y")))

[themanwithnoshoes]

Geniuses. Ok one last thing. Sorry to take advantage, but here goes.

If M2 is above £0 i need the cell to display N.

Also, if it is possible to have the above, if I put in 10%, it won't class that as more than £0 will it?

[Jonmo1]

This is called "The bait and switch", and is generally frowned upon in these forums.
It's best to lay out ALL of your expectations up front.

But I'll go for one more....


Is this new criteria "Above all others" ?
If M2 is above 0, then "N"..Regardless of ALL the other conditions?

=IF(M2>0,"N",IF(K2="Yes","N",IF(COUNT(I2:J2)=2,IF(J2-I2>14,"N","Y"),"")))

And yes, 10% will be considered more than 0.
0 is Nothing, 10% is indeed more than nothing.

[themanwithnoshoes]

Ok actually think i managed the above: =IF(K2="Yes","N",IF(M2>0,"N",IF(COUNT(I2:J2)=2,IF(J2-I2>14,"N","Y"),"")))

See any problems with that one? Is there anyway to only read currency and not class 10% as more than £1.00 ?

Thanks again Rep+

[themanwithnoshoes]

And I'm new to the forums. My aim wasn't to draw you in with Bait. I simply didn't realise the possibilities of Excel as to which you have assisted me with. I am sorry if you do not wish to answer but please do not feel the need to reply if you feel angered by my requests.

[Jonmo1]

I'm not angry, as you see I went on to actually post a response to the new criteria..
That was intended more as a friendly FYI about best practices when posting in the forum.


Anyway..

Not sure I follow what you're after with the 10%..
10% is NOT more than £1.00, but it IS more than 0.

You're testing the formula for M2>0, but then you're asking about more than £1.00 ??
Can you clarify?

[themanwithnoshoes]

Thanks for your help. Sorted.

[Jonmo1]

You're welcome.