*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