# Excel Formula help

Discussion in 'Business Applications' started by MrPlibble, Nov 6, 2007.

Not open for further replies.

Joined:
Nov 6, 2007
Messages:
5
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.

2. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
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

3. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
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)

4. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
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

Joined:
Nov 6, 2007
Messages:
5
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.

Joined:
Oct 20, 2004
Messages:
7,837

7. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
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

8. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
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....

9. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
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!

10. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
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?

11. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
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??

12. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Yes, Zack, your post #9 works just the same as my longer method does.

13. ### caraewilton

Joined:
Nov 7, 2007
Messages:
1,352
Sorry but why can't you just use "range"

14. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
@caraewilton: Can you define what you mean by "range"? We are using ranges.

15. ### caraewilton

Joined:
Nov 7, 2007
Messages:
1,352
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.

As Seen On