+ Reply to Thread
Results 1 to 6 of 6

Convert Variance to a Percentage Using SUMPRODUCT

  1. #1
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Convert Variance to a Percentage Using SUMPRODUCT

    nflsales kindly came up with a formula to calculate the difference between two sets of columns:

    Please Login or Register  to view this content.
    I now need to convert this to a percentage. Normally this would simply be

    Please Login or Register  to view this content.
    for example if '$BK2:$BW2' = 40 and '$AF2:$AR2' = 10, the variance would be (10-40)/40 = 75%

    so I tried

    Please Login or Register  to view this content.
    but that doesn't work

    Does anyone know how to to this?

    Many thanks

  2. #2
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Convert Variance to a Percentage Using SUMPRODUCT

    Can you send me a single file?
    Regards

  3. #3
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Convert Variance to a Percentage Using SUMPRODUCT

    Hi Josť

    File attached. In place in column AA is a SUMPRODUCT formula that calculates the variance between the current month YTD (determined by there being no entries beyond the current month) and the Forecast YTD, so in the attached example the current month is month 3 because there are no entires in month 4 (column Q). Note however there can be zero entries in the current month (see Month 2 and 3, columns O and P).

    The challenge is how to adapt the SUPPRODUCT formula in column AA to produce the variance as a percentage in column AB. The expected answers appear in column AD.

    Many thanks
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Convert Variance to a Percentage Using SUMPRODUCT

    I should have added that if there is an alternative to using SUMPRODUCT to achieve the same thing, that is also good.

  5. #5
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Convert Variance to a Percentage Using SUMPRODUCT

    Yes. Replace SUMPRODUCT by SUM.

  6. #6
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Convert Variance to a Percentage Using SUMPRODUCT

    Hi Josť,

    THanks for your help, sadly that doesn't work! I'm going to repost and see if anyone else can chip in...

+ 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. Percentage Variance Graphs
    By Bobo6782 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 03-04-2014, 07:00 AM
  2. How do I obtain a variance percentage? Please check my calculation/formula
    By Dixiewest in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-12-2013, 02:58 PM
  3. [SOLVED] Find the date of a variance amount over a limit and count the days since the variance.
    By avidcat in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-21-2013, 12:00 PM
  4. Replies: 1
    Last Post: 04-16-2013, 03:58 PM
  5. Conditional Formatting using Percentage Variance
    By Skarnald in forum Excel General
    Replies: 3
    Last Post: 02-09-2012, 11:03 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