Thursday, February 17, 2011

yogi_ComputeHighestValueInColBForEveryDateInColA

                                         Google Spreadsheet   Post  #
                                                 (updated Oct-24-2015)
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.  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))