Google Sheets/Excel Formula to Discern Text from Numbers

Question

Forewarning, I'm asking for Excel help, but running my spreadsheet in Google Sheets.

I've made a few different macro-enabled sheets where a user does a workout, clicks the "Finish" button, and it logs their results on a separate worksheet tab. I'm trying to expand this to better account people using resistance bands versus weights. The difference in the data would be resistance bands are entered as text where weights would be entered as a number.

The challenge I'm running into is generating an average; I'm trying to get a nested IF formula to do an Average formula for all the cells that have numbers, and a Mode formula for the cells that have text. There's a drop-down that a user would select that would the IF statement would reference for whether to search for numbers or text.

Is this even possible? The two formulas I have are working fine independently, as long as the text and numeric values are segregated. I've Googled and searched StackOverflow for solutions, but I've only found ways to return specific text strings, not determine if a value in a cell is numeric versus text. I'm specifically trying to avoid VBA.

Thanks in advance!

*Edit This is a quick, simplified version of what I'm trying to achieve. Normally I'd have the information on a few different tabs, but trying to make it easy to visualize. There's a validated cell in A1 where a person would select "Dumbbells" or "Bands". I'm showing the two working formulas I'm trying to combine in cells D1 & D2. What I'm trying to achieve is if A1=Dumbbells, it averages only the numeric values in B6:B500 (as noted by Scott Cramer, =AVERAGE already addresses only numeric values). The challenge is then if A1=Bands, that the formula in D2 evaluates only cells in the range with text, and returns the MODE of those qualifying cells. As is, if the MODE is a numeric value, it returns the number.

Example Spreadsheet Screenshot

Example Spreadsheet

in excel: =ISNUMBER(--A1) it will return TRUE if number and FALSE if not. — Scott Craner
and if you are running it in google you should be tagging it for google-sheets. the answers for Excel may not work in Google-sheets and vice versa. — Scott Craner
Thanks Scott, fixed the tags. I'm looking for more than a TRUE/FALSE that the =ISNUMBER will provide, as I'm hoping to get the whole average of all numbers in a column of mixed numbers and text. — rant
Gotcha. Didn't know that. I still was hoping for a way to get a MODE of all cells that had text in the same range, ignoring the cells with numeric values. — rant

doubleunary doubleunary · Accepted Answer · 2021-05-08T15:56:51

You apparently want one formula that will give a different result depending on whether the value in cell A1 is "Bands" or "Dumbbells". Try this:

=arrayformula( 
  ifs( 
    A1 = "Dumbbells", 
      average(B6:B), 
    A1 = "Bands", 
      query( 
        query( t(B6:B), "select Col1, count(Col1) where Col1 is not null group by Col1 order by count(Col1) desc label count(Col1) '' ", 0 ), 
        "select Col1 limit 1", 
        0 
      ), 
    true, 
      iferror(1/0)
  ) 
)

Note that this is a Google Sheets formula.

My understanding is that mode() only accepts numbers and will not work with text.

To get the latest dumbbells and bands, use these formulas:

=filter( B6:B, row(B6:B) = max(row(B6:B) * isnumber(B6:B)) )

=filter( B6:B, row(B6:B) = max(row(B6:B) * not(isnumber(B6:B)) * not(isblank(B6:B)) ))

Google Sheets/Excel Formula to Discern Text from Numbers

2 Answers