I have a quantity that must be multiplied by a cumulative % factor at each
period over a time series. At each period of this time series I add another
quantity that must then be multiplied by the same series of factors starting
from the beginning. The resulting list of factored quantities in each time
series are totaled. The three components here, the time series, the list of
quantities and the list of factors are quite big so I am looking for a
formula, an array formula or otherwise that will apply these calculations to
these values without having to make a huge array of discrete calculations.
The logic/structure of the data looks like the array below.

Time Period 1 2 3
Factor 90% 93% 95%
Starting Qty1 1000 900 837 795.15
Factor 90% 93%
Starting Qty2 1300 1170 1088.1
Factor 90%
Starting Qty3 1100 990
Total 900 2007 2873.25

I have been trying to find a way to do this with an array formula but I
can't find the way to stagger the new quantities being started up in each
suceeding time period. Anyone got any good ideas about this?
Thanks in advance,
RD Wirr

Not sure where the last row of data comes from.

To add every second row in column B:
=SUMPRODUCT(--(MOD(ROW(B1:B6),2)=0),B1:B6)
Please clarify
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"RD Wirr" wrote in message
...
I have a quantity that must be multiplied by a cumulative % factor at each
period over a time series. At each period of this time series I add
another
quantity that must then be multiplied by the same series of factors
starting
from the beginning. The resulting list of factored quantities in each time
series are totaled. The three components here, the time series, the list
of
quantities and the list of factors are quite big so I am looking for a
formula, an array formula or otherwise that will apply these calculations
to
these values without having to make a huge array of discrete calculations.
The logic/structure of the data looks like the array below.

Time Period 1 2 3
Factor 90% 93% 95%
Starting Qty1 1000 900 837 795.15
Factor 90% 93%
Starting Qty2 1300 1170 1088.1
Factor 90%
Starting Qty3 1100 990
Total 900 2007 2873.25

I have been trying to find a way to do this with an array formula but I
can't find the way to stagger the new quantities being started up in each
suceeding time period. Anyone got any good ideas about this?
Thanks in advance,
RD Wirr

On Mon, 4 Feb 2008 04:44:01 -0800, RD Wirr
wrote:

I have a quantity that must be multiplied by a cumulative % factor at each
period over a time series. At each period of this time series I add another
quantity that must then be multiplied by the same series of factors starting
from the beginning. The resulting list of factored quantities in each time
series are totaled. The three components here, the time series, the list of
quantities and the list of factors are quite big so I am looking for a
formula, an array formula or otherwise that will apply these calculations to
these values without having to make a huge array of discrete calculations.
The logic/structure of the data looks like the array below.

Time Period 1 2 3
Factor 90% 93% 95%
Starting Qty1 1000 900 837 795.15
Factor 90% 93%
Starting Qty2 1300 1170 1088.1
Factor 90%
Starting Qty3 1100 990
Total 900 2007 2873.25

I have been trying to find a way to do this with an array formula but I
can't find the way to stagger the new quantities being started up in each
suceeding time period. Anyone got any good ideas about this?
Thanks in advance,
RD Wirr

Here's another way of setting up your data that might work out better. In the
formulas, I have assumed that your data is in rows 11-30, with time periods in
C10...

Somewhere on the worksheet, you have a column of factors attributable to each
time period. Name this range "Factors". It might look like:

90%
93%
95%
98%
etc.

Here is your data table (note that the time period values are calculated per
the formula below:

Qty 1 2 3 4 5
Starting Qty1 1000 900 837 795.15
Starting Qty2 1300 1170 1088.1
Starting Qty3 1100 990


C11:
=IF(AND(C$10<=COUNTA($A$11:$A$30),COUNTA($A$11:$A1 1)<=C$10),
PRODUCT($B11,OFFSET(Factor,0,0,COLUMNS($A:B)-COUNTA($A$11:$A11))),"")

Fill the formula to the right for as many columns as there are time periods,
then fill down for as many Starting Qty's as there are, or may be.

In the formula, adjust the $A$30 parameter to reflect the maximum number of
Starting Qty's that might exist. e.g. you could change it to $A$1000

=IF(AND(C$10<=COUNTA($A$11:$A$1000),COUNTA($A$11:$ A11)<=C$10),
PRODUCT($B11,OFFSET(Factor,0,0,COLUMNS($A:B)-COUNTA($A$11:$A11))),"")

To SUM each time period, in, for example, C9:

C9: =SUM(C11:C1000)

In the formula in the Data Table, note that the COUNTA function adjusts where
data appears within the table, so the cells in column A should be blank. If
the cells have a formula in them, we will need a different test than COUNTA on
that column. But that's a simple change.
--ron

If you can arrange your source data
as shown, only one formula is needed:
http://www.freefilehosting.net/download/3bejf

Hi Ron,

Thanks for the formula. That works perfectly. Clever stuff.

Regards,
RDW

"Ron Rosenfeld" wrote:

On Mon, 4 Feb 2008 04:44:01 -0800, RD Wirr
wrote:

I have a quantity that must be multiplied by a cumulative % factor at each
period over a time series. At each period of this time series I add another
quantity that must then be multiplied by the same series of factors starting
from the beginning. The resulting list of factored quantities in each time
series are totaled. The three components here, the time series, the list of
quantities and the list of factors are quite big so I am looking for a
formula, an array formula or otherwise that will apply these calculations to
these values without having to make a huge array of discrete calculations.
The logic/structure of the data looks like the array below.

Time Period 1 2 3
Factor 90% 93% 95%
Starting Qty1 1000 900 837 795.15
Factor 90% 93%
Starting Qty2 1300 1170 1088.1
Factor 90%
Starting Qty3 1100 990
Total 900 2007 2873.25

I have been trying to find a way to do this with an array formula but I
can't find the way to stagger the new quantities being started up in each
suceeding time period. Anyone got any good ideas about this?
Thanks in advance,
RD Wirr

Here's another way of setting up your data that might work out better. In the
formulas, I have assumed that your data is in rows 11-30, with time periods in
C10...

Somewhere on the worksheet, you have a column of factors attributable to each
time period. Name this range "Factors". It might look like:

90%
93%
95%
98%
etc.

Here is your data table (note that the time period values are calculated per
the formula below:

Qty 1 2 3 4 5
Starting Qty1 1000 900 837 795.15
Starting Qty2 1300 1170 1088.1
Starting Qty3 1100 990


C11:
=IF(AND(C$10<=COUNTA($A$11:$A$30),COUNTA($A$11:$A1 1)<=C$10),
PRODUCT($B11,OFFSET(Factor,0,0,COLUMNS($A:B)-COUNTA($A$11:$A11))),"")

Fill the formula to the right for as many columns as there are time periods,
then fill down for as many Starting Qty's as there are, or may be.

In the formula, adjust the $A$30 parameter to reflect the maximum number of
Starting Qty's that might exist. e.g. you could change it to $A$1000

=IF(AND(C$10<=COUNTA($A$11:$A$1000),COUNTA($A$11:$ A11)<=C$10),
PRODUCT($B11,OFFSET(Factor,0,0,COLUMNS($A:B)-COUNTA($A$11:$A11))),"")

To SUM each time period, in, for example, C9:

C9: =SUM(C11:C1000)

In the formula in the Data Table, note that the COUNTA function adjusts where
data appears within the table, so the cells in column A should be blank. If
the cells have a formula in them, we will need a different test than COUNTA on
that column. But that's a simple change.
--ron

Hi Bernard,

The data I showed here was just the logical structure rather that the actual
way I had my data in the spreadsheet. So there was no interim rows of the
factors. I was already multiplying the factors in each data row offsetting
the factors for each successive Qty and time period. I was just looking for a
more elegant way to work the offset rather than using a simple manually
offset formula in each cell.

But thanks anyway.
Rgds,
RDW

"Bernard Liengme" wrote:

Not sure where the last row of data comes from.

To add every second row in column B:
=SUMPRODUCT(--(MOD(ROW(B1:B6),2)=0),B1:B6)
Please clarify
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"RD Wirr" wrote in message
...
I have a quantity that must be multiplied by a cumulative % factor at each
period over a time series. At each period of this time series I add
another
quantity that must then be multiplied by the same series of factors
starting
from the beginning. The resulting list of factored quantities in each time
series are totaled. The three components here, the time series, the list
of
quantities and the list of factors are quite big so I am looking for a
formula, an array formula or otherwise that will apply these calculations
to
these values without having to make a huge array of discrete calculations.
The logic/structure of the data looks like the array below.

Time Period 1 2 3
Factor 90% 93% 95%
Starting Qty1 1000 900 837 795.15
Factor 90% 93%
Starting Qty2 1300 1170 1088.1
Factor 90%
Starting Qty3 1100 990
Total 900 2007 2873.25

I have been trying to find a way to do this with an array formula but I
can't find the way to stagger the new quantities being started up in each
suceeding time period. Anyone got any good ideas about this?
Thanks in advance,
RD Wirr

Hi Herbert,

Very nice solution. I will probably use Ron's suggestion in this case
because it's easier for me to arrange my data with his solution, but the
Pivot table way is good and it gives me an idea for fixing another
application I have.

Thanks much,
RDW

"Herbert Seidenberg" wrote:

If you can arrange your source data
as shown, only one formula is needed:
http://www.freefilehosting.net/download/3bejf

On Tue, 5 Feb 2008 03:23:01 -0800, RD Wirr
wrote:

Hi Ron,

Thanks for the formula. That works perfectly. Clever stuff.

Regards,
RDW

You're welcome. Glad to help. Thanks for the feedback.
--ron