Formula to compare multiple cells

[Guest]

I am looking for a formula that will compare a value in one cell against the
values in multiple cells and I am trying to find out if it is <=. I am
currently using the formula below but it does not always provide the correct
results. I only want it to tell me if it is a winner if it is less than
all(or the least) cells. It will give me a winner if it <= any of the
values.


=IF(N9<=J9,"Winner",IF(N9<=K9,"winner",IF(N9<=L9,"Winner",IF(N9<=M9,"Winner","Stop"))))



Thanks,


Judd

 

[Don Guillett]

try this
=IF(sumproduct((j6:m6>0)*(j6:m6<n6)*1)>0,"winner","Stop")
or even
=IF(sumproduct((j6:m6>0)*(j6:m6<n6)*1),"winner","Stop")

 

[Guest]

Don,
Thanks for the input but both formulas still give me a winner when my
number that I am comparing is not <= "all" values. Here is an example of my
values

1320 1520 1820 1830 1550



I am using 1550 to compare

I should get "winner" only if my value is <= 1320


Judd

 

[Ron Rosenfeld]

I am looking for a formula that will compare a value in one cell against the
values in multiple cells and I am trying to find out if it is <=. I am
currently using the formula below but it does not always provide the correct
results. I only want it to tell me if it is a winner if it is less than
all(or the least) cells. It will give me a winner if it <= any of the
values.


=IF(N9<=J9,"Winner",IF(N9<=K9,"winner",IF(N9<=L9,"Winner",IF(N9<=M9,"Winner","Stop"))))



Thanks,


Judd

How about:

=IF(N9<=MIN(J9:M9),"Winner","Stop")


--ron

 

[Bob Phillips]

=IF(SUMPRODUCT((J6:M6>0)*(J6:M6>N6)*1)=4,"winner","Stop")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Sumproduct would probably be slower than just checking against the smallest
number in the list.

=if(n6<MIN(J6:M6),"Winner","Stop")

 

[Guest]

Thanks guys for your assistance this worked great.


Judd

 

[Ron Rosenfeld]

Thanks guys for your assistance this worked great.


Judd

You're welcome. Thank you for the feedback.


--ron