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, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

On Thu, 21 Feb 2008 at 10:51:34, Pete_UK <(E-Mail Removed)> wrote in
microsoft.public.excel :

>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'.

>> 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.
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

On Thu, 21 Feb 2008 at 14:41:46, T. Valko <(E-Mail Removed)> wrote
in microsoft.public.excel :

>>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
>
>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.
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

"Paul Hyett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Thu, 21 Feb 2008 at 14:41:46, T. Valko <(E-Mail Removed)> wrote
> in microsoft.public.excel :
>
>>>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
>>
>>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.
> --
> Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

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

--
Biff
Microsoft Excel MVP

On Fri, 22 Feb 2008 at 13:03:06, T. Valko <(E-Mail Removed)> wrote
in microsoft.public.excel :
>>>
>>>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.
>
>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.
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

"Paul Hyett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Fri, 22 Feb 2008 at 13:03:06, T. Valko <(E-Mail Removed)> wrote
> in microsoft.public.excel :
>>>>
>>>>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.
>>
>>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.
> --
> Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

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

--
Biff
Microsoft Excel MVP

On Sat, 23 Feb 2008 at 16:44:05, T. Valko <(E-Mail Removed)> wrote
in microsoft.public.excel :
>>>>
>>>> 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.
>>>
>>>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.
>
>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!
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

"Paul Hyett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Sat, 23 Feb 2008 at 16:44:05, T. Valko <(E-Mail Removed)> wrote
> in microsoft.public.excel :
>>>>>
>>>>> 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.
>>>>
>>>>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.
>>
>>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!
> --
> Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

Your'e welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP