+ Reply to Thread
Results 1 to 9 of 9

Help with forecasting

  1. 04-12-2024, 04:42 AM #1
    varbergt
    varbergt is offline
    Registered User
    Join Date
    12-22-2023
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    60

    Help with forecasting

    Hello,

    I'm making a forecast with 3 months rolling (Column F) using the Costs (Column E).
    When the month has ended the actual cost is being moved to column F and then it forecasts for the remaining months of the year.

    However, I realized this is not working correctly.
    In March the actual cost is 0. Hence, 0 should be in cell F6.

    But the way the formula is working in Column F is based on the 0. So if 0 then take average. This is working as long as there are costs but this is not always the case.

    Is there a way to calculate this differently?


    1.png

    Thanks!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-12-2024, 04:47 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,867

    Re: Help with forecasting

    But 0 is not the 3-month rolling cost ...
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 04-12-2024, 04:50 AM #3
    varbergt
    varbergt is offline
    Registered User
    Join Date
    12-22-2023
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    60

    Re: Help with forecasting

    You are right. But I want to combine actual with forecast.
    So Jan, Feb, and Mar have all been finalized. Hence, I would like to use the actual values from these months. Then I would like to forecast for the remaining months of the year.
    Last edited by varbergt; 04-12-2024 at 04:52 AM.
    Reply With QuoteRegister To Reply
  4. 04-12-2024, 04:51 AM #4
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,867

    Re: Help with forecasting

    Sorry - I am not following you at all. There's no consistency if you want 0 in that cell - either it's a rolling average or it isn't (it can't be both).
    Reply With QuoteRegister To Reply
  5. 04-12-2024, 04:56 AM #5
    varbergt
    varbergt is offline
    Registered User
    Join Date
    12-22-2023
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    60

    Re: Help with forecasting

    Sorry if i'm not clear.
    Now we are in April. Therefore, it does not make sense to use a forecasted number for Jan, Feb, and Mar (because we have the actual numbers from these months). But still I would like to forecast for the months where I don't have any actual numbers. So April to December.
    But once I am in April I don't want the forecasted number but the actual number.
    Reply With QuoteRegister To Reply
  6. 04-12-2024, 05:03 AM #6
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,867

    Re: Help with forecasting

    Oh, I see!

    This?

    =IF(EOMONTH(TODAY(),-1)>=D4,E4,AVERAGEIFS(F1:F3,B1:B3,B4))
    Reply With QuoteRegister To Reply
  7. 04-12-2024, 05:08 AM #7
    varbergt
    varbergt is offline
    Registered User
    Join Date
    12-22-2023
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    60

    Re: Help with forecasting

    OMG!
    What is this thing doing. I mean it works! Thx.

    It looks at today's date and if that is > than Column D then it takes the average?
    Reply With QuoteRegister To Reply
  8. 04-12-2024, 05:09 AM #8
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,867

    Re: Help with forecasting

    Almost - the end of the month before today.

    Glad to have helped.

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

    Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
    Reply With QuoteRegister To Reply
  9. 04-12-2024, 05:39 AM #9
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,867

    Re: Help with forecasting

    You haven't signed off this thread - please do so (see post #8). Thanks.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Forecasting
    By BremmesN in forum Excel General
    Replies: 0
    Last Post: 09-25-2017, 05:39 AM
  2. [SOLVED] Forecasting
    By BremmesN in forum Excel General
    Replies: 1
    Last Post: 09-25-2017, 03:45 AM
  3. Forecasting
    By rs1aj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-24-2014, 02:27 PM
  4. Please help with forecasting
    By danstar25 in forum Excel General
    Replies: 0
    Last Post: 08-26-2009, 01:45 PM
  5. Forecasting
    By JR573PUTT in forum Excel General
    Replies: 6
    Last Post: 11-10-2006, 06:35 PM
  6. Forecasting
    By november678x in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-10-2006, 05:30 PM
  7. [SOLVED] Forecasting
    By KrunoG in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-01-2006, 04:45 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1