How to find the number of value within a range?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
=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.
 
Back
Top