Need Max of Difference between 2 numbers in a range

[wowzers]

Hi, I am trying to find the Abs Maximum difference between the first cell in the range and the cell that is greatest in Absolute difference from it within the cell range.

I tried using the MAX function but it returns the max value in the range whereas I want the Max value after the other values in the range have been subtracted from the first cell in the range (Greatest difference.


Please see attached worksheet

btw: the 10000 in the formula is just to make the small numbers more easy to read

[Phuocam]

Try this ...

=MAX(ABS(A2-$A$2:$A$8))*10000

Enter with Ctrl+Shift+Enter.

[wowzers]

Try this ...

=MAX(ABS(A2-$A$2:$A$8))*10000

Enter with Ctrl+Shift+Enter.

Hi, Thanks for the response but that is just spitting out the same incorrect values that the formula I came up with is...

instead of 15 for the first calculation, the correct answer should be 16 which is derived from Abs(.0017-.0001).

[Phuocam]

Hi, Thanks for the response but that is just spitting out the same incorrect values that the formula I came up with is...

instead of 15 for the first calculation, the correct answer should be 16 which is derived from Abs(.0017-.0001).

Cell A6 = 0.001 not 0.0001

[wowzers]

yes thats right.

you know what i mean :-)

[wowzers]

Ok, thank you .now it works.

my question: whats the cntrl+shift+enter do?

[Phuocam]

It is array formulas.