How to find the largest absolute value in Excel
Excel formula
=MAX(ABS(B2:B6))Take MAX of the absolute values: =MAX(ABS(B2:B6)) in Excel 365, or =MAX(MAX(B2:B6),-MIN(B2:B6)) in any version.
- Result on the example
- 3.40 in
E1 - Works in
- Every current version
- Google Sheets
- Different formula, see below
See it work
The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.
How it works
ABS turns every change into its size, so Tuesday's -3.40 becomes 3.40, and MAX returns the biggest. Plain MAX would return 2.95 and miss the larger drop.
=MAX(ABS(B2:B6))Result3.4
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(MAX(ABS(B2:B6))). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =MAX(MAX(B2:B6),-MIN(B2:B6)) | 3.40 | Compares the biggest rise with the biggest fall; no array needed in any version. |
| =INDEX(B2:B6,MATCH(MAX(ABS(B2:B6)),ABS(B2:B6),0)) | -3.40Different result | Returns the move with its sign, so you can tell a rise from a fall. Keeps the sign: -3.40. |
Common mistakes
- In Excel 2019 and older,
=MAX(ABS(B2:B6))needs Ctrl+Shift+Enter, or it uses only one cell. =ABS(MAX(B2:B6))is the absolute value of the largest number, not the largest absolute value.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Find the largest absolute value
E1, find the size of the largest price move in B2:B6, up or down.Questions
How do I find the smallest absolute value?
Use =MIN(ABS(B2:B6)), the move closest to zero.
Which day had the biggest move?
Use =INDEX(A2:A6,MATCH(E1,ABS(B2:B6),0)).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.