How to find the number of value within a range?

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

Guest

Referring to the post in General Question

Does anyone have any suggestion on following case?

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, as responsed to your posting* in .misc ..

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

*Btw, there's no need to multi-post. Most of the regular Excel responders
will read the popular excel newsgroups, like this group & .misc, so just post
in one group will do. A single posting in one popular Excel newsgroup (eg:
..worksheet.functions, .misc, .newusers, etc) won't escape the attention &
readership of these regular responders.
 
Try this:

Assume your numbrs are in the range A1:A15

Enter this formula in B1 and copy down:

=INDEX(FREQUENCY(A$1:A$15,A1+{300,-301}),1)-1

Biff
 
=INDEX(FREQUENCY(A$1:A$15,A1+{300,-301}),1)-1
Could you elaborate a little on how it works ?
Click to expand...

Sure....

It will be easier to understand if I switch things around and put them in
they're logical order:

=INDEX(FREQUENCY(A$1:A$15,A1+{-301,300}),2)-1

Let's use the first number in the list for this example:

1813

The Op wants a count of the value +/-300 excluding the value itself.

A1:A15 is the array of numbers

In the Frequency function that means the bins would be

1813-301 = 1512
1813+300 = 2113

If you broke down the Frequency function you would see that it is just a
series of counts like this:

=COUNTIF(A1:A15,"<=1512")
=COUNTIF(A1:A15,">1512")-COUNTIF(A1:A15,">=2113")
=COUNTIF(A1:A15,">2113")

So the Frequency function returns the array of these counts to the Index
function:

=INDEX({12;3;0},2)-1

The result we want is equivalent to:

=COUNTIF(A1:A15,">1512")-COUNTIF(A1:A15,">=2113")

which is position 2 of the indexed array {12;3;0}

Then we subtract 1 to exclude the specific value itself.

You could get the same results using a formula like this:

=COUNTIF(A$1:A$15,">"&A1-301)-COUNTIF(A$1:A$15,">="&A1+300)-1

The Index method is a little "slicker" and the average calc times* (5 calcs)
are:

Index = 0.000276 sec
Countif = 0.000274 sec

* using Charles Williams' RangeTimer method

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

I first saw this technique used by Ron Coderre a few weeks ago. I plan on
"promoting" it when the situation arises and I can remember to use it.

Biff
 
Thank T. Valko for your detailed description

Would it be possible to retrieve 300 in Cell C1 for this formula?
Variable seems not able to set within { } .
Thank you for any suggestion
Eric
 
Thank T. Valko for your detailed description
Click to expand...

Make that a double from me ! Enriching clarification.
Thanks for the link.
Would it be possible to retrieve 300 in Cell C1 for this formula?
Variable seems not able to set within { } .
Click to expand...

I played around with a defined range in Biff's formula
which seems to work fine in tests here.

Defined range (created via Insert>Name>Define or use namebox)
Limits =Sheet1!$M$1:$M$2
where inputs in M1: 300, in M2: -301

Then in say B1, copied down to B15:
=INDEX(FREQUENCY(A$1:A$15,A1+Limits),1)-1

---
 
I played around with a defined range in Biff's formula
which seems to work fine in tests here.
Click to expand...

Yeah, that'll work. Until you're comfortable using this method I would
reccomend crafting the formula in a logical fashion: ie: bins from lowest to
highest and then use 2 as position argument in Index.

One thing (really, about the only thing) that can be confusing is that the
lowest bin needs to be 1 increment less than the intended range. That's
because of the way Frequency works. Consider this example:

Count all dates that fall within a date range (inclusive)

Date range = 1/1/2007 to 1/31/2007 (inclusive)

A1:A5 =

12/31/2006
1/1/2007
1/5/2007
1/31/2007
2/13/2007

C1:D1 = 1/1/2007, 1/31/2007

=INDEX(FREQUENCY(A1:A5,C1:D1),2)

Result = 2 which is incorrect

So we need to make C1 12/31/2006

Then the formula returns the correct result which is 3.

Biff
 
Back
Top