+ Reply to Thread
Results 1 to 6 of 6

Calculate weekly percentage each week

  1. #1
    Registered User
    Join Date
    12-28-2014
    Location
    Birmingham, England
    MS-Off Ver
    2010
    Posts
    54

    Calculate weekly percentage each week

    HI

    I have a table as per attached file and each week the last week's data is added in. So next week we will have the figures for Week 9. I need to calculate a weekly percentage, but I am looking to have this done using a formula.

    Currently, weekly percentage refers to Week 8. But when Week 9 data is added in, I need the weekly percentage to be based on week 9 figures.?

    can this be done?

    Thanks!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor PaulSP8's Avatar
    Join Date
    11-21-2017
    Location
    Chorley, England
    MS-Off Ver
    2013
    Posts
    393

    Re: Calculate weekly percentage each week

    If you put this formula in K6

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    It'll adjust itself as you add more columns in for your weeks.

    It'll throw an error when you add the new column until you give it some data, if that matters then you can wrap it in an IFERROR

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Does that work for you?

  3. #3
    Registered User
    Join Date
    12-28-2014
    Location
    Birmingham, England
    MS-Off Ver
    2010
    Posts
    54

    Re: Calculate weekly percentage each week

    Hi Paul
    Thanks for your reply. Its giving me a #DIV/0! error - is the formula referring K8 to divide by? K8 is blank so maybe that's why the error?

    I need it to divide I6/I8 in Week 8 and then J6/J8 in week 9 and so on.

    Thanks!

    ===================

    resolved: Thank you, it worked. I had to change the row reference in my actual sheet.
    Last edited by bdrod; 02-26-2019 at 09:21 AM.

  4. #4
    Valued Forum Contributor PaulSP8's Avatar
    Join Date
    11-21-2017
    Location
    Chorley, England
    MS-Off Ver
    2013
    Posts
    393

    Re: Calculate weekly percentage each week

    It'll give you an error until you give it something to divide

    I've attached a Workbook with it working for you, and wrapped it in the IFERROR

    I assume you'll add a week by inserting a column?

    Once you do this then the formula will return "" making it look blank till you plug some figures into it.

    Once you've added in the detail for Week 9 the percentage will return.
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor PaulSP8's Avatar
    Join Date
    11-21-2017
    Location
    Chorley, England
    MS-Off Ver
    2013
    Posts
    393

    Re: Calculate weekly percentage each week

    Quote Originally Posted by bdrod View Post
    resolved: Thank you, it worked. I had to change the row reference in my actual sheet.
    Ah, cool - No worries - Glad to help

  6. #6
    Registered User
    Join Date
    12-28-2014
    Location
    Birmingham, England
    MS-Off Ver
    2010
    Posts
    54

    Re: Calculate weekly percentage each week

    Hi Paul

    I have a question if you don't mind...

    I am trying to set this up so there is minimal work each week. So now I have created a pivot to shows Errors / No Errors by week and this pulls though to a Table on the Summary Page.

    The table is set up for all 52 weeks as per the attached file. So each time new data is added, pivtos refreshed, the table updates. But in this case, the OFFSET function does not work as the columns between the last week and weekly% will change).

    Any ideas how I can calculate this?

    Thanks in advance.
    Attached Files Attached Files

+ 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. Replies: 4
    Last Post: 03-31-2016, 05:50 AM
  2. [SOLVED] IF(OR statement to calculate percentage of hours for daily, weekly, or monthly tasks
    By DjJazzyJeff in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-21-2016, 12:39 PM
  3. Replies: 5
    Last Post: 01-11-2016, 11:15 PM
  4. How to get a percentage of completion on a daily basis, week by week
    By grackja in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-17-2015, 03:59 PM
  5. how to calculate weekly and monthly percentage returns from daily figures
    By bensittingdown in forum Excel Formulas & Functions
    Replies: 32
    Last Post: 10-15-2014, 05:02 PM
  6. [SOLVED] calculate weekly volume based on week number
    By karl8695 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-26-2014, 06:23 AM
  7. Replies: 4
    Last Post: 05-30-2006, 09:15 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