Thursday, February 17, 2011


                                         Google Spreadsheet   Post  #
                                                 (updated Oct-24-2015)
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI  Feb-17-2011

Zero0nee said ...
I have column A and B. What I want to do is to create a new column C that contains the highest value in B for every one of the dates in A
So, let us go ...

Source data is in cells A2:B
my convoluted formula is in cell C1 (this one does need cleaning up and streamlining)

with Hearty Thanks to Andre aka ahab for clear insight,
my streamlined formula in cell F1 is :

=if(A2:A,mmult((A2:A=transpose(index(query(A2:B,"select A,max(B) where A is not null group by A label max(B) '' "),0,1)))*(transpose(index(query(A2:B,"select A,max(B) where A is not null group by A label max(B) '' "),0,2))),sign(row(indirect("A1:A"&count(unique(A2:A)))))),iferror(1/0))