Will "IF THEN" Formula accomplish this for me?

  • Thread starter Thread starter Pravda
  • Start date Start date
P

Pravda

Hello,

In my worksheet below, I display budget amounts for each month of the year
(the "Forecast" columns) which change to Actuals (the "Actual" columns) once
a month has ended and the financials are completed. My "Full Year Forecast"
column (the first column) needs to add Actual amounts for the months that
have closed and Forecast amounts for the months with no Actuals yet. For
example, if I have Actuals through March, my "Full Year Forecast" column
should be adding Actuals for January, February, and March but Forecast
amounts for April through December. Of course, as I move through the year
each month my "Forecast" column will be manually zeroed out and replaced
with an "Actual" amount.

Can I create a formula which will know to add either the "Forecast" or
"Actual" amount for each given month, depending on which cell is populated?
I would like to create a formula that I do not have to manually tweak each
month as "Forecast" turns to "Actual".

"Full Year Forecast" column is F4. January "Forecast" and "Actual" columns
are I4 and J4 respectively. February is M4 and N4 respectively. March is
Q4 and R4 respectively. This pattern continues from April through December.

Any help is very much appreciated. Thanks!


Full Year Forecast (Actuals plus Forecast) Full Year Variance Full
Year Variance % January February March
Forecast Actual Variance Var. % Forecast Actual Variance Var. %
Forecast Actual Variance Var. %

0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!

0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
 
Hello,

In my worksheet below, I display budget amounts for each month of the year
(the "Forecast" columns) which change to Actuals (the "Actual" columns) once
a month has ended and the financials are completed.  My "Full Year Forecast"
column (the first column) needs to add Actual amounts for the months that
have closed and Forecast amounts for the months with no Actuals yet.  For
example, if I have Actuals through March, my "Full Year Forecast" column
should be adding Actuals for January, February, and March but Forecast
amounts for April through December.  Of course, as I move through the year
each month my "Forecast" column will be manually zeroed out and replaced
with an "Actual" amount.

Can I create a formula which will know to add either the "Forecast" or
"Actual" amount for each given month, depending on which cell is populated?
I would like to create a formula that I do not have to manually tweak each
month as "Forecast" turns to "Actual".

"Full Year Forecast" column is F4.  January "Forecast" and "Actual" columns
are I4 and J4 respectively.  February is M4 and N4 respectively.  March is
Q4 and R4 respectively.  This pattern continues from April through December.

Any help is very much appreciated.  Thanks!

      Full Year Forecast (Actuals plus Forecast) Full Year VarianceFull
Year Variance % January February March
            Forecast Actual Variance Var. % Forecast Actual Variance Var. %
Forecast Actual Variance Var. %

      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!
      0  0  #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0! 0 0 0 #DIV/0!

      0  0  #DIV/0! 0  0  0  #DIV/0! 0  0  0  #DIV/0! 0  0  0  #DIV/0!
Click to expand...

Hi,

The formula below assumes you have the column headings "Forecast" and
"Actual" for each of the months and a cell (B1) which holds the date
you wish to sum to.


=SUMIF(OFFSET(I3,0,0,1,(MONTH(B1))*4),"Actual",OFFSET(I4,0,0,1,(MONTH
(B1))*4))+SUMIF(OFFSET(I3,0,(MONTH(B1))*4,1,45),"Forecast",OFFSET(I4,0,
(MONTH(B1))*4,1,45))

hth

Regards

David
 
Hi,

The formula below assumes you have the column headings "Forecast" and
"Actual" for each of the months and a cell (B1) which holds the date
you wish to sum to.


=SUMIF(OFFSET(I3,0,0,1,(MONTH(B1))*4),"Actual",OFFSET(I4,0,0,1,(MONTH
(B1))*4))+SUMIF(OFFSET(I3,0,(MONTH(B1))*4,1,45),"Forecast",OFFSET(I4,0,
(MONTH(B1))*4,1,45))


David,

So if I understand you correctly, I would need to add a cell (B1 in your
example), which would contain the date (month?) I wish to sum to? For
example, put April in cell B1 if I wish to sum Actuals through April and
Forecast from May through December? Keep in mind I always need to be adding
12 months, the only difference being I will be adding one more month of
Actuals and one less month of Forecast each month.
 
Hi,

The formula below assumes you have the column headings "Forecast" and
"Actual" for each of the months and a cell (B1) which holds the date
you wish to sum to.

=SUMIF(OFFSET(I3,0,0,1,(MONTH(B1))*4),"Actual",OFFSET(I4,0,0,1,(MONTH
(B1))*4))+SUMIF(OFFSET(I3,0,(MONTH(B1))*4,1,45),"Forecast",OFFSET(I4,0,
(MONTH(B1))*4,1,45))

David,

So if I understand you correctly, I would need to add a cell (B1 in your
example), which would contain the date (month?) I wish to sum to?  For
example, put April in cell B1 if I wish to sum Actuals through April and
Forecast from May through December?  Keep in mind I always need to be adding
12 months, the only difference being I will be adding one more month of
Actuals and one less month of Forecast each month.
Click to expand...

Thats correct, except you put in "01/04/09", instead of just April.

The formula takes the month number and calculates the actual up to
that month and the forecast from the next month

You could replace B1 with NOW() if you want and that will
automatically calculate the actuals for the month you are in and
forecast for future months and negate the need for the B1 reference,
but having the date in B1 gives you a little more flexibility.

Regards

David
 
Back
Top