Help with Possible Array or Sumproduct Formula

  • Thread starter Thread starter Steph
  • Start date Start date
S

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.
 
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))
 
Hello,

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

Regards,
Bernd
 
Hello Steph,

if your first row might be empty:

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

Regards
Klaus
 
Hi Bernd

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

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

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

Regards,
Bernd
 
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
 
Back
Top