Formula Help: multiple cells with a < or > then Return statement

JackDanIce

Well-known Member

Joined

Feb 3, 2010

Messages

9,922

Office Version

1. 365

Platform

1. Windows

• May 29, 2014

• #2

Welcome to the board. Try:

Code:

=IF(OR(I1 < L1, J1 < L1, K1 < L1), "Yes", "No" )<l1,j1<l1,k1<l1),"yes","no")

Note, you haven't stated the condition for when either cell = L1. My suggestion will return "No" when it does.</l1,j1<l1,k1<l1),"yes","no")

 

Upvote 0

D

daverm

Board Regular

Joined

Apr 17, 2014

Messages

66

• May 29, 2014

• #3

<l1","yes"<"no")<l1"),"yes","no")[ code]
Why can't I post

Code:

if(countif(I1:K1,"<l1")>=1","Yes","No")

</l1")></l1","yes"<"no")<l1"),"yes","no")[>

 

Last edited: May 29, 2014

Upvote 0

L

ldavis1227

New Member

Joined

May 29, 2014

Messages

19

• May 29, 2014

• #4

Oh my gosh! Thank you so much! I've been working on this for hours! It worked!!!!

 

Upvote 0

L

ldavis1227

New Member

Joined

May 29, 2014

Messages

19

• May 29, 2014

• #5

I spoke too soon. If I change the value of K1 to .13 it returns and answer of "No", which it should say "Yes" because all values are less than L1. Any ideas? BTW, I'm using Excel 10

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• May 29, 2014

• #6

You posted that L1 = 0

0.13 is NOT Less than 0 (nor is 0.02 or 0.08)

 

Upvote 0

JackDanIce

Well-known Member

Joined

Feb 3, 2010

Messages

9,922

Office Version

1. 365

Platform

1. Windows

• May 29, 2014

• #7

ldavis: With your example, if L1 is 0 and K1 is 0.13, isn't 0.13 > 0, so the answer is No? What are the values for I1 and J1?

daverm: If you use < and > without spaces, it treats it as HTML tags I think. Try suggesting your COUNTIF answer again but use a space before and after '<'

 

Upvote 0

L

ldavis1227

New Member

Joined

May 29, 2014

Messages

19

• May 29, 2014

• #8

I feel really stupid right now. I'm using a 24 hour TAT using date and time, so it is calculating minutes and L1 is just using date only. I'll have to figure something else out.

Jonmo1 said:

You posted that L1 = 0

0.13 is NOT Less than 0 (nor is 0.02 or 0.08)

Click to expand...

 

Upvote 0

L

ldavis1227

New Member

Joined

May 29, 2014

Messages

19

• May 29, 2014

• #9

some of my TAT's are in hours and some are in days, even if I use the formula provided by JackDance with these new values, it returns an incorrect answer.

I1 = 4.00
J1 = 9.00
K1 = 31.00
L1 = 30

I get "yes" when it should be "No" because of K1 being more than 30.

 

Upvote 0

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• May 29, 2014

• #10

ldavis1227 said:

I want to know if any of the values in (I1, J1, K1) is (< then L1) return "Yes", if (> then L1) return "No".

Click to expand...

Oh dear. I think that what has happened is that when you said this way back in post #1, you actually meant "all", not "any".

Big difference!!

 

Upvote 0