average, array and offsets
 

Hi,

It has been a while since I have used advanced formulas in excel and i
am having some difficulties so any suggestions would be appreciated!

*The Problem*
I want to multiply a cell by the average of the cells of the preceeding
3 months. My problem is I need to look up ALL of the cells. I have tried
using arrays but my understanding is a bit limited and I have run into
difficulties with how to structure a formula or even if it is
possible.

*Data Structure & Layout*
I have one worksheet which list Gross Margin (GM) for each month of the
year by client. Previous month data is fed off the actuals which are
looked up from another worksheet in the file.

The current month (at the moment november) also looks up the actuals
and extrapolates for month end.

We forecast for future months GM based on volume forecasts manually
entered by sales managers and account managers by month by client. This
is in a seperate worksheet in the file.

I also have another worksheet which lists by month by client the
average GM by client by message (we are a mobile aggregator) by
month.

*Solution So Far*
What i need to do is look up the volume forecast for the client for the
month and multiply this by the average GM by message by client for the
preceeding three months. I can look up the volume figure no problem.

When it comes to referencing the last three months, this is difficult.
So for example, for december i need to lookup the volume forcast and
multiply this by the average GM/call/month/client for September,
october and, november (these three figures also need to be looked up.

It is a bit hard to try and dump in some small examples of data so im
not sure if my problem is clear enough. I also believe my answer may
lie in index formula although im not sure. I used to build dynamic
graphs that you could scroll through and i used to use arrays with the
index formula but my memory is very hazy!

Any suggestions or help would be appreciated:)

Cheers!
Darin.


--
Darin1979


------------------------------------------------------------------------
Darin1979's Profile: http://www.excelforum.com/member.php...o&userid=16521
View this thread: http://www.excelforum.com/showthread...hreadid=314425