On Nov 19, 1:10*pm, "Bob Phillips" wrote:
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
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