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

Report
Posts
3
Registration date
Wednesday May 29, 2013
Status
Member
Last seen
May 30, 2013
-
Posts
3
Registration date
Wednesday May 29, 2013
Status
Member
Last seen
May 30, 2013
-
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

2 replies

Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
9
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
Status
Member
Last seen
May 30, 2013

Thank you so much! It works beautifully!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!