G
GroovyKincaid
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.
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.