+ Reply to Thread
Results 1 to 4 of 4

Seems like it should be simple? (need formula to adjust based on previous months input)

  1. #1
    Registered User
    Join Date
    06-29-2017
    Location
    USA
    MS-Off Ver
    Office 2016
    Posts
    76

    Seems like it should be simple? (need formula to adjust based on previous months input)

    Hi!

    Please see the attached spreadsheet.

    But basically I have 12 months, and have 6000 to spread across those 12 months, as a sort of forecast.

    But then I want to put in actuals each month as static values. I then want any remaining future months to calculate the spread of that 6000 but taking into account the actuals I've put in.

    So in my example I put in 600 for Jan, and 100 for Feb. So then each remaining month should be calculated as 530.

    Any help for a formula on this?

    Thanks!

    (I guess I'm asking: is there a way to do it without a helper row)?
    Attached Files Attached Files
    Last edited by Katie620; 11-15-2018 at 03:15 PM.

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Seems like it should be simple? (need formula to adjust based on previous months inpu

    Leave A3 as is & use this in B3 & fill right
    =($O$2-SUM($A$3:A$3))/(12-COUNT($A$3:A$3))

  3. #3
    Registered User
    Join Date
    06-29-2017
    Location
    USA
    MS-Off Ver
    Office 2016
    Posts
    76

    Re: Seems like it should be simple? (need formula to adjust based on previous months inpu

    Thank you that works perfectly, but (and this might be kind of silly) what about at the start of the year when each month is a forecast? That formula won't work in January.

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Seems like it should be simple? (need formula to adjust based on previous months inpu

    You need to leave the Jan cell with your original formula.
    With my solution starting in Feb

+ 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] Offset, Match adjust previous formula
    By quaney in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-15-2017, 12:27 PM
  2. Auto-adjust stock levels based on drop-down input
    By generalxmayhem in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2016, 03:27 PM
  3. Auto-adjust stock levels based on drop-down input
    By generalxmayhem in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-25-2016, 12:09 PM
  4. [SOLVED] Need a formula to sum a rolling three months based on input date
    By dc0822 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-10-2015, 06:11 PM
  5. Formula to adjust a weekend date to the previous Friday...
    By joencrystal in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-21-2013, 05:25 PM
  6. How to count months (Previous & Next) based on particular date
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-28-2011, 01:17 PM
  7. Macro to adjust graphs based on input
    By geotr in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-10-2010, 03:31 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