MAXA function

MAXA returns the maximum value in a range, treating text as 0—use it when data mixes text and numbers and you want a predictable result.

=MAXA(value1, ...)

Generate a MAXA formula

Describe what you need. The generator will reach for MAXA where MAXA is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How MAXA reads its arguments
value1requiredMAXA →
ArgumentRequiredDescription
value1RequiredRequired. A number, date, array, or range to evaluate. Text values are treated as 0.
...RepeatingOptional. Additional numbers, dates, arrays, or ranges to compare; text in any argument is treated as 0.

Returns

A number representing the maximum value found (0 if all entries are text).

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Find the maximum hours logged on any task

TaskOwnerStart DateDue DateHours Logged
Design HomepageAlice1/15/20241/22/202416
API IntegrationBob1/16/20241/25/202424
Testing PhaseCharlie1/20/20242/1/20248
DocumentationAlice1/22/20242/5/202412
Bug FixesDavid1/25/20242/10/202415
=MAXA(E2:E6)

Result: 24

The Hours Logged column contains 16, 24, 8, 12, 15. MAXA returns 24, the highest value. Since all entries are numeric, MAXA and MAX return the same result, making it useful for finding the most time-intensive task.

2. Find the most recent due date

TaskOwnerStart DateDue DateHours Logged
Design HomepageAlice1/15/20241/22/202416
API IntegrationBob1/16/20241/25/202424
Testing PhaseCharlie1/20/20242/1/20248
DocumentationAlice1/22/20242/5/202412
Bug FixesDavid1/25/20242/10/202415
=MAXA(D2:D6)

Result: 45348 (displays as 2/10/2024)

Spreadsheets store dates as serial numbers. The due dates range from 1/22/2024 to 2/10/2024. MAXA returns the highest serial number (45348), representing the latest deadline. This is valuable for identifying the final project milestone.

3. Find the maximum value when the range contains only text

TaskOwnerStart DateDue DateHours Logged
Design HomepageAlice1/15/20241/22/202416
API IntegrationBob1/16/20241/25/202424
Testing PhaseCharlie1/20/20242/1/20248
DocumentationAlice1/22/20242/5/202412
Bug FixesDavid1/25/20242/10/202415
=MAXA(B2:B6)

Result: 0

The Owner column contains only text values (Alice, Bob, Charlie, David). MAXA treats all text as 0, so it returns 0. This demonstrates a key difference from MAX, which would ignore text entirely. MAXA guarantees predictable handling when data types are mixed.

Common errors

Which MAXA error are you seeing?
MAXA returned an error#REF!
Update the formula to reference valid cells. For example, if you used =MAXA(E2:E100) but deleted rows 7 through 100, change it to =MAXA(E2:E6).
#VALUE!
Exclude the cell with the formula from its own range. For instance, place =MAXA(E2:E5) in cell E7, never in any cell within E2:E5.
#N/A
Wrap the source formula with IFERROR to replace #N/A values with a safe default: =MAXA(IFERROR(VLOOKUP(...),0)).
ErrorWhy it happensHow to fix it
#REF!The range references cells or columns that have been deleted and no longer exist in the worksheet.Update the formula to reference valid cells. For example, if you used =MAXA(E2:E100) but deleted rows 7 through 100, change it to =MAXA(E2:E6).
#VALUE!A circular reference exists—the cell containing the MAXA formula references itself, either directly or through another formula.Exclude the cell with the formula from its own range. For instance, place =MAXA(E2:E5) in cell E7, never in any cell within E2:E5.
#N/AOne or more arguments contain a #N/A error propagated from another function, such as a failed VLOOKUP or unmatched INDEX/MATCH.Wrap the source formula with IFERROR to replace #N/A values with a safe default: =MAXA(IFERROR(VLOOKUP(...),0)).

Tips and when to use something else

  • MAXA treats text as 0, making results predictable in ranges with mixed data types—if you want text ignored entirely, use MAX instead.
  • For purely numeric ranges, MAXA and MAX return identical results; MAXA is safer if the range might contain text in the future.
  • Combine MAXA with IF in an array formula to find the maximum of a filtered subset: {=MAXA(IF(B2:B6="Alice",E2:E6,0))} returns the highest hours for a specific team member.
  • Dates work seamlessly with MAXA because they are stored as numbers—find the latest date in a project timeline with a single formula.

Related functions

MAXMAX returns the largest numeric value in a range, ignoring text and empty cells. Useful for finding peak inventory, highest prices, or latest dates.MINAMINA returns the smallest numeric value in a range, treating text as zero and logical values as numbers—useful when your data mixes different types.MAXIFSReturns the largest value in a range that meets multiple criteria, combining the power of MAX with IF-like filtering.AVERAGEAAVERAGEA returns the average of values in a range, treating text as 0 and logical values as 1 (TRUE) or 0 (FALSE).COUNTACOUNTA counts the number of non-empty cells in a range, regardless of their content type or whether they contain text, numbers, or errors.

Frequently asked questions

When should I use MAXA instead of MAX?
Use MAXA when your range might contain both numbers and text, and you want a guaranteed result. MAXA treats text as 0; MAX ignores text. For purely numeric ranges, both return the same value. MAXA is the safer choice if data types could vary unexpectedly.
What happens if all values are text?
MAXA returns 0, because every text entry is treated as 0. If you need to count text entries instead, use COUNTA. If you're checking whether a range contains only text, test whether MAXA equals 0.
Does MAXA work with dates?
Yes. Dates are internally stored as numbers (serials), so MAXA treats them as numbers and returns the highest serial, which corresponds to the latest date. Use =MAXA(D2:D6) to find the final deadline in a project schedule.
How do MAXA and MAX differ with mixed text and numbers?
MAX returns only the maximum numeric value and ignores text entirely. MAXA treats text as 0 and includes it in comparison. With positive numbers and text, both return the same result. With negative numbers, MAXA might return 0 (from text) while MAX returns a negative number.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17