Hi folks,
I created the following Excel Table:
Table1.png
It uses the Excel Solver to calculate the best combination in terms of costs and still handle all users on the server.
Please excuse my bad explanations but I'll try to give my best to explain it:
Formulas:
G2 = B2 * $F$2, B3 * $F$3 etc.
H2 = C2 * $F$2, C3 * $F$3 etc.
I2 = D2 * $F$2, D3 * $F$3 etc.
J2 = E2 * $F$2, E3 * $F$3 etc.
F6:J6 = each calculates the sum of its column
B13 / B14 / B15 hold the amount of users and small / medium / large stands for their purchased storage.
I calculated how many user one server would be able to handle in the columns B / C / D.
By pressing the "Calculate" button I run the following VBA code:
It basically looks for:Please Login or Register to view this content.
Amount * server type = user which are handable -> is this number greater or equal to the amount of user types (small / medium / large) in total
It calculates fine but there is one problem: The solver only takes the users separately into consideration.
What I'm trying to say is e.g.:
I have:
2 small users
10 medium users
20 large users
Small server handles:
20 small users
15 medium users
10 large user
Medium server handles:
30 small users
20 medium users
15 large users
Large server handles:
40 small users
30 medium users
20 large users
Let's not talk about the costs now.
The calculation would now probably show that either 1 large server or 2 small servers are needed in order to handle all users (because 20 large users have to be handled).
But the calculation is off. If I have 2 small servers the solver currently thinks I'd be able to handle 2*20 small users AND 2*15 medium users AND 2*10 small users but in reality it's 2*20 OR 2*15 OR 2*10 as I'm not able to put 2*15 medium users on the server if it's filled with 2*20 small users already.
And that's the problem I was trying to solve in the last few hours but it seems I got a brainfreeze of that tasty slush as I'm not able to fix it myself
Hope I was able to made myself clear. If not, just tell me and I try to give it my all to do better.
Thanks for your help & effort.
Best,
Mike
Bookmarks