MAX Array?
 

Hi,

I have two columns - 'Key' (text, col A) and 'Forecast' (number, col B).

I have several rows for each 'Key' value, but the number of rows *varies*.

How do I (in one function in col C) calculate the maximum value for each
'Key'?

i.e If I have 16 rows all with the 'Key' value LC05XGE - what is the
maximum 'Forecast' value in the corresponding 16 'Forecast' values?

Any help greatly appreciated......Many thanks,

Jason

MAX Array?
 

Try this...

=SUMPRODUCT(MAX((A1:A1000="LC05XGE")*B1:B1000))

Reset the maximum row from the 1000 I used in my example to the maximum row
you ever expect to fill data in. Also, you may want to consider using a cell
to hold the lookup value (LC05XGE) and replace the "LC05XGE" with the cell
address instead.

Rick



"Jay" wrote in message
...
Hi,

I have two columns - 'Key' (text, col A) and 'Forecast' (number, col B).

I have several rows for each 'Key' value, but the number of rows *varies*.

How do I (in one function in col C) calculate the maximum value for each
'Key'?

i.e If I have 16 rows all with the 'Key' value LC05XGE - what is the
maximum 'Forecast' value in the corresponding 16 'Forecast' values?

Any help greatly appreciated......Many thanks,

Jason

MAX Array?
 

"Rick Rothstein" wrote...
Try this...

=SUMPRODUCT(MAX((A1:A1000="LC05XGE")*B1:B1000))
....

This will produce multiples of the maximum value if there were
multiple instances of the maximum value for a given ID, and #VALUE! if
there were any nonnumeric text cells in B1:B1000.

Always safer to use an array formula for this:

=MAX(IF(A1:A1000="LC05XGE",B1:B1000))

MAX Array?
 

That's great Rick - works a treat. My attempts were on the right lines
but I was trying a MAX & IF array - is that also an option?

Also, is there any reason why replacing MAX with MIN doesn't work
(returns 0 every time), because I was hoping for a formula where I could
also find the minimum.

*Many* thanks for your help....Regards, Jason


Rick Rothstein (MVP - VB) wrote:
Try this...

=SUMPRODUCT(MAX((A1:A1000="LC05XGE")*B1:B1000))

Reset the maximum row from the 1000 I used in my example to the maximum
row you ever expect to fill data in. Also, you may want to consider
using a cell to hold the lookup value (LC05XGE) and replace the
"LC05XGE" with the cell address instead.

Rick



"Jay" wrote in message
...
Hi,

I have two columns - 'Key' (text, col A) and 'Forecast' (number, col B).

I have several rows for each 'Key' value, but the number of rows
*varies*.

How do I (in one function in col C) calculate the maximum value for
each 'Key'?

i.e If I have 16 rows all with the 'Key' value LC05XGE - what is the
maximum 'Forecast' value in the corresponding 16 'Forecast' values?

Any help greatly appreciated......Many thanks,

Jason

MAX Array?
 

Try this...

=SUMPRODUCT(MAX((A1:A1000="LC05XGE")*B1:B1000) )

This will produce multiples of the maximum value if there were
multiple instances of the maximum value for a given ID

The best I can tell from my tests... this formula returns the actual maximum
value, even if that maximum value is repeated for the ID being looked up.
Have I misunderstood your comment?

and #VALUE! if there were any nonnumeric text cells in B1:B1000.

True, but the OP specifically said "I have two columns - 'Key' (text, col A)
and 'Forecast' (number, col B)"; hence, I would not expect that he has any
text values in column B (well, maybe a header, but the lower row number in
the ranges can be set to avoid it if there is one).

Rick

MAX Array?
 

The way SUMPRODUCT works is to multiply each expression for each cell value
in the specified range (before feeding it to the MAX function as the formula
is set up) before outputting a result. For those cases where the logical
expression evaluates to FALSE, the multiplication of it times any other
number is 0... hence, the MIN function sees that evaluated 0 as the minimum
compared to all your other positive values. By the way, I should have
mentioned, on the off-chance it could happen, the formula I posted for the
MAX would produce an incorrect result if ALL value for the given ID were
negative (the evaluate 0 for the FALSE conditions would be larger than any
and all negative values). Give this formula a try for your minimum
calculation...

=SUMPRODUCT(MIN(100000-(A1:A1000="LC05XGE")*(100000-B1:B1000)))

Note: The 100000 value I am using is for example purposes only... you should
replace the two occurrences of the number 100000 with a number that is
guaranteed to be larger than any value you could ever have in your Forecast
column.

Rick


"Jay" wrote in message
...
That's great Rick - works a treat. My attempts were on the right lines
but I was trying a MAX & IF array - is that also an option?

Also, is there any reason why replacing MAX with MIN doesn't work (returns
0 every time), because I was hoping for a formula where I could also find
the minimum.

*Many* thanks for your help....Regards, Jason


Rick Rothstein (MVP - VB) wrote:
Try this...

=SUMPRODUCT(MAX((A1:A1000="LC05XGE")*B1:B1000))

Reset the maximum row from the 1000 I used in my example to the maximum
row you ever expect to fill data in. Also, you may want to consider using
a cell to hold the lookup value (LC05XGE) and replace the "LC05XGE" with
the cell address instead.

Rick



"Jay" wrote in message
...
Hi,

I have two columns - 'Key' (text, col A) and 'Forecast' (number, col B).

I have several rows for each 'Key' value, but the number of rows
*varies*.

How do I (in one function in col C) calculate the maximum value for each
'Key'?

i.e If I have 16 rows all with the 'Key' value LC05XGE - what is the
maximum 'Forecast' value in the corresponding 16 'Forecast' values?

Any help greatly appreciated......Many thanks,

Jason

MAX Array?
 

Many thanks Rick..I really appreciate your help, and thanks for the
really informative answer.

Regards...Jason

Rick Rothstein (MVP - VB) wrote:
The way SUMPRODUCT works is to multiply each expression for each cell
value in the specified range (before feeding it to the MAX function as
the formula is set up) before outputting a result. For those cases where
the logical expression evaluates to FALSE, the multiplication of it
times any other number is 0... hence, the MIN function sees that
evaluated 0 as the minimum compared to all your other positive values.
By the way, I should have mentioned, on the off-chance it could happen,
the formula I posted for the MAX would produce an incorrect result if
ALL value for the given ID were negative (the evaluate 0 for the FALSE
conditions would be larger than any and all negative values). Give this
formula a try for your minimum calculation...

=SUMPRODUCT(MIN(100000-(A1:A1000="LC05XGE")*(100000-B1:B1000)))

Note: The 100000 value I am using is for example purposes only... you
should replace the two occurrences of the number 100000 with a number
that is guaranteed to be larger than any value you could ever have in
your Forecast column.

Rick


"Jay" wrote in message
...
That's great Rick - works a treat. My attempts were on the right lines
but I was trying a MAX & IF array - is that also an option?

Also, is there any reason why replacing MAX with MIN doesn't work
(returns 0 every time), because I was hoping for a formula where I
could also find the minimum.

*Many* thanks for your help....Regards, Jason


Rick Rothstein (MVP - VB) wrote:
Try this...

=SUMPRODUCT(MAX((A1:A1000="LC05XGE")*B1:B1000))

Reset the maximum row from the 1000 I used in my example to the
maximum row you ever expect to fill data in. Also, you may want to
consider using a cell to hold the lookup value (LC05XGE) and replace
the "LC05XGE" with the cell address instead.

Rick



"Jay" wrote in message
...
Hi,

I have two columns - 'Key' (text, col A) and 'Forecast' (number, col
B).

I have several rows for each 'Key' value, but the number of rows
*varies*.

How do I (in one function in col C) calculate the maximum value for
each 'Key'?

i.e If I have 16 rows all with the 'Key' value LC05XGE - what is the
maximum 'Forecast' value in the corresponding 16 'Forecast' values?

Any help greatly appreciated......Many thanks,

Jason