Google Sheets MAXA Function – How to Return Maximum Numeric Value with Text in Array

google sheetsworksheet-function

UPDATED
MAXA() not returns maximum numeric value if text is in array, as expected according https://support.google.com/docs/answer/3094016

MAXA Returns the maximum numeric value in a dataset.

  • and even sample is with error – returns #VALUE, not maximum numeric

Example:
formula MAXA({1;20;300;"#VALUE!";"Google"}) gives 300
but if values 1; 20; 300; #VALUE!; Google are in cells A9:A13
having array =MAXA(A9:A13) gives #VALUE!

To reproduce: https://docs.google.com/spreadsheets/d/17yJ33Zx24eC7-n-a7EaR9f9knhlQUaKYHEk2ghzZXAI/edit#gid=1923535662

Where is the problem?

Best Answer

You can't pass MAXA() character strings directly (vs having a text value in a range of cells), but can pass numeric string such as "35". You can however pass non-numeric character strings as part of a range of cells (see example below) and it will assign them a value of zero.

The function will return something like #VALUE! or #NAME? as an error message if it cannot calculate the function parameters. If you mouse over the value it returns in your example it will tell you why it failed.

Function MAXA parameter 4 expects number values. 
But '#VALUE!' is a text and cannot be coerced to a number.

Examples:
MAXA(3,4,"5") valid, it is a numeric string
MAXA(3,4,"five") invalid, as it is not a numeric string
MAXA(A1:A6) valid, "text" gets set to 0, when A1 to A6 looks like 

 A1 | -2
 A2 | -4
 A3 | -2
 A4 | -5
 A5 | -1
 A6 | text

Source: the support document you linked.
Note they show in one of their examples that the result of =MINA(A2:A6, "Google") is #VALUE!

Related Topic