Trying to figure out what formula to use

[drewszone32]

I am trying to figure out YTD by month


Here is an example of spreadsheet remembering there are more columns with the rest of the months in it and a column for total and another for YTD at end.


In total column shows formula =SUM(C7:N7) right now equaling 1,308,499


I am trying to right a formula to find YTD % by month


So far I have =(O7-C10-D10)/O7 O7 is 1,309,499 - C10 is 650,640 - D10 is 585,007


Receipts January February March

Actual 2013 643,209 665,290

Projected 605,891 731,021 497,902

Variance % 6.2% -9.0%

2012 Actual 650,640 585,007 612,614


I would like to be able to have a running count in last YTD column so I don't have to calculate everytime I add an amount in the month actual 2013 field.


Can you help me?


Thank you

 

[DaveTurton]

=SUM((O7-SUM(C10,OFFSET(B10,,B6)))/O7)

Where B10 contains the month number (i.e. January=1, February=2). All you have to do is change the value in B10 to get the figure to the month of your choice

 

[DaveTurton]

Apologies used a , instead of :


this will work:

=SUM((O7-SUM(C10:OFFSET(B10,,B6)))/O7)

 

[DaveTurton]

if you are always going to be looking at the month you are currently in you could put

=MONTH(TODAY())

in B6 and that will automatically show for example 3 as we are in March

 

[DaveTurton]

And if it is the case you always want to look at data upto and including the current month you could just incorporate the

=month(today()) into your formula:


[code]=SUM((O7-SUM(C10:OFFSET(B10,,MONTH(TODAY()))))/O7)

For up to current month


=SUM((O7-SUM(C10:OFFSET(B10,,MONTH(TODAY())-1)))/O7)[/code] For up to previous month

 

[drewszone32]

I guess I am trying to run current. So taken O7 and subtracting C10 (January amount) subtracting E10 ( February amount) then when I have March's amount it subtracts that and so fourth so on.


I hope I am making since.


Thank you in advance for your help.

 

[DaveTurton]

I presume you won't know March's amount until April in which case the below will suffice for your needs

=SUM((O7-SUM(C10:OFFSET(B10,,MONTH(TODAY())-1)))/O7)