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