How to find out Maximum number ?

[sanbhagade]

Hi,


a 10

a 11

a 25

a 12

a 1

b 25

b 50

b 37

b 48

c 35

c 45

c 52

c 12

c 75


In the above data how can i find out the max number for a, b and c by using one formula.

Can you help me how can i use sumproduct formula. However i am aware about the array formula "=MAX(($E$4:$E$17=H4)*$F$4:$F$17)" but i want to know the sumproduct formula which is not array formula.

Thanks in advance,

Santosh

 

[Luke M]

=SUMPRODUCT(MAX((E4:E17=H4)*(F4:F17)))


The two arrays multiplied inside the MAX function give you the array of numbers, and the SUMPRODUCT is simply there to make sure the whole thing gets treated as an array. Note that this method won't work for MIN (or negative numbers, I suppose)

 

[shrivallabha]

Hi Santosh,


Welcome to Chandorg.


Please note that SUMPRODUCT is an ARRAY formula with non-typical behaviour i.e. we do not have to commit formula by using CTRL+SHIFT+ENTER but that does not mean it isn't ARRAY formula. Read here:

http://www.vbaexpress.com/forum/forumdisplay.php?f=98


Hi Luke,

We can coerce SUMPRODUCT to give MIN by using something like:

=SUMPRODUCT(MIN(IF(E4:E17=H4,F4:F17,MAX(F4:F17))))

 

[sanbhagade]

Hi Luke and shrivallabha,


thanks for your help. when i am trying this sumproduct formula in same sheet where the data contains the formula is working fine. But when i am trying this in another sheet (other than the data sheet) the formula doesnt work.

Could you please help me in to that.


thanks again.

Santosh

 

[nazmul_muneer]

Dear Sanbhagade,

I have tested Luke M's formula its working.

Type your data from A1 in Sheet2

Type A1=a, B1=b, C1=c in Sheet3

Type the formula in B1 in the sheet3

=SUMPRODUCT(MAX((Sheet2!$A$1:$A$14=A1)*Sheet2!$B$1:$B$14))


Regards,

Muneer

 

[Colin Legg]

Hi shrivallabha,

We can coerce SUMPRODUCT to give MIN by using something like:

=SUMPRODUCT(MIN(IF(E4:E17=H4,F4:F17,MAX(F4:F17))))

I guess the MAX() section at the end of that formula is a typo?


If you put an IF() statement inside SUMPRODUCT() like that, it needs to be CTRL+SHIFT+ENTER'd anyway, so the SUMPRODUCT() is redundant in this case. You might as well just use


{=MIN(IF(E4:E17=H4,F4:F17))}


-------------


Hi sanbhagade,


Going back to the original question, what's wrong with CTRL+SHIFT+ENTERing the MAX() formula? It calculates just as quickly (if anything, I'd expect fractionally faster) than the SUMPRODUCT(MAX()) one.

 

[shrivallabha]

Hi Colin,


You are right.

 

[kmjamal]

Thanks this was helpful.