Hi there
Is there a formula to get the number of items in a list defined like
this:, product a, b, c ... has points, and the list is ordered with
decreasing points values. The set is composed of the products that
have points greater or equals to the number in the set.

So for example, with two columns:
a 6
b 6
c 4
d 4
e 2
f 1
g 0

The set is made of a, b, c, d and the result would be 4.

I know I could solve this with vb, but I wonder if a formula could
give me the result. I have tried something with COUNTIF be the trick
is that I would need a criteria saying value in current cell <= row of
the current cell

Thanks a lot for any help

Francois

Is this what you mean?

=COUNTIF(B1:B7,"="&num_points)

--
__________________________________
HTH

Bob

"Francois" wrote in message
...
Hi there
Is there a formula to get the number of items in a list defined like
this:, product a, b, c ... has points, and the list is ordered with
decreasing points values. The set is composed of the products that
have points greater or equals to the number in the set.

So for example, with two columns:
a 6
b 6
c 4
d 4
e 2
f 1
g 0

The set is made of a, b, c, d and the result would be 4.

I know I could solve this with vb, but I wonder if a formula could
give me the result. I have tried something with COUNTIF be the trick
is that I would need a criteria saying value in current cell <= row of
the current cell

Thanks a lot for any help

Francois

On Nov 19, 9:43*am, "Bob Phillips" wrote:
Is this what you mean?

=COUNTIF(B1:B7,"="&num_points)

--
__________________________________
HTH

Bob

"Francois" wrote in message

...

Hi there
Is there a formula to get the number of items in a list defined like
this:, product a, b, c ... has points, and the list is ordered with
decreasing points values. The set is composed of the products that
have points *greater or equals to the number in the set.

So for example, with two columns:
a * 6
b * 6
c * 4
d * 4
e * 2
f * *1
g * 0

The set is made of a, b, c, d and the result would be 4.

I know I could solve this with vb, but I wonder if a formula could
give me the result. I have tried something with COUNTIF be the trick
is that I would need a criteria saying value in current cell <= row of
the current cell

Thanks a lot for any help

Francois

Yes, but num_points is variable since it's the number of items in the
set (or the row position if the items are ordered)

So how do you work out the value of num_points?

--
__________________________________
HTH

Bob

wrote in message
...
On Nov 19, 9:43 am, "Bob Phillips" wrote:
Is this what you mean?

=COUNTIF(B1:B7,"="&num_points)

--
__________________________________
HTH

Bob

"Francois" wrote in message

...

Hi there
Is there a formula to get the number of items in a list defined like
this:, product a, b, c ... has points, and the list is ordered with
decreasing points values. The set is composed of the products that
have points greater or equals to the number in the set.

So for example, with two columns:
a 6
b 6
c 4
d 4
e 2
f 1
g 0

The set is made of a, b, c, d and the result would be 4.

I know I could solve this with vb, but I wonder if a formula could
give me the result. I have tried something with COUNTIF be the trick
is that I would need a criteria saying value in current cell <= row of
the current cell

Thanks a lot for any help

Francois

Yes, but num_points is variable since it's the number of items in the
set (or the row position if the items are ordered)

On Nov 19, 11:20*am, "Bob Phillips" wrote:
So how do you work out the value of num_points?

--
__________________________________
HTH

Bob

wrote in message

...
On Nov 19, 9:43 am, "Bob Phillips" wrote:



Is this what you mean?

=COUNTIF(B1:B7,"="&num_points)

--
__________________________________
HTH

Bob

"Francois" wrote in message

....

Hi there
Is there a formula to get the number of items in a list defined like
this:, product a, b, c ... has points, and the list is ordered with
decreasing points values. The set is composed of the products that
have points greater or equals to the number in the set.

So for example, with two columns:
a 6
b 6
c 4
d 4
e 2
f 1
g 0

The set is made of a, b, c, d and the result would be 4.

I know I could solve this with vb, but I wonder if a formula could
give me the result. I have tried something with COUNTIF be the trick
is that I would need a criteria saying value in current cell <= row of
the current cell

Thanks a lot for any help

Francois

Yes, but num_points is variable since it's the number of items in the
set (or the row position if the items are ordered)

If I do this "by hand", I count the lines and stop when the value of
points is lower then the row I am in (which is also the number of
items in my set).
a 6 1
b 6 2
c 4 3
d 4 4
e 2 I stop above since the 5 rows has a value of 2 points
f 1
g 0

I there a way to do this with a formula ? (I hope I'm clearer ?)

Francois

How about this array formula

=MAX(IF(B1:B7=ROW(B1:B7),ROW(B1:B7)))

--
__________________________________
HTH

Bob

wrote in message
...
On Nov 19, 11:20 am, "Bob Phillips" wrote:
So how do you work out the value of num_points?

--
__________________________________
HTH

Bob

wrote in message

...
On Nov 19, 9:43 am, "Bob Phillips" wrote:



Is this what you mean?

=COUNTIF(B1:B7,"="&num_points)

--
__________________________________
HTH

Bob

"Francois" wrote in message

...

Hi there
Is there a formula to get the number of items in a list defined like
this:, product a, b, c ... has points, and the list is ordered with
decreasing points values. The set is composed of the products that
have points greater or equals to the number in the set.

So for example, with two columns:
a 6
b 6
c 4
d 4
e 2
f 1
g 0

The set is made of a, b, c, d and the result would be 4.

I know I could solve this with vb, but I wonder if a formula could
give me the result. I have tried something with COUNTIF be the trick
is that I would need a criteria saying value in current cell <= row of
the current cell

Thanks a lot for any help

Francois

Yes, but num_points is variable since it's the number of items in the
set (or the row position if the items are ordered)

If I do this "by hand", I count the lines and stop when the value of
points is lower then the row I am in (which is also the number of
items in my set).
a 6 1
b 6 2
c 4 3
d 4 4
e 2 I stop above since the 5 rows has a value of 2 points
f 1
g 0

I there a way to do this with a formula ? (I hope I'm clearer ?)

Francois

=IF(ROW(A2)-ROW($A$2)+1<=B2,A2,"")

Is this what you want? You say the input table is sorted descending.
If you copy the formula down it will start giving blanks when the
relative position in the data becomes greater than the points.

HTH
Kostis Vezerides


On Nov 19, 10:32*am, Francois wrote:
Hi there
Is there a formula to get the number of items in a list defined like
this:, product a, b, c ... has points, and the list is ordered with
decreasing points values. The set is composed of the products that
have points *greater or equals to the number in the set.

So for example, with two columns:
a * 6
b * 6
c * 4
d * 4
e * 2
f * *1
g * 0

The set is made of a, b, c, d and the result would be 4.

I know I could solve this with vb, but I wonder if a formula could
give me the result. I have tried something with COUNTIF be the trick
is that I would need a criteria saying value in current cell <= row of
the current cell

Thanks a lot for any help

Francois

On Nov 19, 4:40*pm, vezerid wrote:
=IF(ROW(A2)-ROW($A$2)+1<=B2,A2,"")

Is this what you want? You say the input table is sorted descending.
If you copy the formula down it will start giving blanks when the
relative position in the data becomes greater than the points.

HTH
Kostis Vezerides

On Nov 19, 10:32*am, Francois wrote:

Hi there
Is there a formula to get the number of items in a list defined like
this:, product a, b, c ... has points, and the list is ordered with
decreasing points values. The set is composed of the products that
have points *greater or equals to the number in the set.

So for example, with two columns:
a * 6
b * 6
c * 4
d * 4
e * 2
f * *1
g * 0

The set is made of a, b, c, d and the result would be 4.

I know I could solve this with vb, but I wonder if a formula could
give me the result. I have tried something with COUNTIF be the trick
is that I would need a criteria saying value in current cell <= row of
the current cell

Thanks a lot for any help

Francois

The above Bob's array formula works if the values are ordered
=MAX(IF(B1:B7=ROW(B1:B7),ROW(B1:B7)))
This array formula works also for unordered data
=MAX(IF(LARGE(B1:B7;ROW(B1:B7))=ROW(B1:B7);ROW(B1 :B7)))
Francois