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?

  • As written, this question is low quality and IMO should not be migrated to another site. – Twisty Jul 26 '17 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") – AndriuZ Jul 27 '17 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.

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. – AndriuZ Jul 21 '17 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." – AndriuZ Jul 21 '17 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 '17 at 13:03
  • probably #VALUE! is a kind of a "stop" word – AndriuZ Jul 21 '17 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 '17 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.