differences in groups of numbers
 

Hello there-

I have a column of 10 numbers. The last row of the column is a control
value. I would like to know the easiest way to see if any of the previous 9
numbers in a column divided by the control number is .6 or less. I tried OR
function and it works, but I will be dealing with bigger columns of up to 100
numbers to compare to a control number and the OR function in this case would
be cumbersome.

PM

Assume your numbers are in A1:A100 and your control number is in A101, this
function counts the number of entries meeting this condition:

=SUMPRODUCT(--(A1:A100/A101<0.6))

--
Regards,

David Billigmeier


"pm" wrote:

Hello there-

I have a column of 10 numbers. The last row of the column is a control
value. I would like to know the easiest way to see if any of the previous 9
numbers in a column divided by the control number is .6 or less. I tried OR
function and it works, but I will be dealing with bigger columns of up to 100
numbers to compare to a control number and the OR function in this case would
be cumbersome.

PM

=SUM(A1:A9)/A10<0.6

--

HTH

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


"David Billigmeier" wrote in
message ...
Assume your numbers are in A1:A100 and your control number is in A101,
this
function counts the number of entries meeting this condition:

=SUMPRODUCT(--(A1:A100/A101<0.6))

--
Regards,

David Billigmeier


"pm" wrote:

Hello there-

I have a column of 10 numbers. The last row of the column is a control
value. I would like to know the easiest way to see if any of the
previous 9
numbers in a column divided by the control number is .6 or less. I
tried OR
function and it works, but I will be dealing with bigger columns of up
to 100
numbers to compare to a control number and the OR function in this case
would
be cumbersome.

PM

Bob - Your formula simply sums the values and divides by the control number.
I believe (unless I read wrong) he wanted to check if each individual number
divided by the control number is less than .6, in which case my formula would
have to be used:

=SUMPRODUCT(--(A1:A100/A101<0.6))

--
Regards,

David Billigmeier



"Bob Phillips" wrote:

=SUM(A1:A9)/A10<0.6

--

HTH

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


"David Billigmeier" wrote in
message ...
Assume your numbers are in A1:A100 and your control number is in A101,
this
function counts the number of entries meeting this condition:

=SUMPRODUCT(--(A1:A100/A101<0.6))

--
Regards,

David Billigmeier


"pm" wrote:

Hello there-

I have a column of 10 numbers. The last row of the column is a control
value. I would like to know the easiest way to see if any of the
previous 9
numbers in a column divided by the control number is .6 or less. I
tried OR
function and it works, but I will be dealing with bigger columns of up
to 100
numbers to compare to a control number and the OR function in this case
would
be cumbersome.

PM

Thanks Dave. Just out of curiosity, what is the purpose of the two dashes
(--) in the formula?

"pm" wrote:

Hello there-

I have a column of 10 numbers. The last row of the column is a control
value. I would like to know the easiest way to see if any of the previous 9
numbers in a column divided by the control number is .6 or less. I tried OR
function and it works, but I will be dealing with bigger columns of up to 100
numbers to compare to a control number and the OR function in this case would
be cumbersome.

PM

Re-reading, I think you are right. I missed the emphasis of any. Pit, mine
was much simpler :-)

Bob


"David Billigmeier" wrote in
message ...
Bob - Your formula simply sums the values and divides by the control
number.
I believe (unless I read wrong) he wanted to check if each individual
number
divided by the control number is less than .6, in which case my formula
would
have to be used:

=SUMPRODUCT(--(A1:A100/A101<0.6))

--
Regards,

David Billigmeier



"Bob Phillips" wrote:

=SUM(A1:A9)/A10<0.6

--

HTH

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


"David Billigmeier" wrote
in
message ...
Assume your numbers are in A1:A100 and your control number is in A101,
this
function counts the number of entries meeting this condition:

=SUMPRODUCT(--(A1:A100/A101<0.6))

--
Regards,

David Billigmeier


"pm" wrote:

Hello there-

I have a column of 10 numbers. The last row of the column is a
control
value. I would like to know the easiest way to see if any of the
previous 9
numbers in a column divided by the control number is .6 or less. I
tried OR
function and it works, but I will be dealing with bigger columns of
up
to 100
numbers to compare to a control number and the OR function in this
case
would
be cumbersome.

PM

All explained in http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--

HTH

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


"pm" wrote in message
...
Thanks Dave. Just out of curiosity, what is the purpose of the two dashes
(--) in the formula?

"pm" wrote:

Hello there-

I have a column of 10 numbers. The last row of the column is a control
value. I would like to know the easiest way to see if any of the
previous 9
numbers in a column divided by the control number is .6 or less. I
tried OR
function and it works, but I will be dealing with bigger columns of up
to 100
numbers to compare to a control number and the OR function in this case
would
be cumbersome.

PM