Confusion amongst matrices
I have attached a model I am working on for manpower forecasting. Things were going ok until I added a sum function to the matrix “people and rates”. I added the sum function so that I could see the overall loading of a group of people as well as individually in the “Resource Time Entry” matrix, which is the main data entry sheet on a day to day basis. The “people and rates” matrix would only get updated periodically as people or rates change.
My problem comes into play with the “Total Days” and “Labour Costs” matrices. Because I am using “Sum” and “sumproduct” functions here that refer back to the “Resource Time Entry” matrix, I have inadvertently included the sum of people into the calculation, effectively doubling my calucations in “Total Days” and “Labour Costs”. How do I “skip” the summation item in “People and Rates” when calculating “Total Days” and “Labour Costs” because in those two matrices, the calculation relys upon data in the “Resource Time Entry” matrix? Is there a better way to do it?
When it comes to charts, is there a way to restrict the number of series presented in the graph? If you look at my model, there is a Matrix called “Usage Calcs” that I added. All of the equations in this matrix were originally contained in the “Resource Time Entry” Matrix, but the number of series presented for graphing were becoming unwieldy, so I created a separate matrix. However, if you look at the “Resource Time Entry” chart, there are still too many series there. I would like to restrict what is presented in the chart without affecting the underlying matrix.
One last question – I couldn’t upload the attached file as a template. I had to recreate it a model file before it could be uploaded. Why is that?
Thanks for your help.
Doug
Hi – I have amended your model and uploaded it as an attachment to this reply. I have made four changes:
1. On the chart, I right clicked “Sum of SI” and selected “Collapse”. This allows you (along with “Collapse Other”) to only chart part of a matrix;
2. In the “People and Rates” matrix, I grouped “PersonB1” to “PersonB6” as the “People” group – this will then allow us to just use these rows (and avoid the “Sum of SI” row);
3. In “Total Days”, I changed the summation formula to “Signed .. Committed Use=sum(Resource Time Entry::Month[THIS]:People)” (i.e. added “:People” to the summand); and
4. In “Labour Costs” (always nice to see British spelling!) I changed the sumproduct to “Signed .. Committed Use=sumproduct(People and Rates::Daily Rate:People, Resource Time Entry::Month[THIS]:People) Skip Project Work Books” (i.e. adding “:People” to both range expressions – and also changing the “*” to a “,” to make it more readable).
I hope this does the job! Let me know if you need anything else – I also do consultancy work if you’d like something commercial.