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

 

[Marcelo Branco]

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

See if this works

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

In VBA to enter an array formula in multiple cells it's a little bit tricky - you must enter the formula only in the first cell and then copy/paste or fill it down.
Take a look at
https://colinlegg.wordpress.com/2012/05/23/working-with-range-formulaarray-in-vba/

M.

 

[JumboCactuar]

See if this works

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

so each colour should only have one occurance of 1 in the H Column

In VBA to enter an array formula in multiple cells it's a little bit tricky - you must enter the formula only in the first cell and then copy/paste or fill it down.
Take a look at
https://colinlegg.wordpress.com/2012/05/23/working-with-range-formulaarray-in-vba/

M.

thankyou i got it working

now with this formula

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

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

see for blue, there is duplicate max values
is it possible to just output a "1" for just one record of blue if that makes sense ?
[/FONT]

 

[Aladin Akyurek]

thankyou i got it working

now with this formula

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

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

<tbody>
</tbody>

see for blue, there is duplicate max values
is it possible to just output a "1" for just one record of blue if that makes sense ?
[/FONT]

Control+shift+enter, not just enter:

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

 

[JumboCactuar]

Control+shift+enter, not just enter:

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

Perfect
Thanks again for the help

 

[Aladin Akyurek]

Perfect
Thanks again for the help

Glad to help.