# Excel Formula help

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### MrPlibble

Hi all, bit of a newbie to this forum...

I am after some help finding a formula or vbscript or something that can run through a range of numbers in a column, and give me the highest difference between to cells...

ie...

12
13
17
9
15
18
8

As you can see the difference between 12 & 13 is 1, 13 & 17 is 4, 17 & 9 is 8, 9 & 15 is 6, 15 & 18 is 3, 18 & 8 is 10.

I need a formula that can do this, and in ONE cell, give me the HIGHEST difference. in my example the ONE cell answer would be 10 as 10 was the biggest difference.

IS this possible? if so I would be so grateful of your help.

Christian.

#### Zack Barresse

Hi Christian, welcome to the board!

If I'm not mistaken, would not this do what you are asking for? ...

Code:
``=MAX(A1:A10)-MIN(A1:A10)``
HTH

#### slurpee55

I don't think so, firefytr - I think he is looking at the difference between two adjacent cells (A1 vs A2, A2 vs A3, A3 vs A4....) and wants to know what the largest absolute difference is between any two cells that are adjacent. (I'm thinking this might be something like sales on day 1 above sales on day 2, etc.)
I would suspect - if this is a large file, at least - that this will be needing some code.
So Christian, how many comparisons do you have to make? A dozen or so could be written into a formula (I think) but much more than that and it will need code)
Of course, you could just have the formula
=ABS(A1-A2) in B2 and copy it down, then, somewhere that you want to see it have the formula
=Max(B:B)

#### bomb #21

MrPlibble said:
I am after some help finding a formula or vbscript or something that can run through a range of numbers in a column, and give me the highest difference between to cells...
slurpee's spot on for the formula option, except for one thing -- don't use =ABS(n-n+1) for the last value in the column because the last value and the blank below it <> a pair but the diff between the last value and the blank below it might be the largest.

For a script option (and assuming "a column" = column A):

Sub test()
For Each Cell In Range("A2", Range("A" & Rows.Count).End(xlUp))
x = Abs(Cell - Cell.Offset(-1))
If x > y Then
y = x
End If
Next Cell
MsgBox y
End Sub

#### MrPlibble

Thanks for your help guys, I have used the ABS function in another worksheet and then used the final value in the correct place, all working great.  #### slurpee55

Glad to help - please use the thread tools to mark this as solved. #### Zack Barresse

Oh, well for adjacent cells, I still don't think you'll need VBA...

Code:
``=SUMPRODUCT(MAX(A2:A10-A1:A9))``
This is where your dataset is in A1:A10. MrPlibble, please check that and get back to us.

HTH

#### slurpee55

Firefytr, for the following data:
12
13
17
9
15
18
8
12
7
3
the answer should be 10 (the difference between 18 and 8) but your formula gives 6 as the answer. The odd thing is that a1 to a9 here sums to 111 and a2 to a10 sums to 102, and just =SUMPRODUCT(A1:A9-A2:A10) returns the proper answer of 9 - but that still isn't what he wants....

#### Zack Barresse

So you're saying adjacent up or down? Just add the second range..

Code:
``=SUMPRODUCT(MAX(A2:A10-A1:A9,A1:A9-A2:A10))``
I don't think I really understand the OPs request. LOL!

#### slurpee55

Basically, the OP wants the maximum absolute value of the following:
A1-A2, A2-A3, A3-A3, A4-A5....
That is, which is the biggest difference?

#### Zack Barresse

Well, the second formula I produced will look at the following formulas...

A1-A2
A2-A1
A2-A3
A3-A2
A3-A4
A4-A3
A4-A5
A5-A4
A5-A6
A6-A5
A6-A7
A7-A6
A7-A8
A8-A7
A8-A9
A9-A8
A9-A10
A10-A9

The maximum value shall be returned. Is that on target??

#### slurpee55

Yes, Zack, your post #9 works just the same as my longer method does. #### caraewilton

Sorry but why can't you just use "range"

#### Zack Barresse

@caraewilton: Can you define what you mean by "range"? We are using ranges.

#### caraewilton

Sorry, I am a maths person. So the term is math related, not excel related. In maths, "range" is used to calculate the difference between the biggest and smallest number. Excel tends to have functions for most math concepts so I would not be suprised if it actually has this one to.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.