+ Reply to Thread
Results 1 to 5 of 5

Function for displaying percentage difference either negative or positive

  1. #1
    Registered User
    Join Date
    06-09-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Wink Function for displaying percentage difference either negative or positive

    Hello,
    Sorry this is probably pretty simple but I couldn't find the answer anywhere!

    I want to display the percentage difference of 2 columns whether they are positive, negative or whether A>B and vice versa.

    Example:

    A B C
    1 1 1 ?%
    2 -1 1 ?%
    3 3 2 ?%

    I was using this but obviously I just want one formula.

    =(B1-A1)/B1
    =(A1-B1)/A1

    I was also using this but it seems longwinded:
    =IF(AND(ISNUMBER(A1),ISNUMBER(B1)), ((B1-A1)/A1)*100, 0)

    Thanks!
    Last edited by possemiss; 06-09-2010 at 09:12 AM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,601

    Re: Function for displaying percentage difference either negative or positive

    =abs((abs(a1)-abs(b1))/abs(a1))

  3. #3
    Forum Contributor day92's Avatar
    Join Date
    04-20-2010
    Location
    Los Angeles
    MS-Off Ver
    Excel 360
    Posts
    600

    Re: Function for displaying percentage difference either negative or positive

    try this..

    =(B1-A1)/A1

  4. #4
    Registered User
    Join Date
    06-09-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Function for displaying percentage difference either negative or positive

    Thank you two but that just gives me the percent total i.e 92% when I actually want 0.8% displayed.

    Plus one of those was +ve and one was -ve...

  5. #5
    Registered User
    Join Date
    06-09-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Function for displaying percentage difference either negative or positive

    Damn I just realised I have 2 sheets and want the comparison on sheet 3.
    E.g.

    Sheet 1 A1
    Sheet 2 A1
    (Compare)
    % Diff displayed on Sheet 3 A1

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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