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
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,389
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,389
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,081,691
Messages
5,360,631
Members
400,592
Latest member
badgergurl

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top