Counting cells before/after a maximum value

  • Thread starter Thread starter Paul Hyett
  • Start date Start date
P

Paul Hyett

If I have a list of numbers like the following...

2,5,2,2,2,2,2,2,2,1,2,5,5,6,9,11,8,11,12

.... across a row of cells, is there a way of counting the number of
(non-empty) cells to left of the maximum value, and also to the right of
it?

In the above example, the answer to both should be 9, of course.

TIA
 
Well, I think the answer would be 9 for both if the number 1 in the
middle of your data was actually a mis-type for something like 13 or
14, or if you want to count away from the MINIMUM and not maximum.
Click to expand...

Well spotted - I did mean to type 'Minimum'.
 
If you meant *minimum*
Yes.

then the answer would be 9 and 9.

Try this based on the *first instance of the minimum* :

For the count to the left of the *first instance of the minimum* :

=IF(COUNT(A1:S1),COUNT(A1:INDEX(A1:S1,MATCH(MIN(A1:S1),A1:S1,0)))-1,0)

For the count to the right of the *first instance of the minimum* :

=IF(COUNT(A1:S1),COUNT(S1:INDEX(A1:S1,MATCH(MIN(A1:S1),A1:S1,0)))-1,0)
Click to expand...
I'll try this, though I should read up on what Index & Match do, as I
won't know what's going on otherwise. :)

Well it does what I want for instances to the left of the minimum value,
but fails on the RHS if the minimum value occurs more than once in the
list, though the latter isn't too important.

Thanks.
 
Paul Hyett said:
I'll try this, though I should read up on what Index & Match do, as I
won't know what's going on otherwise. :)

Well it does what I want for instances to the left of the minimum value,
but fails on the RHS if the minimum value occurs more than once in the
list, though the latter isn't too important.

Thanks.
Click to expand...

How should the count be handled if there are multiple instances of the
minimum?
 
How should the count be handled if there are multiple instances of the
minimum?
Click to expand...
I'd be looking to count how many cells there were after the last
instance of the minimum.
 
Paul Hyett said:
I'd be looking to count how many cells there were after the last instance
of the minimum.
Click to expand...

Try this for the count to the right of the last instance of the min:

=IF(COUNT(A1:S1),COUNT(S1:INDEX(A1:S1,LOOKUP(2,1/(A1:S1=MIN(A1:S1)),COLUMN(A1:S1)-MIN(COLUMN(A1:S1))+1)))-1,"")
 
Try this for the count to the right of the last instance of the min:

=IF(COUNT(A1:S1),COUNT(S1:INDEX(A1:S1,LOOKUP(2,1/(A1:S1=MIN(A1:S1)),COLU
MN(A1:S1)-MIN(COLUMN(A1:S1))+1)))-1,"")
Click to expand...
Don't you just love long complicated formulas! :)

Thank you - that did the trick. Much appreciated!
 
Back
Top