I need help with projections

worthm

New Member
Joined
Mar 18, 2014
Messages
37
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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,564
Office Version
365
Platform
MacOS
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
 

worthm

New Member
Joined
Mar 18, 2014
Messages
37
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
 

Redwolfx

Well-known Member
Joined
Feb 22, 2013
Messages
1,161
Are you entering 1 number for each month and updating that one number or are you entering a new number every day?
 

worthm

New Member
Joined
Mar 18, 2014
Messages
37
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
 

Redwolfx

Well-known Member
Joined
Feb 22, 2013
Messages
1,161
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
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,564
Office Version
365
Platform
MacOS
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
#6
Redwolfx
has already provided details
Today, 09:00 PM
#
 

Forum statistics

Threads
1,089,557
Messages
5,408,947
Members
403,245
Latest member
Nanda Kishore

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top