1

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?

2
  • 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")
    – AndriuZ
    Jul 27, 2017 at 11:34

1 Answer 1

1

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!

12
  • I dont' get - according support.google.com/docs/answer/3094013?hl=en To allow text values, use MAXA.
    – AndriuZ
    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."
    – AndriuZ
    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
    – AndriuZ
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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