Remember Me?

#1
June 29th 11, 10:09 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jun 2011 Posts: 6
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

#2
July 5th 11, 12:15 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2009 Posts: 154
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.
#3
July 7th 11, 03:51 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 84
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post BW Excel Worksheet Functions 2 August 8th 09 01:57 AM SeanUK2005 Excel Worksheet Functions 3 March 6th 07 03:42 PM jeffreyj Excel Discussion (Misc queries) 10 June 5th 06 10:06 PM doggone Excel Discussion (Misc queries) 4 July 9th 05 07:59 PM Larry Excel Worksheet Functions 4 May 13th 05 09:22 AM

All times are GMT +1. The time now is 11:19 PM.