How to find the number of value within a range?

[Eric]

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

 

[Max]

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.

 

[T. Valko]

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

 

[Max]

That's a nice one, Biff !
Could you elaborate a little on how it works ?

 

[Eric]

Thank everyone for suggestions
Eric

 

[T. Valko]

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

Could you elaborate a little on how it works ?

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

 

[Eric]

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

 

[Max]

Thank T. Valko for your detailed description

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 { } .

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

 

[T. Valko]

I played around with a defined range in Biff's formula

which seems to work fine in tests here.

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

C11 = 1/1/2007, 1/31/2007

=INDEX(FREQUENCY(A1:A5,C11),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

 

[Eric]

Eric

 

[Max]

Thanks for additional clarification, Biff. Cheers.

 

[T. Valko]

You're welcome, Max.

Biff