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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Eric" wrote:
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

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

"Eric" wrote in message
...
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

That's a nice one, Biff !
Could you elaborate a little on how it works ?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"T. Valko" wrote:
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

Thank everyone for suggestions
Eric

=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)
a

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

"Max" wrote in message
...
That's a nice one, Biff !
Could you elaborate a little on how it works ?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"T. Valko" wrote:
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

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

"T. Valko" wrote:

=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)
a

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

"Max" wrote in message
...
That's a nice one, Biff !
Could you elaborate a little on how it works ?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"T. Valko" wrote:
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

On Feb 28, 8:33 am, Eric wrote:
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 InsertNameDefine 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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

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

"Max" wrote in message
ps.com...
On Feb 28, 8:33 am, Eric wrote:
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 InsertNameDefine 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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Eric

Thanks for additional clarification, Biff. Cheers.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

You're welcome, Max.

Biff

"Max" wrote in message
...
Thanks for additional clarification, Biff. Cheers.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---