D
Darin1979
Hi,
It has been a while since I have used advanced formulas in excel and
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 preceedin
3 months. My problem is I need to look up ALL of the cells. I have trie
using arrays but my understanding is a bit limited and I have run int
difficulties with how to structure a formula or even if it i
possible.
*Data Structure & Layout*
I have one worksheet which list Gross Margin (GM) for each month of th
year by client. Previous month data is fed off the actuals which ar
looked up from another worksheet in the file.
The current month (at the moment november) also looks up the actual
and extrapolates for month end.
We forecast for future months GM based on volume forecasts manuall
entered by sales managers and account managers by month by client. Thi
is in a seperate worksheet in the file.
I also have another worksheet which lists by month by client th
average GM by client by message (we are a mobile aggregator) b
month.
*Solution So Far*
What i need to do is look up the volume forecast for the client for th
month and multiply this by the average GM by message by client for th
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 an
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 i
not sure if my problem is clear enough. I also believe my answer ma
lie in index formula although im not sure. I used to build dynami
graphs that you could scroll through and i used to use arrays with th
index formula but my memory is very hazy!
Any suggestions or help would be appreciated
Cheers!
Darin
It has been a while since I have used advanced formulas in excel and
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 preceedin
3 months. My problem is I need to look up ALL of the cells. I have trie
using arrays but my understanding is a bit limited and I have run int
difficulties with how to structure a formula or even if it i
possible.
*Data Structure & Layout*
I have one worksheet which list Gross Margin (GM) for each month of th
year by client. Previous month data is fed off the actuals which ar
looked up from another worksheet in the file.
The current month (at the moment november) also looks up the actual
and extrapolates for month end.
We forecast for future months GM based on volume forecasts manuall
entered by sales managers and account managers by month by client. Thi
is in a seperate worksheet in the file.
I also have another worksheet which lists by month by client th
average GM by client by message (we are a mobile aggregator) b
month.
*Solution So Far*
What i need to do is look up the volume forecast for the client for th
month and multiply this by the average GM by message by client for th
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 an
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 i
not sure if my problem is clear enough. I also believe my answer ma
lie in index formula although im not sure. I used to build dynami
graphs that you could scroll through and i used to use arrays with th
index formula but my memory is very hazy!
Any suggestions or help would be appreciated
Cheers!
Darin