Counting cells before/after a maximum value

[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

 

[Paul Hyett]

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.

Well spotted - I did mean to type 'Minimum'.

 

[Paul Hyett]

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)

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.

 

[T. Valko]

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.

How should the count be handled if there are multiple instances of the
minimum?

 

[Paul Hyett]

How should the count be handled if there are multiple instances of the
minimum?

I'd be looking to count how many cells there were after the last
instance of the minimum.

 

[T. Valko]

I'd be looking to count how many cells there were after the last instance
of the minimum.

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,"")

 

[Paul Hyett]

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,"")

Don't you just love long complicated formulas!

Thank you - that did the trick. Much appreciated!

 

[T. Valko]

Don't you just love long complicated formulas!

Thank you - that did the trick. Much appreciated!

Your'e welcome. Thanks for the feedback!