+ Reply to Thread
Results 1 to 18 of 18

Excel Solver through VBA - how could I adapt my calculation

  1. #1
    Registered User
    Join Date
    02-17-2016
    Location
    Vienna, Austria
    MS-Off Ver
    2013
    Posts
    10

    Excel Solver through VBA - how could I adapt my calculation

    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:

    Please Login or Register  to view this content.
    It basically looks for:

    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

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Excel Solver through VBA - how could I adapt my calculation

    Calculate the weighted total of users where one small user =1, one medium user =1.5 and one large user =2. So for your example:

    2 small users
    10 medium users
    20 large users
    Weighted users: 2*1 + 10*1.5 + 20*2 = 57

    Then solve for cost vs. weighted capacity of the servers

    Small server handles:
    20 small users
    15 medium users
    10 large user
    Weighted capacity = 20

    Medium server handles:
    30 small users
    20 medium users
    15 large users
    Weighted capacity = 30

    Large server handles:
    40 small users
    30 medium users
    20 large users
    Weighted capacity = 40

    So it looks like you would require one large server and one small server, or two medium servers, or three small servers.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Excel Solver through VBA - how could I adapt my calculation

    A solver maximum model perhaps like this? But the point raised by alphafrog is rather interesting. I do wonder how one should model that, could be interesting to have a go at it but not sure I'm up to it.

    Alf
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-17-2016
    Location
    Vienna, Austria
    MS-Off Ver
    2013
    Posts
    10

    Re: Excel Solver through VBA - how could I adapt my calculation

    thanks for your answers guys.

    @AlphaFrog: I don't really get what you wanted to say about 'solve it for costs vs. weighted capacity'. How should I calculate the weighted capacity compared to the costs of the server? Maybe I'm just thinking too much and make the problem bigger than it really is

    @Alf: I will inspect your sheet tomorrow morning and get back to you if it worked out

  5. #5
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Excel Solver through VBA - how could I adapt my calculation

    Hi

    My first uploaded model is useless so forget all about it.

    The capacity idea that AlphaFrog defined is the right way to set up solver.
    As a small server can handle 20 small users or 10 large one the capacity demand of a large use is 2 (20/10) as compared to 1 for a small user.

    The new model has a user matrix and a capacity matrix. But I've had to scale server cost since solver don't work with "If" commands.

    This is the problem with the model as server cost should be the driving force but it must be modelled properly and my "scaling" of server cost is not right. Still it may give you a starting point.

    The model works for one of each kind of servers i.e. small, medium, large and extra large. To have more of one kind of servers and get full flexibility one could probably use two or three rows for each kind of server.

    Alf
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-17-2016
    Location
    Vienna, Austria
    MS-Off Ver
    2013
    Posts
    10

    Re: Excel Solver through VBA - how could I adapt my calculation

    thanks again for your input. I thought I'd be able to handle it but I did something wrong :/

    I calculated the weighted numbers and it looks like this now:
    SolverProblem.png

    My VBA code was adjusted to this:
    Please Login or Register  to view this content.
    I think this is related to me using "I44" to compare the values in E40:E43 because if I change I44 to I40:I43 the solver calculates something but not the right one (it now calculates how many servers are needed for each row -> how many small servers needed for E40, how many medium servers for E41, how many large servers for E42 ....)

    Hope you can help me out again as I'm currently not able to do it on my own.

  7. #7
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Excel Solver through VBA - how could I adapt my calculation

    As I and my wife is on a 10 day vaccation in Vienna at the moment I'm not sure how I can help you unless you come to Cafe Hummel during midmorning or we could meet in the afternoon at Yppenplatz,
    Cafe Linden. State you preferences where and when but you must bring your laptop as I only have a netpad with me and This is ot up to solver problems.

    Doing a bit of testing I found I got better answer from solver if I deleted the cell references in the solver setup for the target value.

    If a meeting in Vienna should suit you PM me with a date a time and a place to meet. If not your problem will have to wait untill the 13th of May when I'm back in Stockholm.

    Alf
    Last edited by Alf; 05-03-2016 at 10:37 AM.

  8. #8
    Registered User
    Join Date
    02-17-2016
    Location
    Vienna, Austria
    MS-Off Ver
    2013
    Posts
    10

    Re: Excel Solver through VBA - how could I adapt my calculation

    Thanks Alf for your offer. I'm glad to see you visit this beautiful city
    As I'm about 350km away from Vienna it isn't really possible but I'm really thankful for this opportunity.

    I'll try to fix this during your vacation and hopefully I'm able to do so as I don't want to bother you after you come home in a rested state

    Best,
    Mike

  9. #9
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Excel Solver through VBA - how could I adapt my calculation

    Ok to bad about the distance but hopefully you can solve this on your own. If not I'll have a go at it when I'm back in Stockholm with access to a proper PC unless (hopefully) another helpfull forum member do have a solution this.

    I would suggest you upload a sample file, as this makes it much easier to test possible solutions.

    Alf

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Excel Solver through VBA - how could I adapt my calculation

    Given that it is surely the case the larger servers offer more capacity per dollar (or else why build them), and AF's insight to use a weighted total capacity, I think the problem can be solved in closed form (i.e., without Solver).

    B
    D
    E
    F
    G
    H
    I
    J
    2
    Size
    L
    M
    S
    3
    Price
    $ 1,000
    $ 800
    $ 600
    4
    Capacity
    40
    30
    20
    5
    Unit Price
    $ 25.00
    $ 26.67
    $ 30.00
    6
    Cap Reqd
    Qty
    Qty
    Qty
    Tot Cap
    Tot Cost
    7
    5
    0
    0
    1
    20
    $ 600
    D7:F7: {=PkgQty(B7, $D$3:$F$3, $D$4:$F$4)}
    8
    10
    0
    0
    1
    20
    $ 600
    9
    15
    0
    0
    1
    20
    $ 600
    10
    20
    0
    0
    1
    20
    $ 600
    11
    25
    0
    1
    0
    30
    $ 800
    12
    30
    0
    1
    0
    30
    $ 800
    13
    35
    1
    0
    0
    40
    $ 1,000
    14
    40
    1
    0
    0
    40
    $ 1,000
    15
    45
    1
    0
    1
    60
    $ 1,600
    16
    50
    1
    0
    1
    60
    $ 1,600
    17
    55
    1
    0
    1
    60
    $ 1,600
    18
    60
    1
    0
    1
    60
    $ 1,600
    19
    65
    1
    1
    0
    70
    $ 1,800
    20
    70
    1
    1
    0
    70
    $ 1,800
    21
    75
    2
    0
    0
    80
    $ 2,000
    22
    80
    2
    0
    0
    80
    $ 2,000
    23
    85
    2
    0
    1
    100
    $ 2,600
    24
    90
    2
    0
    1
    100
    $ 2,600
    25
    95
    2
    0
    1
    100
    $ 2,600
    26
    100
    2
    0
    1
    100
    $ 2,600


    Please Login or Register  to view this content.
    Last edited by shg; 05-03-2016 at 12:26 PM.
    Entia non sunt multiplicanda sine necessitate

  11. #11
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Excel Solver through VBA - how could I adapt my calculation

    I may have oversimplified the problem but this is what I had in mind. The basic premise is the needed capacity for Sm\Med\Large users are all converted to an equivalent total capacity for small users. Then the required server capacity is determined and the costs calculated.

    Server_Capacity.PNG

    • Input the desired capacity (green)
    • The equivalent small user capacity is calculated (pink)
    • Solve for Min cost (D15) by changing Quantities (C10:C12) and constrain Result Capacity (E13) >= Required Capacity (E6)
    • The Costs and Capacities (D10:E12) are formulas that just multiply the quantities by the values in the Server table (G2:I5).

    I would have attached the workbook as well but this lovely site won't allow it at this time for some reason.
    Here's the VBA Solver code I used.

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    02-17-2016
    Location
    Vienna, Austria
    MS-Off Ver
    2013
    Posts
    10

    Re: Excel Solver through VBA - how could I adapt my calculation

    @shg: thank you very much for your input. I'm just not sure how I should map that to my current setup so I'd prefer to continue with the solutions provided by ALF & AlphaFrog as I think I'm missing only a little bit to reach the goal

    @AlphaFrog: thanks for your answer again. The only problem with your solution is the following: It assumes that the small user equivalent ratio always stays the same but it does not as the server (small / medium / large / xl) have different configurations. I can't post my original worksheet as it included confidential information but I exported the relevant parts into a separate worksheet. Hope you can see what I tried to do but I think I have a logic error as well as an incapability to solve this myself I didn't include any VBA code as I would have to adjust it but it would only be the Solver Code like you did in your last reply

    As the attachement functionality for files is really broken at the moment, I had to upload it somewhere else:

    https://www.dropbox.com/s/6jpd1znt2v...mple.xlsx?dl=0

  13. #13
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Excel Solver through VBA - how could I adapt my calculation

    Hi Mike
    To bad the suggested solution from AlphaFrog did not solve your problem as I thought it was spot on. But this makes me wonder if your problem is more complex than I first thought.

    For instance a small server can support 20 small or 10 big users. So if you had 10 small users on this server you have a spare capacity of 10 or in terms of a big user 5. Could you take this 5 and add the missing capacity from another server or must a user take all the capacity he needs from one server?

    Alf

  14. #14
    Registered User
    Join Date
    02-17-2016
    Location
    Vienna, Austria
    MS-Off Ver
    2013
    Posts
    10

    Re: Excel Solver through VBA - how could I adapt my calculation

    hi Alf, hope you're doing fine in Vienna. Don't bother helping me, you should rather enjoy your vacation

    No, it isn't possible to borrow ressources from an other servers.
    Maybe the solution provided by AlphaFrog is spot on, but I'm not able to adapt it to my needs.


    AlphaFrog used an equivalent ratio of 1 / 1.5 / 2 for small, medium and large users.

    As my worksheet indicates, a small server in my case is able to handle 10 small users / 6 medium users and 3 large users.

    This would result in the following equivalent ratios for small users:
    • 1
    • 1,66
    • 3,33

    A medium server on the other hand is able to handle 15 small users / 12 medium users and 10 large users due to different adjustments to hardware configurations.
    This results in the following equivalent ratios for for small users:
    • 1
    • 1,25
    • 1,5

    Every server has other equivalent ratios and therefore I can't just take "amount of users * equivalent ratios = total amount of equivalent small users" into my solver calculation,
    as AlphaFrog did with "E6".

  15. #15
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Excel Solver through VBA - how could I adapt my calculation

    This does three separate solvers; one each for Small Clients, Medium Clients, and Large Clients. It accumulates the results for each.
    Example.xlsm

    Capture.PNG

    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    02-17-2016
    Location
    Vienna, Austria
    MS-Off Ver
    2013
    Posts
    10

    Re: Excel Solver through VBA - how could I adapt my calculation

    thanks AlphaFrog this was exactly what I was looking for. You made my day
    I also want to thank the rest of you for all your effort.

    Best forum in terms of willingness to help I've experienced so far!
    Wish you all the best.

  17. #17
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Excel Solver through VBA - how could I adapt my calculation

    You're welcome. Thanks for the feedback.

  18. #18
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Excel Solver through VBA - how could I adapt my calculation

    Back from vacation curiosity got the better of me and I tested if I could set up a shorter macro for solver run.

    With a model setup as in the uploaded file a macro like this seems to work for Excel versions 2003 to 2010.

    Please Login or Register  to view this content.
    Alf
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel Solver VBA - Solver Violating Constraints
    By lespaul00 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-19-2015, 04:00 PM
  2. Replies: 14
    Last Post: 03-02-2014, 11:48 AM
  3. Goalseek/Solver with calculation in limited range
    By ajayb23 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-10-2013, 04:22 PM
  4. Adapt macro to select from list - Excel 2010
    By MrsRobinson in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-22-2012, 04:24 PM
  5. Solver calculation problem
    By freddie12345 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-14-2008, 06:07 PM
  6. Automating a calculation - (Solver)
    By Muten_roshi in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-11-2006, 05:14 AM
  7. Using solver while disabling auto calculation
    By Kevin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-07-2006, 10:00 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1