I'm working on a Google Sheet that will include rows where people give a number rating then include text. I want to have a column where I read the rows, parse them into the number and text, then write out the average of the numbers & a list of all the most common text values.

Example:

Column A	Column B	Average
1 Cool text	2 Uncool text	1.5 Cool text, Uncool text
8 Cool text	4 Cool text	6 Cool text
1.5 Cool		1.5 Cool

I'm working on a Google Sheet that will include rows where people give a number rating then include text. I want to have a column where I read the rows, parse them into the number and text, then write out the average of the numbers & a list of all the most common text values.

Example:

Column A	Column B	Average
1 Cool text	2 Uncool text	1.5 Cool text, Uncool text
8 Cool text	4 Cool text	6 Cool text
1.5 Cool		1.5 Cool

and so on

I'm having issues with the MODE function, though.

In the case of row 1 above, I get the error MODE cannot produce a result. No values occur more than once.. I tried using REPT and manually doing a repeat to effectively duplicate all of the values, but I still get the same error:

=MODE.MULT(FLATTEN({(FILTER(A2:B2,NOT(ISBLANK(A2:B2))));(FILTER(A2:B2,NOT(ISBLANK(A2:B2))))}))

I also have not figured out how to display the entire list in one cell once I have it

• google-sheets
• google-sheets-formula

Share Improve this question Follow edited Nov 19, 2024 at 12:28 Alma_Matters 1,01411 gold badge22 silver badges1313 bronze badges asked Nov 19, 2024 at 3:09 bad_at_sheetsbad_at_sheets 311 silver badge11 bronze badge 1

• list of all the most common text values.. how many? – TheMaster Commented Nov 19, 2024 at 6:04

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

Finding the value with most occurrences:

I do think that there is an on going issue with Mode Function that doesn't let it return a string Value, it only works on Int. I have created a Formula that can do both of your expected output.

Try this formula

=LET(c, A1:B1,z,TOCOL(SPLIT(JOIN(" ", c)," ")), y,REDUCE(0,z,LAMBDA(a,r, IF(ISNUMBER(r), SUM(a,r),a))), JOIN("",y/COUNTA(c),CHOOSECOLS(LET(w, LET(x, TOCOL(SPLIT(REGEXREPLACE(REGEXREPLACE(JOIN(",",c),"[.]",""),"[0-9]",""),",")), y, unique(x), z, BYROW(y, lambda(r,COUNTA(FILTER(x, x=r)))), HSTACK(y,z)), FILTER(w,CHOOSECOLS(w,2)=MAX(w))),1)))

Sample Output

Note: This is not on a dynamic spilling function, though you can easily implement that by changing the first value on the range. Feel free to ask any questions if you have one.

References:

Reduce

Filter

This answer relies on a combination of modern and old-school techniques.

Try this formula in Cell C1:

=map(A2:A4,B2:B4,
 lambda(cola,colb,
   if(
     iferror(right(cola,len(cola)-len(iferror(index(SPLIT(cola, " "), 0, 1)))-1))=iferror(right(colb,len(colb)-len(iferror(index(SPLIT(colb, " "), 0, 1)))-1)),
       average(iferror(index(SPLIT(cola, " "), 0, 1)),iferror(index(SPLIT(colb, " "), 0, 1)))&" "&iferror(right(cola,len(cola)-len(iferror(index(SPLIT(cola, " "), 0, 1)))-1)),average(iferror(index(SPLIT(cola, " "), 0, 1)),iferror(index(SPLIT(colb, " "), 0, 1)))&" "&textjoin(", ",true,iferror(right(cola,len(cola)-len(iferror(index(SPLIT(cola, " "), 0, 1)))-1)),iferror(right(colb,len(colb)-len(iferror(index(SPLIT(colb, " "), 0, 1)))-1)))
   )
 )
)

---

Logic

• the formula relies on MAP(array1, [array2, ...], LAMBDA)
  • "array1" = Column A
  • "array2" = Column B
  • `"LAMBDA" works as follows:
    • index(SPLIT(A2, " "), 0, 1) - returns the first element (the number value) in Column A; ditto for Column B
    • average is used to get the average of values in Column A & B
    • right(A2,len(A2)-len(index(SPLIT(A2, " "), 0, 1))-1) - returns the text element for Column A; ditto Column B
    • test for "Column A text"="Column B text"
      • if yes, display the `average&" "&Column A text,
      • if no, display average&" "&textjoin(", ",true,Column A text,Column B text))`
  • add a bunch of iferror

---

Output