Number of items in a set

[Francois]

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

 

[Bob Phillips]

Is this what you mean?

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

 

[frarapp]

Is this what you mean?

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

--
__________________________________
HTH

Bob

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)

 

[Bob Phillips]

So how do you work out the value of num_points?

--
__________________________________
HTH

Bob

Is this what you mean?

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

--
__________________________________
HTH

Bob

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)

 

[frarapp]

So how do you work out the value of num_points?

--
__________________________________
HTH

Bob




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

 

[Bob Phillips]

How about this array formula

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

--
__________________________________
HTH

Bob

So how do you work out the value of num_points?

--
__________________________________
HTH

Bob




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

 

[frarapp]

How about this array formula

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

--
__________________________________
HTH

Bob





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

That's great ! Thanks it works

I was on the same array formula, a little more verbose =MAX(ROW(a6c)*IF
(a6c>=ROW(a6c);1;0))
Where a6c is the named range of points values.

Now, both solution works only if the points are in decreasing order.

I try to include the following array formula to have the same result
with unordered values
=LARGE(a6c;ROW(a6c))
but for the moment I have error messages only...

Francois

 

[frarapp]

That's great ! Thanks it works

I was on the same array formula, a little more verbose =MAX(ROW(a6c)*IF
(a6c>=ROW(a6c);1;0))
Where a6c is the named range of points values.

Now, both solution works only if the points are in decreasing order.

I try to include the following array formula to  have the same result
with unordered values
=LARGE(a6c;ROW(a6c))
but for the moment I have error messages only...

Francois

OK Here we are
=MAX(IF(LARGE(a6c;ROW(a6c))>=ROW(a6c);ROW(a6c)))
as an array formula

work even if the data in a6c are not ordered.
Many thanks for your help !!

Francois

 

[vezerid]

=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

 

[frarapp]

=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

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