# How to return maximum numeric value if text is in array with MAXA()?

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!`

Where is the problem?

• As written, this question is low quality and IMO should not be migrated to another site.
– Twisty
Jul 26, 2017 at 14:21
• @Twisty You are welcome to suggest your edited version if able (but I don't understand reason to "be migrated to another site") Jul 27, 2017 at 11:34

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!`

• I dont' get - according support.google.com/docs/answer/3094013?hl=en To allow text values, use MAXA. Jul 21, 2017 at 12:18
• and "Any referenced text value in any of the value arguments will be assigned the numeric value 0 for the purpose of this function." Jul 21, 2017 at 12:30
• It is a little odd how it words it, but testing it you can have text in a range of cells and it does count text as 0. So a range like A2:A8 with text gets assigned zero but you can't pass directly as a parameter.
– Confuzing
Jul 21, 2017 at 13:03
• probably #VALUE! is a kind of a "stop" word Jul 21, 2017 at 13:36
• Yes it's like excel if it can't calculate the formula it gives #VALUE! as an error message.
– Confuzing
Jul 21, 2017 at 13:43