MAX value in one coulmn based upon another column [Solved/Closed]

Posts
3
Registration date
Wednesday May 29, 2013
Last seen
May 30, 2013
- - Latest reply: Piggy70
Posts
3
Registration date
Wednesday May 29, 2013
Last seen
May 30, 2013
- May 30, 2013 at 08:55 AM
Batch Price Column C
00015 2.75
00015 2.9995
00016 1.89
00017 0.56116
00018 0.1421
00018 0.11808
00018 0.1605
00018 0.12575
00018 0.1091
00018 0.1745
00018 0.1342
00018 0.1655
00019 0.1421
00019 0.11808

Hi, I have about 55,000 rows like the above and I am trying to get the maximum price within each batch to display in Column C....example, for Batch 00015, the max price should be $2.9995 (displayed in Column C) - I do not know how to do this in EXCEL - can anyone assist? thank you
See more 

2 replies

Posts
42
Registration date
Thursday January 31, 2013
Last seen
April 26, 2014
0
Thank you
Hi

Try the following array (CTRL+ SHIFT + ENTER to commit). Assuming the batch numbers are in A1:A14 & prices are in C1:C14, also assuming that the batch numbers are "text".

=MAX(IF(A1:A14="00015",C1:C14))

If they are numbers in column A, formatted as "00000".

=MAX(IF(A1:A14=15,C1:C14))

You might want to look at the aggregate function if you have Excel 2010>.
Posts
3
Registration date
Wednesday May 29, 2013
Last seen
May 30, 2013
0
Thank you
Thank you so much! It works beautifully!