Formula to compare multiple cells

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

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")
--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> 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,
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
"Don Guillett" <[email protected]> wrote in message
news:%[email protected]...
> 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")
> --
> Don Guillett
> SalesAid Software
> [email protected]
> <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>

On Sat, 21 May 2005 12:24:23 -0500, <[email protected]> wrote:

>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

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

--

HTH

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


<[email protected]> wrote in message
news:[email protected]...
> 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
> "Don Guillett" <[email protected]> wrote in message
> news:%[email protected]...
> > 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")
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >
> >
>
>

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

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

--
Regards,
Tom Ogilvy



<[email protected]> wrote in message
news:[email protected]...
> 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
> "Don Guillett" <[email protected]> wrote in message
> news:%[email protected]...
> > 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")
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >
> >
>
>

Thanks guys for your assistance this worked great.


Judd

"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Sat, 21 May 2005 12:24:23 -0500, <[email protected]> wrote:
>
>>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

On Sat, 21 May 2005 18:11:27 -0500, <[email protected]> wrote:

>Thanks guys for your assistance this worked great.
>
>
>Judd

You're welcome. Thank you for the feedback.


--ron