Wednesday, October 17, 2012

yogi_Extract The Maximum Value Of Data Per User Per Round From A Table Of User Round And Data

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #812  Oct 17, 2012    www.energyefficientbuild.com.

user CornelB said: (http://productforums.google.com/forum/?zx=3rrg4qfnsz5v#!category-topic/docs/spreadsheets/r4x4fvlrTRQ)
ArrayFormula, Filter by two conditions, Get Max Value
Scenario:
User fills a form With UserName (filter_1), round number (filter_2), data (numeric)
User can (not necesarily will) fill the form several times for a round.



I need to have that data sorted and centralized in another sheet and, extract the MAX() value of data for each user and each round.
Column headers:
User,Round1,Round2,Round3 ....

I've tried Filter, Query, MAX(IF()) ...

Right now i'm stuck at MMULT. I can get the SUM() ot that data. How can i retrieve MAX() ?

=ARRAYFORMULA( IF( A2:A="", "", MMULT( A2:A=TRANSPOSE(data!B2:B), data!D2:D * (B1:C1=data!C2:C)) ) ) )


Any ideas?
Thank you for your time.
----------------------------------------------------------------------------------------------
following is a solution to the problem