If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|