+ Reply to Thread
Results 1 to 7 of 7

Formula to Calculate Ratios

  1. #1
    Registered User
    Join Date
    03-21-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    58

    Formula to Calculate Ratios

    Hi everyone,

    I am fairly new to spreadsheet and need to develope a simple spreadsheet that will present a actual vs budget % for certain clients on a weekly basis.

    It is currently taking me forever to remap my formula each week when i have populated my actual vs budget data.

    On Sheet 1 it has my data and on Sheet 2 is where my ratios are presented with graphs.

    On Sheet 2, the ratio is presented in one cell with a formula that calculates based on two cells adjacent to each other on Sheet 1.

    I am currently running my report horizontally across the spreadsheet.

    The problem is that when i add in another week running horizontally across my spread, the formula can not be dragged across as it does not jump by two cells.

    I have attached a example of what i mean just incase i have confused everyone.

    If anyone have any suggestion to make things simplier and time saving it would be greatly greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Formula to Calculate Ratios

    In C3 Cell

    =IFERROR((INDEX(Sheet1!$C$4:$J$9,MATCH($B3,Sheet1!$B$4:$B$9,0),MATCH(C$2,Sheet1!$C$3:$J$3,0))-INDEX(Sheet1!$C$4:$J$9,MATCH($B3,Sheet1!$B$4:$B$9,0),MATCH(C$2,Sheet1!$C$3:$J$3,0)+1))/INDEX(Sheet1!$C$4:$J$9,MATCH($B3,Sheet1!$B$4:$B$9,0),MATCH(C$2,Sheet1!$C$3:$J$3,0)),"")

    Drag it down and right...


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Formula to Calculate Ratios

    =(INDEX(Sheet1!$C$4:$R$9,ROWS(M$1:M1),COLUMNS($A$1:A1)*2-1)-INDEX(Sheet1!$D$4:$R$9,ROWS(M$1:M1),COLUMNS($A$1:A1)*2-1))/INDEX(Sheet1!$C$4:$R$9,ROWS(M$1:M1),COLUMNS($A$1:A1)*2-1)
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Formula to Calculate Ratios

    Hi,

    If you set your data up as a table (see my example) and then play with a Pivot Table and Pivot Chart by dragging around the different column heads, you might get exactly what you want. See the example. As new dates and numbers are added, simply expand the pivot table and chart ranges.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Registered User
    Join Date
    03-21-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    58

    Re: Formula to Calculate Ratios

    Thank you kindly gents!!!

    Kind regards sixthsense as the formula is perfect for my report.

    Thanks Marvin for the pivot table demonstration but unfortunately my sheet 1 is my historical data and needs to run horizontally and not down the page for easy comparisons between the weeks for each client.

  6. #6
    Registered User
    Join Date
    03-21-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    58

    Re: Formula to Calculate Ratios

    Hi Sixthsense, how difficult will it be to add into the formula if there is another dimension to the clients?

    For example client A exists in numerous states?
    Attached Files Attached Files

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Formula to Calculate Ratios

    =(Sheet1!C4-Sheet1!D4)/Sheet1!C4

    Formula for Sheet1!C4

    =SUMPRODUCT((Sheet1!$A$4:$A$9=$A3)*(Sheet1!$B$4:$B$9=$B3)*(Sheet1!$C$1:$J$1=C$2)*(TRIM(Sheet1!$C$2:$J$2)="Budget"),Sheet1!$C$4:$J$9)


    Formula for Sheet1!D4

    =SUMPRODUCT((Sheet1!$A$4:$A$9=$A3)*(Sheet1!$B$4:$B$9=$B3)*(Sheet1!$C$1:$J$1=C$2)*(TRIM(Sheet1!$C$2:$J$2)="Actual"),Sheet1!$C$4:$J$9)

    Formula for =(Sheet1!C4-Sheet1!D4)

    =(SUMPRODUCT((Sheet1!$A$4:$A$9=$A3)*(Sheet1!$B$4:$B$9=$B3)*(Sheet1!$C$1:$J$1=C$2)*(TRIM(Sheet1!$C$2:$J$2)="Budget"),Sheet1!$C$4:$J$9)-SUMPRODUCT((Sheet1!$A$4:$A$9=$A3)*(Sheet1!$B$4:$B$9=$B3)*(Sheet1!$C$1:$J$1=C$2)*(TRIM(Sheet1!$C$2:$J$2)="Actual"),Sheet1!$C$4:$J$9))

    Mingling all the above formula's to this equation =(Sheet1!C4-Sheet1!D4)/Sheet1!C4

    In C3 Cell of Sheet2

    =(SUMPRODUCT((Sheet1!$A$4:$A$9=$A3)*(Sheet1!$B$4:$B$9=$B3)*(Sheet1!$C$1:$J$1=C$2)*(TRIM(Sheet1!$C$2:$J$2)="Budget"),Sheet1!$C$4:$J$9)-SUMPRODUCT((Sheet1!$A$4:$A$9=$A3)*(Sheet1!$B$4:$B$9=$B3)*(Sheet1!$C$1:$J$1=C$2)*(TRIM(Sheet1!$C$2:$J$2)="Actual"),Sheet1!$C$4:$J$9))/SUMPRODUCT((Sheet1!$A$4:$A$9=$A3)*(Sheet1!$B$4:$B$9=$B3)*(Sheet1!$C$1:$J$1=C$2)*(TRIM(Sheet1!$C$2:$J$2)="Budget"),Sheet1!$C$4:$J$9)

    Drag it down and right....

    Refer the attached file for details
    Attached Files Attached Files
    Last edited by :) Sixthsense :); 01-10-2014 at 02:00 AM. Reason: Added Attachment File

+ 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. [SOLVED] To calculate the average of the ratios between two columns
    By BNCOXUK in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-26-2013, 07:37 AM
  2. [SOLVED] How do i calculate standardised mortality ratios using Excel?
    By mlk in forum Excel General
    Replies: 1
    Last Post: 05-24-2006, 03:10 PM
  3. [SOLVED] Formula for Ratios
    By Dave Y in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  4. [SOLVED] Formula for Ratios
    By Dave Y in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  5. How do I calculate ratios between 2 cells?
    By Excel Novice in forum Excel General
    Replies: 2
    Last Post: 01-17-2005, 12:06 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