+ Reply to Thread
Results 1 to 6 of 6

Formula so annual revenues change based on number of weeks earlier it can be realized

  1. #1
    Forum Contributor
    Join Date
    07-22-2015
    Location
    San Francisco
    MS-Off Ver
    Mac 2011 Version 14.5.2
    Posts
    107

    Formula so annual revenues change based on number of weeks earlier it can be realized

    Hello,

    I have attached a spreadsheet that has forecasted monthly revenues out to six years. I have then summed each year to give annual revenues.

    I would like to write a formula that shows how the annual revenues will change (go up in this case) if the monthly revenue is realized earlier e.g 5 weeks earlier (but I would like to be able to change this number to reflect different scenarios.

    Does anyone have any idea how to do this?

    Any help would be greatly appreciated.

    Many thanks!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Formula so annual revenues change based on number of weeks earlier it can be realized

    This proposed solution assumes the average 52 weeks in a year. Try this formula in c12 and see if it yields the expected result:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Contributor
    Join Date
    07-22-2015
    Location
    San Francisco
    MS-Off Ver
    Mac 2011 Version 14.5.2
    Posts
    107

    Re: Formula so annual revenues change based on number of weeks earlier it can be realized

    Thank you for your reply.

    What I am looking to do is pull all the revenue earlier by a specific time period. For example if I pulled it earlier by 4 weeks (say 1 month), then the revenue would be summing Feb 2017 through Jan 2018. Therefore, if there is a particularly high surge in revenue for a particular month, then this would be captured.

    Is it possible to do it so that it pulls the data from the specific cells instead of using averages? I could use months instead of weeks if that makes it more feasible and then just put a fraction of a month in the variable cell.

    Many thanks,
    Adam

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Formula so annual revenues change based on number of weeks earlier it can be realized

    This formula is written based on what you said you wanted it to do. If the value in b9 represents months and is one then the formula will sum the values from February of the year listed in row 11 through January of the following year. Likewise if the value of b9 is two the formula sums March through February etc.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let us know if you have any questions.

  5. #5
    Forum Contributor
    Join Date
    07-22-2015
    Location
    San Francisco
    MS-Off Ver
    Mac 2011 Version 14.5.2
    Posts
    107

    Re: Formula so annual revenues change based on number of weeks earlier it can be realized

    That works perfectly! Thank you so much for your help.

    Have a great weekend.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Formula so annual revenues change based on number of weeks earlier it can be realized

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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] Add 12 weeks to date to return week number turn over at 52 weeks
    By nigelog in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-01-2017, 05:58 AM
  2. If condition - Change the number to Weeks
    By arun.sj in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-23-2014, 11:00 PM
  3. Return weekending in advance based on number of weeks
    By tantcu in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-17-2014, 12:51 PM
  4. number of days based of number of weeks
    By trainingday in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-18-2012, 03:28 PM
  5. Replies: 4
    Last Post: 10-15-2012, 07:38 AM
  6. Replies: 1
    Last Post: 10-05-2011, 03:40 PM
  7. Weekly growth forecast based on annual number
    By bbbmiller in forum Excel General
    Replies: 2
    Last Post: 03-03-2010, 05:35 PM

Tags for this Thread

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