CODEWITHSUNDEEP
excelarray-formulas
AlanB
AlanB

Reputation: 11

Excel worksheet maximum of calculated value

Is there a worksheet function that enables you to find the maximum value of a series of calculations. I'm trying to find the highest difference between two columns without creating a new column with the difference in. I could use =max(l1-m1,l2-m2,l3-m3) etc. but there are hundreds of rows. Max(l1-m1:l100-m100) doesn't work

Upvotes: 1

Views: 55

Answers (1)

Mrig
Mrig

Reputation: 11727

Try this:

=MAX(IF(ISNUMBER(L1:L100)*ISNUMBER(M1:M100),ABS(L1:L100-M1:M100),""))

Its an array formula, so commit the formula by pressing Ctrl + Shift + Enter

If your data is consistent you can even use:

=MAX(ABS(L1:L100-M1:M100))

Again this is an array formula.

Got this from here.

Upvotes: 3

Related Questions