How to find the number of value within a range?
 

There is a list of numbers under colume A
1813, 1748, 1652, 1415, 1361, 1169, 1129
945, 890, 765, 633, 604, 598, 525, 490

A range 300 is defined each number's upper and lower limit, such as the
number 945, upper limit is 945 + 300 = 1245, lower limit is 945 - 300 = 645.
Within the defined limits between 1245 and 645 for the number 945, which
cross above the number 1169 & 1129 and cross below the number 890 & 765, this
defined range for 945 covers 4 values within those upper and lower limits,
then it returns the value 4 in colume B.
I would like to perform this calculation for each number.
Does anyone have any suggestion on how to do it in Excel?
Thank you for any suggestion
Eric

How to find the number of value within a range?
 

One way ..

Assuming source numbers listed in A1:A15

Place in B1:
=MATCH(A1-300,$A$1:$A$15,-1)-IF(ISNA(MATCH(A1+300,$A$1:$A$15,-1)),0,MATCH(A1+300,$A$1:$A$15,-1))-1
Copy down to B15
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Eric" wrote:
There is a list of numbers under colume A
1813, 1748, 1652, 1415, 1361, 1169, 1129
945, 890, 765, 633, 604, 598, 525, 490

A range 300 is defined each number's upper and lower limit, such as the
number 945, upper limit is 945 + 300 = 1245, lower limit is 945 - 300 = 645.
Within the defined limits between 1245 and 645 for the number 945, which
cross above the number 1169 & 1129 and cross below the number 890 & 765, this
defined range for 945 covers 4 values within those upper and lower limits,
then it returns the value 4 in colume B.
I would like to perform this calculation for each number.
Does anyone have any suggestion on how to do it in Excel?
Thank you for any suggestion
Eric

How to find the number of value within a range?
 

See reply in .Functions

Biff

"Eric" wrote in message
...
There is a list of numbers under colume A
1813, 1748, 1652, 1415, 1361, 1169, 1129
945, 890, 765, 633, 604, 598, 525, 490

A range 300 is defined each number's upper and lower limit, such as the
number 945, upper limit is 945 + 300 = 1245, lower limit is 945 - 300 =
645.
Within the defined limits between 1245 and 645 for the number 945, which
cross above the number 1169 & 1129 and cross below the number 890 & 765,
this
defined range for 945 covers 4 values within those upper and lower limits,
then it returns the value 4 in colume B.
I would like to perform this calculation for each number.
Does anyone have any suggestion on how to do it in Excel?
Thank you for any suggestion
Eric

How to find the number of value within a range?
 

=COUNTIF(A$1:A$15,"="&A8-300)-COUNTIF(A$1:A$15,""&A8+300)-1
You may want to adjust the and =, depending on how you want to treat
values on the +/- 300 limits.
--
David Biddulph

"Eric" wrote in message
...
There is a list of numbers under colume A
1813, 1748, 1652, 1415, 1361, 1169, 1129
945, 890, 765, 633, 604, 598, 525, 490

A range 300 is defined each number's upper and lower limit, such as the
number 945, upper limit is 945 + 300 = 1245, lower limit is 945 - 300 =
645.
Within the defined limits between 1245 and 645 for the number 945, which
cross above the number 1169 & 1129 and cross below the number 890 & 765,
this
defined range for 945 covers 4 values within those upper and lower limits,
then it returns the value 4 in colume B.
I would like to perform this calculation for each number.
Does anyone have any suggestion on how to do it in Excel?
Thank you for any suggestion
Eric

How to find the number of value within a range?
 

Thank everyone for suggestions
Eric