A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Average If, with a Moving Target



 
 
Thread Tools Display Modes
  #1  
Old April 20th, 2010, 11:12 PM posted to microsoft.public.excel.misc
GroovyKincaid
external usenet poster
 
Posts: 1
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


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


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.