Using Excel to model multi-dimensional system with Solver

[Edward Stansfeld]

Hi, I'm looking for some ideas here...I'm building a resource planning model
in Excel, which looks a bit like a calendar, with Months and Tasks, i.e. two
dimensions, corresponding to columns and rows. The model uses Solver to
reach a solution.

I want to bring in another couple of dimensions; Staff Grade and Team. I
could have the data in a single sheet with Months along the top in columns,
and then three row grouping levels, Tasks, Grades and Teams, but this is
inefficient with space, and quite complex to program in VBA.

Is there a better data structure? The data seems to me more suited to a
relational database or OLAP cube, but I would prefer to keep everything in
Excel, as this is better for Solver.

Is there a matrix or array set of functions that would be useful, like
DOTPRODUCT or MMULT (all I need to do is add cells, not multiply them).

Thoughts welcome
Edward

 

[Dana DeLouis]

DOTPRODUCT and QUADPRODUCT Add-In Functions
can be downloaded here...

http://www.frontsys.com/members/dwn_xl97.htm#dotprod

Good luck. HTH.

 

[Edward Stansfeld]

We've emailed before on this, no?
Otherwise you wouldn't have linked me with using Solver. And I did not know
where to get hold of these functions - so thanks!
Edward