yogi_Compute Mode Row By Row For Columnar Data Using An Array Formula

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


user Ken Lane US said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/xIFJTDtK__s)
Auto-expansion of formula involving MODE
Chrome v.20.0.1132.47 / Mac OS X v.10.7.4

Before I ask my question, a preemptive "Thank You" to @ahab, @yogia and many others for your advance in this forum...I've gleaned much.

A survey I've created has the option for responses with values between 0 and 4. There are also possible empty cells. I need to determine the MODE for subsequent calculations, but, skipping "0"s and empty cells in my calculation. I have taken sample data of a number of responses, but would like to auto-expand the summary formula, specifically in relation to determining the MODE (Column AF). I have started with the following solution, but I'm not sure how to continue:

=ArrayFormula( MODE( IF( ((B2:AD2>0)*(B2:AD2<>0)) , B2:AD2) ) )

Shared Spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0Ak4Uw2zHfC0LdFNCRGZITmpOVWpUQWs0RHFOdlRHTUE

I have included the subsequent calculations I need to determine for reference (perhaps there is a simpler, more elegant solution that alludes me - highly likely!)

AG = 'Frequency Percentage' is looking for a particular "outlier score", namely '4', and then to establish that response frequency in respect to total responses.

AH = 'Overall Score' first checks for the "outlier score" and if present adds a frequency modifier in decimal form to the '4', else, it sums all values not '0', '4', or blank.

Thanks in advance for any assistance, Ken

------------------------------------------------------------------
following is a solution where in I have used a number of helper columns to prevent the formula from becoming too convoluted