Insert $
between A
and 2
in the formula. As CF 'steps through' to test each cell in the range in turn the range is being automatically adjusted. So when for example testing A9 it is checking for the maximum in A9:A1007, which is indeed 14
- so it formats it.
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!
Best Answer
You need following formulas: MAXIFS and MINIFS.