Help With Formula For Cell Addresses
 

I have a spreadsheet with monthly usage quantities for the last 18
months (Dec-09 to May-11). The figures are in columns B to S. Column
A has the part numbers.

The headings for the quantities are in row 3 and show the abbreviation
for the month and the year. For instance, "Dec-09", "Jan-10", etc.
That row is formatted as Date.

What I would like to do now is build a formula that calculates the
weighted average usage by putting the following variable data in cells
T1/T2 and U1/U2 and spits out the results down starting in cell V3:

* T1 = the weighted % assigned to the average obtained with the figure
specified in U1;
* U1 = an integer representing the number of older months to average
using the % indicated in T1;

* T2 = the weighted % assigned to the average obtained with the figure
specified in U2;
* U2 = and integer representing the number of younger months to average
using the % indicated in T2.

Obviously, I would need to make sure that T1 + T2 = 100%, and U1 + U2 =
18 months

The combined weighted results should be displayed down starting in cell
V3.

I guess I could write formulas all the way down for each part number
but things should be faster the way I want to do it as explained above
when doing simulations.

Thanks.

--
tb

Help With Formula For Cell Addresses
 

Maybe something like this would help.

Put this in V4 and copy down:
=$T$1*AVERAGE(OFFSET(B4,0,$U$2,1,$U$1))+
$T$2*AVERAGE(OFFSET(B4,0,0,1,$U$2))

I started in row 4 because row 3 has the headings.

If all the cells in an average are empty, there will be a #DIV/0
error. If this is a possibility with your data, add a check for this
case.

Modify as needed.

Help With Formula For Cell Addresses
 

On 07/04/2011 06:15 PM, zvkmpw wrote:
Maybe something like this would help.

Put this in V4 and copy down:
=$T$1*AVERAGE(OFFSET(B4,0,$U$2,1,$U$1))+
$T$2*AVERAGE(OFFSET(B4,0,0,1,$U$2))

I started in row 4 because row 3 has the headings.

If all the cells in an average are empty, there will be a #DIV/0
error. If this is a possibility with your data, add a check for this
case.

Modify as needed.

Perfect, thanks!
--
tb