Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average If, with a Moving Target
Hi,
I am trying to do something with an average that is similar to a sumif statement. The spreadsheet is a rolling 12 month forecast. What I have is an area of the spreadsheet in which the user will enter a date in cell L2. Cell L5 equals Cell L2 then Cells M5 through to W5, there are forumlas that will add a month to the month in the previous cell. There is a VLookup to a table in which equates a particular date to a fiscal quarter and fiscal year. For example, April 2010 (in Cell L5) returns "Q2-2010", June 2010 (in Cell N5) returns "Q2-2010"...March 2011 (in Cell W5) returns "Q1-2011". Under each month, the user is to enter the expected or forecasted percentages of product mix. In another section of the spreadsheet, there is an area for forecast, The column titles are "Q1-2010" cell F5, "Q2-2010" cell g5, "Q3-2010" cell h5, "Q4-2010 cell i5", "Q1-2011 cell j5". It is in this section that I would like to write a formula that averages the percentages based on the quarter-year vlookup results using the titles above. So in lay man's language I would like to do this: In Column F give me the average of L6 to W6, but only if it matches the data in F5. Like wise for Column G, give me the average of L6 to W6 only if it matches the data in G5 and so on. I am not too sure how to achieve this but any help is appreciated. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif two conditions moving target | Excel Discussion (Misc queries) | |||
Automaticly changing average function target cells | Excel Worksheet Functions | |||
Target cell reference moves when target is cut and pasted | Excel Discussion (Misc queries) | |||
how to do a 50 day and 200 day moving average | Excel Discussion (Misc queries) | |||
Moving average | Charts and Charting in Excel |