I've really got myself confused on how to figure this out and need
some assistance.
I'm working on a financial worksheet and have been getting away with
YTD budget formulas using a total figure and dividing it by the number
of months I'm reporting on. Except now they want to enter the month
they want to report on and it will sum up a number of columns.

In the budget.month worksheet there is a series of fields:
CCA, CCB, CCC, CCD, CCE, CCF, Period 1, Period 2, Period 3, Period 4,
Period 5, Period 6, Period 7, Period 8, Period 9, Period 10, Period 11
and Period 12.

The formula I'm using in the Report worksheet is currently.......

{=(SUM((O9B.CCB=VALUE(C7))*(O9B.CCD=VALUE(rep.fund ))*
(O9B.cat="Expense")*(O9B.Amounts)))/4}

O9B stands for 08/09 Budget
the rest is self explanatory.


The Value in C7 is the costcentre aka CCB

Thus what I want to do is extend on this ( {=(SUM((O9B.CCB=VALUE(C7))*
(O9B.CCD=VALUE(rep.fund))*(O9B.cat="Expense")* ) with something along
these lines.....

The reporting.month cell is where the user will enter which period:
EG: 6 for July to December. The formula will then sum the values in
the columns Period 1, 2, 3, 4, 5, 6

I'm not sure which way to do it without creating some mega formula
that goes along the lines of (IF(reporting.month=1,(Period 1),If
(reporting.month=2,(Period 1)*(Period 2),if(reporting.month=3,(Period
1)*(Period 2)*(Period 3)......... any ideas on how to tackle this
one..... I am stuck.

I did come up with a workaround.....

In the budget.month worksheet I inserted a formula in one of the
columns:

=SUM(H2:OFFSET(H2,,reporting.month-1))

What it does is it sums Period 1 to Period 12 depending on the number
of columns. The -1 is so that when I report on Period 1, it doesn't
sum Period 1 & Period 2 but Period 1 on its own.

That works :)

Try something like this....

Suppose your data is in the range A1:L1 (12 cells)

A5 = user enters a number from 1 to 12

=SUM(A1:INDEX(A1:L1,A5))

If A5 = 2 you get: SUM(A1:B1)
If A5 = 5 you get: SUM(A1:E1)
If A5 = 10 you get: SUM(A1:J1)

If A5 is empty you get the sum of the entire range.

--
Biff
Microsoft Excel MVP


"Forgone" wrote in message
...
I've really got myself confused on how to figure this out and need
some assistance.
I'm working on a financial worksheet and have been getting away with
YTD budget formulas using a total figure and dividing it by the number
of months I'm reporting on. Except now they want to enter the month
they want to report on and it will sum up a number of columns.

In the budget.month worksheet there is a series of fields:
CCA, CCB, CCC, CCD, CCE, CCF, Period 1, Period 2, Period 3, Period 4,
Period 5, Period 6, Period 7, Period 8, Period 9, Period 10, Period 11
and Period 12.

The formula I'm using in the Report worksheet is currently.......

{=(SUM((O9B.CCB=VALUE(C7))*(O9B.CCD=VALUE(rep.fund ))*
(O9B.cat="Expense")*(O9B.Amounts)))/4}

O9B stands for 08/09 Budget
the rest is self explanatory.


The Value in C7 is the costcentre aka CCB

Thus what I want to do is extend on this ( {=(SUM((O9B.CCB=VALUE(C7))*
(O9B.CCD=VALUE(rep.fund))*(O9B.cat="Expense")* ) with something along
these lines.....

The reporting.month cell is where the user will enter which period:
EG: 6 for July to December. The formula will then sum the values in
the columns Period 1, 2, 3, 4, 5, 6

I'm not sure which way to do it without creating some mega formula
that goes along the lines of (IF(reporting.month=1,(Period 1),If
(reporting.month=2,(Period 1)*(Period 2),if(reporting.month=3,(Period
1)*(Period 2)*(Period 3)......... any ideas on how to tackle this
one..... I am stuck.

Hi,

You might try something like this:

=SUM(OFFSET(A2,,,,A5))

In this case I am assuming the the first reporting period has it's value in
A2 and goes to the right for 12 (or more months - columns). And A5 contains
the period number that the user is entering.

As a side note - consider the formula you posted:

{=(SUM((O9B.CCB=VALUE(C7))*(O9B.CCD=VALUE(rep.fund ))*
(O9B.cat="Expense")*(O9B.Amounts)))/4}

1. There is not reason to use the VALUE function in this type of formula
99% of the time.
2. You can avoid the need for array entry by replacing the SUM with
SUMPRODUCT
3. There is not need to put () around the last argument (09B.Amounts)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Forgone" wrote:

I've really got myself confused on how to figure this out and need
some assistance.
I'm working on a financial worksheet and have been getting away with
YTD budget formulas using a total figure and dividing it by the number
of months I'm reporting on. Except now they want to enter the month
they want to report on and it will sum up a number of columns.

In the budget.month worksheet there is a series of fields:
CCA, CCB, CCC, CCD, CCE, CCF, Period 1, Period 2, Period 3, Period 4,
Period 5, Period 6, Period 7, Period 8, Period 9, Period 10, Period 11
and Period 12.

The formula I'm using in the Report worksheet is currently.......

{=(SUM((O9B.CCB=VALUE(C7))*(O9B.CCD=VALUE(rep.fund ))*
(O9B.cat="Expense")*(O9B.Amounts)))/4}

O9B stands for 08/09 Budget
the rest is self explanatory.


The Value in C7 is the costcentre aka CCB

Thus what I want to do is extend on this ( {=(SUM((O9B.CCB=VALUE(C7))*
(O9B.CCD=VALUE(rep.fund))*(O9B.cat="Expense")* ) with something along
these lines.....

The reporting.month cell is where the user will enter which period:
EG: 6 for July to December. The formula will then sum the values in
the columns Period 1, 2, 3, 4, 5, 6

I'm not sure which way to do it without creating some mega formula
that goes along the lines of (IF(reporting.month=1,(Period 1),If
(reporting.month=2,(Period 1)*(Period 2),if(reporting.month=3,(Period
1)*(Period 2)*(Period 3)......... any ideas on how to tackle this
one..... I am stuck.

As a side note - consider the formula you posted:

{=(SUM((O9B.CCB=VALUE(C7))*(O9B.CCD=VALUE(rep.fund ))*
(O9B.cat="Expense")*(O9B.Amounts)))/4}

1. *There is not reason to use the VALUE function in this type of formula
99% of the time.

-- Originally the value stored was in text format rather than as a
number and had to use VALUE, I kept using VALUE for this workbook to
be on the safe side.

2. *You can avoid the need for array entry by replacing the SUM with
SUMPRODUCT

-- Thanks, a lot easier to use........

3. *There is not need to put () around the last argument (09B.Amounts)


-- habit :)


If this helps, please click the Yes button
-- Which website are you using? I'm using Google Groups which gives a
rating.

Thanks, it was helpful.