Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
=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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count comma separated numbers, numbers in a range with dash, not t | Excel Discussion (Misc queries) | |||
Checking Winning Numbers in the Lottery. | Excel Discussion (Misc queries) | |||
Count and Sum Total occurrances of two specific numbers | Excel Worksheet Functions | |||
Counting differences in numbers across columns | Excel Worksheet Functions | |||
Converting Numbers to Text properly | Excel Discussion (Misc queries) |