MAX Array?

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

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
 
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
 
Rick Rothstein said:
Try this...

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

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))
 
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
 
Try this...
This will produce multiples of the maximum value if there were
multiple instances of the maximum value for a given ID
Click to expand...

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.
Click to expand...

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
 
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
 
Many thanks Rick..I really appreciate your help, and thanks for the
really informative answer.

Regards...Jason
 
Back
Top