Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for summing addresses within a single cell | Excel Worksheet Functions | |||
Formula for matching addresses | Excel Worksheet Functions | |||
How do create a formula to add IP addresses? | Excel Discussion (Misc queries) | |||
Cell addresses | Excel Discussion (Misc queries) | |||
cell addresses | Excel Worksheet Functions |