Selective addition

[Guest]

I sure someone can help out with an elegant solution to my problem...

I am constructing a spreadsheet to help monitor a small business. It is
planned to be a combination of a budget spreadsheet and a reporting
spreadsheet. I have two columns for each month one titled Budget and the
other Actual. So I have columns Jan Budget, Jan Actual, Feb Budget, Feb
Actual etc. And each row is a budget item, say staff costs, material cost,
rent etc. etc. At the start of the year there will be no actuals filled in
but as the year progresses actuals will be entered.

My problem is that when I sum either vertically for a month or horizontally
for a budget item I would like to use the Actual figure if there is one and
the budget figure if not.

I can do it if I set up a helper matrix of zeros and ones with a very clumsy
set of logical tests or if I use a whole string of horrible "IF" statements
but I am sure this is not the best approach. Surely there must be a formula
(using SumProduct???) that could help me out.

I hope I have explained my problem clearly enough. Any pointers would be
greatly appreciated. Thanks in advance and apologies for such a long post.

Peter

 

[Guest]

I can think of some possibilities:

1) Write a macro/vba for each kind of summation you want to do.
2) Use a combination of SumIF, IF, Offset and SumProduct.

The two above requires some effort and not a lot of flexibility, methinks.

3) In my opinion, the easiest way would just to be set up a new third column
for each month. In that third column, do a simple If statement for every
entry and drag it down. Then do your sums horizontally or vertically on the
third column values instead of the Actual or Budget values.

A simple macro could be written to automate the creation of the third columns.

 

[Roger Govier]

Hi Peter

Assuming your data runs from B2:Y2
I used cell A1 to hold the Month number from which you want to use
Budget figures instead of Actual.
You can use any other cell you like to hold this value.

To get the total Actual
=SUMPRODUCT(--(MOD(COLUMN(B2:INDEX(B2:Y2,,($A$1-1)*2)),2)=0)
*B2:INDEX(B2:Y2,,($A$1-1)*2))

To get the Total Budget from a given Month onward
=SUMPRODUCT(--(MOD(COLUMN(INDEX(B2:Y2,,$A$1*2):Y2),2)=1)
*INDEX(B2:Y2,,$A$1*2))

When you enter a 1 in cell A1, the second formula will be all of the
budget figures for the whole year.
At this point there would be no actual figures, so the first formula
would return 0.
As soon as you begin to enter Actuals for January, then A1 would need to
be set to 2, so that Budget figures are only picked up from February
onward

Rather than combining these to a total cell, I think I would be inclined
to have 4 columns at the end of my 24 columns of data

Total Actual with formula 1
Remaining Budget with formula 2
Forecast being the sum of the two previous columns
Total Budget =SUMPRODUCT(--(MOD(COLUMN(B2:Y2),1)=1)*B2:Y2)

..