+ Reply to Thread
Results 1 to 17 of 17

Max difference between two consecutive cells in percent

  1. #1
    Registered User
    Join Date
    07-04-2017
    Location
    Kharkiv
    MS-Off Ver
    Excel2016
    Posts
    9

    Max difference between two consecutive cells in percent

    Hello!
    Need help with the following case:

    I have a row with numbers 50 23 45 33 26 56 22, for example.
    They are in Cells A1:G1. To calculate the maximum difference between two consecutive cells I use the following array formula:
    =MAX(ABS(B1:G1-A1:F1))

    In this case the max difference is 34, it's the difference between 22 and 56 in cells G1 and F1. However, I need to get this max difference in percent, what will be -155%
    The question is, is there any formula so I could get the value in percent immediately?

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Max difference between two consecutive cells in percent

    Ther's certainly going to be a nicer way, but this works:

    =100*MAX(ABS(B1:G1-A1:F1))/(MIN(INDEX(A1:G1,MATCH(MAX(ABS(B1:G1-A1:F1)),ABS(B1:G1-A1:F1),0)),INDEX(A1:G1,MATCH(MAX(ABS(B1:G1-A1:F1)),ABS(B1:G1-A1:F1),0)+1)))

    an array formula.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Contributor
    Join Date
    03-22-2017
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Max difference between two consecutive cells in percent

    if the different is divide by B1:G1 (ie 34/22)

    here is the array formula, and you have to change the format of the cell to percentage by your self
    =MAX(ABS(B1:G1-A1:F1)/B1:G1)

    if you refuse to do so, use text functions
    =text(MAX(ABS(B1:G1-A1:F1)/B1:G1),"####%")
    Hope you can learn every time you visit here.

    If you still confuse on how it work, kindly ask or go to
    i) Formula - Formula (Ribbon) > Formula Auditing (Section) > Evaluate Formula > Evaluate; or
    ii) VBA/Code - Click F8 to see how it work step by step.

    It it take care of your question, Please:
    Mark tread as [Solved] [Thread Tools->Mark thread as Solved]
    ;and
    Click *Add Reputation to thank anyone solved your question.

  4. #4
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Max difference between two consecutive cells in percent

    =A3/LOOKUP(10000000,B1:G1/(ABS(B1:G1-A1:F1)=A3))
    in A3 your formula

  5. #5
    Registered User
    Join Date
    07-04-2017
    Location
    Kharkiv
    MS-Off Ver
    Excel2016
    Posts
    9

    Re: Max difference between two consecutive cells in percent

    =MAX(ABS(B1:G1-A1:F1)/B1:G1)
    This doesn't work in our case. It suites if we calculate difference between two cells, but not between the range of cells.

    Anyway, thanks for the reply

  6. #6
    Registered User
    Join Date
    07-04-2017
    Location
    Kharkiv
    MS-Off Ver
    Excel2016
    Posts
    9

    Re: Max difference between two consecutive cells in percent

    Quote Originally Posted by Glenn Kennedy View Post
    Ther's certainly going to be a nicer way, but this works:
    I get an error. Probably some parentheses in wrong places.

  7. #7
    Registered User
    Join Date
    07-04-2017
    Location
    Kharkiv
    MS-Off Ver
    Excel2016
    Posts
    9

    Re: Max difference between two consecutive cells in percent

    Quote Originally Posted by tim201110 View Post
    =A3/LOOKUP(10000000,B1:G1/(ABS(B1:G1-A1:F1)=A3))
    in A3 your formula
    Doesn't work as well. Receive message "There is a problem with your formula".

    I am not very strong in excel. Guys, sorry if don't understand something

  8. #8
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Max difference between two consecutive cells in percent

    vse rabotaet
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-04-2017
    Location
    Kharkiv
    MS-Off Ver
    Excel2016
    Posts
    9

    Re: Max difference between two consecutive cells in percent

    Quote Originally Posted by tim201110 View Post
    vse rabotaet
    Spasibo!
    Made some mistakes before

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Max difference between two consecutive cells in percent

    Format the formula cell as percentage to 2 decimal places. Then array enter this formula. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  11. #11
    Registered User
    Join Date
    07-04-2017
    Location
    Kharkiv
    MS-Off Ver
    Excel2016
    Posts
    9

    Re: Max difference between two consecutive cells in percent

    Thank you for this option. However, it works not for all my data. Get div/0 error in some

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Max difference between two consecutive cells in percent

    Perhaps you need to upload a workbook with multiple samples of what you are working with and expected results ... hand typed in. Be sure to include examples of where this is not working.

    If you are not familiar with how to do this:

    To attach a file to your post,
    • be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.

  13. #13
    Registered User
    Join Date
    07-04-2017
    Location
    Kharkiv
    MS-Off Ver
    Excel2016
    Posts
    9

    Re: Max difference between two consecutive cells in percent

    Quote Originally Posted by FlameRetired View Post
    Perhaps you need to upload a workbook


    Thank you for your help!
    Here is an .xslx file with 6 rows of data. Actually, I have more than 200 rows, let this be an example.

    Let me explain it once again. I need to know the decrease of value in cell2 in comparison to cell1, for example, as a result I need to get such maximum decline between 2 cells in a given row of data.

    Firstly I tried to use the formula, which you can see in AA1:AA5, however it didn't work for row 6, as the formula displayed not the decline but the growth of data (difference of C6 and D6), however, the highest decline is 19 (Q6 and R0). Hence, I changed the formula and got the correct result. Afterward, I faced another difficulty, the formula which calculated the difference (decline) in percents stopped working, you can see this in AB6. I've tried to use your formula in AC column.

    As a result, I need to get:
    1. Maximum difference between two consecutive cells in a row (decline, like I get in AA6 cell)
    2. This difference in percentage.


    One more point. I'm working in SpreadSheets, after I downloaded my document as .xsls all the formulas stopped working for some cells.
    Attached Files Attached Files
    Last edited by korshun; 07-05-2017 at 05:35 AM.

  14. #14
    Forum Contributor
    Join Date
    03-22-2017
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Max difference between two consecutive cells in percent

    Hi Korshun,

    If the amount of different is same, how which different should be show?

  15. #15
    Registered User
    Join Date
    07-04-2017
    Location
    Kharkiv
    MS-Off Ver
    Excel2016
    Posts
    9

    Re: Max difference between two consecutive cells in percent

    Not sure I've got your question.
    Firstly, I used such formula =MAX(ABS(B1:G1-A1:F1)) to display the max difference between 2 consecutive cell in a row.
    However, I need to get the maximum decline, thus the formula was changed to =MIN(B1:G1-A1:F1) and I got what I wanted. But now I need to get this value in percent.

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Max difference between two consecutive cells in percent

    Been away for a while. is this it? Array entered in AA1:

    =ABS(MIN(B1:X1-A1:W1))

    Use existing formula in AB1

  17. #17
    Registered User
    Join Date
    07-04-2017
    Location
    Kharkiv
    MS-Off Ver
    Excel2016
    Posts
    9

    Re: Max difference between two consecutive cells in percent

    Thank you! That was quite simple
    Now everything works perfectly.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Simple computation to record the difference between consecutive cells over a range
    By Jayyunit in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-13-2016, 12:27 AM
  2. Display percent difference for subtotal only.
    By brent_milne in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-27-2015, 12:28 PM
  3. Macro to calculate the percent difference after entering an input
    By hydronicengr in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-19-2012, 08:33 PM
  4. [SOLVED] Displaying percent difference between 2 cells
    By shanuck in forum Excel General
    Replies: 2
    Last Post: 03-28-2012, 11:14 AM
  5. Difference between consecutive number
    By loudwallace in forum Excel General
    Replies: 6
    Last Post: 08-18-2010, 04:07 PM
  6. Conditional Formatting Based on Percent Difference
    By dontcare in forum Excel General
    Replies: 7
    Last Post: 06-25-2010, 12:17 AM
  7. Conditional Formatting Based on Percent Difference
    By dontcare in forum Excel General
    Replies: 1
    Last Post: 06-18-2010, 05:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1