CODEWITHSUNDEEP
excelif-statementgoogle-sheets
rant
rant

Reputation: 19

Google Sheets/Excel Formula to Discern Text from Numbers

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

Upvotes: 1

Views: 247

Answers (2)

doubleunary
doubleunary

Reputation: 18819

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)) ))

Upvotes: 2

MattKing
MattKing

Reputation: 7773

This should return "Blue".

=INDEX(SORT(FILTER({B6:B,COUNTIF(B6:B,B6:B)},ISTEXT(B6:B)),2,0),1,1)

Note that it will not work in excel.

Upvotes: 1

Related Questions