Largest difference in a row

[markus2330]

Dear community,

is there some function that can directly calculate the largest difference from one cell to another?
E.g. I have

20
30
25

Then I would have the absolute differences (from one cell to the next) 10 and 5, so the result should be 10. It is quite easy to do if you first calculate the absolute differences in a new row, and then the maximum. But I have dozens of rows and this would be cumbersome. Is there a function to directly calculate with the contents from one cell to another for the whole row?

best regards
Markus

[JohnSUN]

Welcome!

Yes, it’s possible.

Let’s say your string occupies the range of cells B6:L6. So you need to subtract the values of cells B6:K6 (from the first to the penultimate) from cells C6:L6 (from the second to the end of the row) .
The formula {=MAX(C6:L6-B6:K6)} will return the correct result if you enter it as an array formula, complete the input by pressing Ctrl+Shift+Enter instead of the usual Enter (otherwise the formula will return #VALUE!)

If you don’t like array formulas (they are really a bit complicated for beginners), then just wrap the formula in the SUMPRODUCT() function

=SUMPRODUCT(MAX(C6:L6-B6:K6))

[LeroyG]

=SUMPRODUCT(MAX(C6:L6-B6:K6;B6:K6-C6:L6))

Sample file: largest_difference.ods (10.0 KB)

@EarnestAl

ABS included

EDIT: I can’t find a way to make ABS() work with SUMPRODUCT().

[EarnestAl]

How about =SUMPRODUCT(MAX(ABS(B6:K6-C6:L6)))

[LeroyG]

@EarnestAl

How about…

Yes, that’s okay. Obviously what I tried (I don’t remember exactly what) was not correct (or it seemed to me that it was not). Thanks.

[Lupp]

Not just, but halfway for fun:
giftigeSchwammerlFuerVerirrteFeinschmecker.ods (15.0 KB)
[editing]
giftigeSchwammerlFuerVerirrteFeinschmeckerNachgewuerzt.ods (15.2 KB)
Thanks a lot to @EarnestAl! Not only was an enclosing ABS() missing in three places, but also were zthe formulas in E2 and in G2 of the sheet inconsistent containing the ABS() in one place but lacking it in the corresponding one. Sorry. I uploaded the rectified version.
[/editing]
The original example looked like a column to me. I did it therefore for a column of reasonably extended length.
In Addition I assumed somebody might be interested to also find out where the extreme difference occurs the first time.
Concerning the funny filename: I would dissuade from doing such things the one-cell-formula-way.
Using helpers often makes concepts clear and formulas maintainable and scalable. IOt can also even be more efficient.

[EarnestAl]

I like the spreadsheet.
With negative numbers in the sheet and if just the biggest absolute difference is needed then ABS included would be {=MAX(ABS(A2:A27-A3:A28))}

[MacroMan1]

Very simple formula to get the difference between the smallest and the largest number in a cell range:

=MAX(A1:A8)-MIN(A1:A8)