Help with Possible Array or Sumproduct Formula

[Steph]

I have a series of values in which the maximum value occurs several times. I
am trying to create a formula for each offsetting cell in column B that flags
the first occurance of the maximum value only.

For example, the sample data appears in column A. The desired output
appears in column B.

A1 = 2 B1 = 0
A2 = 7 B2 = 1
A3 = 5 B3 = 0
A4 = 7 B4 = 0
A5 = 3 B5 = 0
A6 = 7 B6 = 0

Cell B2 flags the first occurance of the maximum value (7) with a "1". The
remainder of column B is filled with zeros.

The formula needs to be a single-cell formula for each cell in column B. In
this case I can't use a multi-cell array.

I currently have the single-cell array formula below, but it fails because
it picks up the maximum three times.

{=IF(B2=MAX($A$1:$A$6),1,0)}

Thank you for help that can be provided.

 

[T. Valko]

Try this:

Assuming you don't need to "worry" about empty cells:

Enter this formula in B1:

=--(A1=MAX(A1:A6))

Enter this formula in B2 and copy down to B6:

=--(AND(A2=MAX(A$1:A$6),SUM(B$1:B1)=0))

 

[Bernd P]

Hello,

No array formula needed: In B1 enter
=--(ROW()=MATCH(MAX($A$1:$A$6),$A$1:$A$6,))
and copy down.

Regards,
Bernd

 

[Klaus \Perry\ Pago]

Hello Steph,

if your first row might be empty:

B2: =(A2=MAX($A$2:$A$7))*(SUM($B$1:B1)=0)

Regards
Klaus

 

[Teethless mama]

Try this:

In B1: =(A1=MAX($A$1:$A$6))*(COUNTIF($A$1:A1,A1)=1)
copy down

 

[T. Valko]

Hi Bernd

I think you offered the best solution but I would tweak it slighlty:

=--(ROWS(B$1:B1)=MATCH(MAX(rng),rng,))

 

[Bernd P]

Hi Biff,

Hmm, if I think more about it the second rng needs only to be $A$1:A1,
would you agree?

Regards,
Bernd

 

[Bernd P]

No, wouldn't work because max value might not be found. If we do not
want to search the whole rng we need to trap possible errors...

Regards,
Bernd