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

Thread Starter
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.


Many thanks in advance...


Christian.
 
Joined
Jul 25, 2004
Messages
5,456
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
 
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)
 
Joined
Jul 1, 2005
Messages
8,546
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

Thread Starter
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.

(y) (y)
 
Joined
Jul 25, 2004
Messages
5,456
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
 
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....
 
Joined
Jul 25, 2004
Messages
5,456
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!
 
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?
 
Joined
Jul 25, 2004
Messages
5,456
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??
 
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.
 
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top