1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Formula help

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

Thread Status:
Not open for further replies.
Advertisement
  1. MrPlibble

    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.
     
  2. Zack Barresse

    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

    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

    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
     
  5. MrPlibble

    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)
     
  6. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Glad to help - please use the thread tools to mark this as solved. :)
     
  7. Zack Barresse

    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

    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

    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

    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

    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

    slurpee55

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

    caraewilton

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

    Zack Barresse

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

    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.
     
  16. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/648814

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice