Results 1 to 8 of 8

Thread: I need help with projections
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2014
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question I need help with projections

    Hello, I am totaling a value for each month of the year as and I need something to give me a projected year end value based on the numbers I enter for each month. Why doesn't hitting enter take me to a new line? Seems dumb. Anyway, for example, for January I have 42, February is 31 and March (so far) is at 26. Since we're about halfway through March that means we're about 2.5 months into the year. I need a formula which calculates how far into the year we are and then projects a year end total based on the numbers so far and how far we are into the year. Thanks for any help

  2. #2
    Board Regular etaf's Avatar
    Join Date
    Oct 2012
    Location
    UK, West Sussex
    Posts
    3,379
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: I need help with projections

    what happens when you hit enter - you can setup for enter to move to next cell to the right or down

    would in your example , the following work

    sum the values in the months entered - so sum(range for Jan-dec)
    then divide by the number of days we have had so far ?
    and then multiply by 365

    =TODAY()-DATEVALUE("1/1/2014")
    that would for today() which = 18th March 2014 = 76

    now we can divide the sum so far by 76
    =SUM(Range of forcast)/TODAY()-DATEVALUE("1/1/2014")
    which would be (42+31+26 ) = 99

    99/76 = 1.303 (rounded to 3 decimals)

    now multiply by 365

    1.303 * 365
    =
    475.6
    Wayne

    Using Excel Version 365 for Mac

  3. #3
    New Member
    Join Date
    Mar 2014
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I need help with projections

    Quote Originally Posted by etaf View Post
    what happens when you hit enter - you can setup for enter to move to next cell to the right or down

    would in your example , the following work

    sum the values in the months entered - so sum(range for Jan-dec)
    then divide by the number of days we have had so far ?
    and then multiply by 365

    =TODAY()-DATEVALUE("1/1/2014")
    that would for today() which = 18th March 2014 = 76

    now we can divide the sum so far by 76
    =SUM(Range of forcast)/TODAY()-DATEVALUE("1/1/2014")
    which would be (42+31+26 ) = 99

    99/76 = 1.303 (rounded to 3 decimals)

    now multiply by 365

    1.303 * 365
    =
    475.6
    Isnt there an equation I can enter in the last cell in the row of months that can do all of this? And when I hit enter here, nothing happens. I don't know how to go down to the next line

  4. #4
    Board Regular
    Join Date
    Feb 2013
    Location
    USA
    Posts
    1,161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I need help with projections

    Are you entering 1 number for each month and updating that one number or are you entering a new number every day?
    -Red


    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  5. #5
    New Member
    Join Date
    Mar 2014
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I need help with projections

    Quote Originally Posted by Redwolfx View Post
    Are you entering 1 number for each month and updating that one number or are you entering a new number every day?
    I am entering a number for each month but the number keeps changing as new data comes in. We're tracking the number of customer complaints and trying to forecast a total for the year based on YTD totals

  6. #6
    Board Regular
    Join Date
    Feb 2013
    Location
    USA
    Posts
    1,161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I need help with projections

    So is it safe to assume at the end of the year you will have 12 numbers?

    If your information was in Row 2 you would use

    =SUM(B2:M2)/(TODAY()-DATE(YEAR(TODAY()),1,1))*365
    -Red


    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  7. #7
    New Member
    Join Date
    Mar 2014
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: I need help with projections

    Quote Originally Posted by Redwolfx View Post
    So is it safe to assume at the end of the year you will have 12 numbers?

    If your information was in Row 2 you would use

    =SUM(B2:M2)/(TODAY()-DATE(YEAR(TODAY()),1,1))*365
    Thank you!

  8. #8
    Board Regular etaf's Avatar
    Join Date
    Oct 2012
    Location
    UK, West Sussex
    Posts
    3,379
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: I need help with projections

    Isnt there an equation I can enter in the last cell in the row of months that can do all of this? And when I hit enter here, nothing happens. I don't know how to go down to the next line
    yes, you can do that , and it appears Today, 09:00 PM
    Redwolfx
    has already provided details
    Today, 09:00 PM
    Wayne

    Using Excel Version 365 for Mac

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •