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

1. ## 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  Reply With Quote

2. ## 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  Reply With Quote

3. ## Re: I need help with projections Originally Posted by etaf 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  Reply With Quote

4. ## 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?  Reply With Quote

5. ## Re: I need help with projections Originally Posted by Redwolfx 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  Reply With Quote

6. ## 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  Reply With Quote

7. ## Re: I need help with projections Originally Posted by Redwolfx 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!  Reply With Quote

8. ## 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
Today, 09:00 PM  Reply With Quote

## User Tag List

date, excel, projections 