Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 29th 11, 10:09 PM posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2011
Posts: 6
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old July 7th 11, 03:51 AM posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 84
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula for summing addresses within a single cell BW Excel Worksheet Functions 2 August 8th 09 01:57 AM
Formula for matching addresses SeanUK2005 Excel Worksheet Functions 3 March 6th 07 03:42 PM
How do create a formula to add IP addresses? jeffreyj Excel Discussion (Misc queries) 10 June 5th 06 10:06 PM
Cell addresses doggone Excel Discussion (Misc queries) 4 July 9th 05 07:59 PM
cell addresses Larry Excel Worksheet Functions 4 May 13th 05 09:22 AM


All times are GMT +1. The time now is 12:01 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017