Can i combine these 2 formulas into 1? MAX IF

[JumboCactuar]

Hi,

i have the 2 formulas which work

G2

{=MAX(IF($A$2:$A$20000=A2,$G$2:$G$20000,0))}

gets max value of column A data

H2

=IF(G2=O2,1,0)

checks to see if value in G2 is highest, if so output 1

Im sure i can merge these somehow

thanks in advance

 

[Aladin Akyurek]

Are you wanting the formula of H2 to copy down?

 

[snjpverma]

which version of excel are you using?

 

[Marcelo Branco]

Hi,

i have the 2 formulas which work

G2

{=MAX(IF($A$2:$A$20000=A2,$G$2:$G$20000,0))}

gets max value of column A data

I do not think this formula can work - circular reference.

M.

 

[JumboCactuar]

sorry guys, where i post G2/H2 that isnt right. its H2 and I2

this is example data:

A	B	C	D	E	F	G	H	I
red	0	0	0	0	0	216	254	0
red	0	0	0	0	0	254	254	1
blue	0	0	0	0	0	571	571	1
blue	0	0	0	0	0	254	571	0
blue	0	0	0	0	0	44	571	0
yellow	0	0	0	0	0	33	33	1
green	0	0	0	0	0	480	514	0
green	0	0	0	0	0	48	514	0
green	0	0	0	0	0	514	514	1
black	0	0	0	0	0	81	134	0
black	0	0	0	0	0	37	134	0
black	0	0	0	0	0	134	134	1
white	0	0	0	0	0	236	236	1
white	0	0	0	0	0	39	236	0

<tbody>
</tbody>

H + I are my helper columns

H2:

=MAX(IF($A$2:$A$20000=A2,$G$2:$G$20000,0))

I2:

=IF(G2=H2,1,0)

i can autofill them down to last row
just wondering if i can do this all in 1 column instead of 2

thanks

 

[Aladin Akyurek]

One of:

1. Control+shift+enter, not just enter, and copy down:

=($G2=

MAX(IF($A$2:$A$20000=$A2,$G$2:$G$20000​

)))+0

2. If you have MAXIFS, just enter and copy down:

=($G2=MAXIFS($G$2:$G$20000,

$A$2:$A$20000,$A2))+0​

​

 

[JumboCactuar]

One of:

1. Control+shift+enter, not just enter, and copy down:

=($G2=

MAX(IF($A$2:$A$20000=$A2,$G$2:$G$20000​

)))+0

2. If you have MAXIFS, just enter and copy down:

=($G2=MAXIFS($G$2:$G$20000,

$A$2:$A$20000,$A2))+0​

​

perfect, 1st one works
2nd gives #NAME

thankyou

 

[Aladin Akyurek]

perfect, 1st one works
2nd gives [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=NAME]#NAME [/URL]

The #NAME ? indicates that your version of Excel does not have the MAXIFS function.

thankyou

You are welcome.

 

[JumboCactuar]

is there a reason why it doesnt let me fill down this array formula with vba ?

ive tried these 2:

        lr = Cells(Rows.Count, "L").End(xlUp).Row
   	Range("x2:x" & lr).FormulaArray = "=(RC7=MAX(IF(R2C1:R20000C1=RC1,R2C7:R20000C7)))+0"

	lr = Cells(Rows.Count, "L").End(xlUp).Row
   	Range("x2:x" & lr).FormulaArray = "=(RC7=MAX(IF(R2C1:R20000C1=RC1,R2C7:R20000C7)))+0"
    	Range("x2:x" & lr).FillDown

it outputs the exact formula in every cell

=($G2=MAX(IF($A$2:$A$20000=$A2,$G$2:$G$20000)))+0

 

[Aladin Akyurek]

is there a reason why it doesnt let me fill down this array formula with vba ?
[...]

Hope someone into VBA will chim in...