Using Excel's Solver and VBA For Repetitive Table Calculation

Quote of the Day

A belief is not merely an idea the mind possesses; it is an idea that possesses the mind.

— Robert Oxton Bolt, screenwriter and playwright


Introduction

Figure 1: Footing Diameter and Thickness Versus Deck Area. (Source)

Figure 1: Footing Diameter and Thickness Versus Deck Area. (Source)

I will be providing some employee training on Excel in January, and I need an example of how to automate the use of Excel's Solver add-in – a powerful optimization tool that few engineers use effectively. When I give a training seminar, I make a serious effort to show how I use Excel on real problems. While I generally use Mathcad for most optimization applications, Mathcad does not support integer programming – an optimization method where some or all variables are restricted to be integers. Here is where Solver shines – it supports integer programming.

While reading about how to size footings for decks, I used the table in Figure 1 to determine my footing sizes. To ensure that I understood the physics behind the table, I decided to use Excel and Solver to generate it on my own. In this post, I will be using Excel's VBA with Solver to generate the table of footing sizes for given deck tributary areas shown in Figure 1.

I am going to apologize right from the start for using US customary units – the table is in square feet and inches. Adding metric conversion would have complicated a relatively simple problem.

Here is my source for those who are interested.

Note: I am not a structural engineer. I am only using some basic math to generate a table of values. If you have a structural question, hire a structural engineer. If you have an electrical engineering question, you can give me a try.

Background

Caveats

  • There are many valid ways to fill in this table.
  • I do not know what optimization criteria was used by the standards body that created the table.
  • My objective is to minimize the amount of concrete required while meeting the code requirements (described below).
  • My results are very close to those in Figure 1, but not exactly the same.

Assumptions

There are some basic assumptions that one needs to keep in mind when computing footing sizes.

  • The table says that decks must support a live load of 40 lbf per square foot (psf) and a dead load of 10 psf, for a total deck load of 50 psf. This agrees with the building codes in my area.
  • Each footing is assumed to have a certain area of deck it is assigned to supporting that is called the tributary area.
  • The soil must support the weight of the deck tributary area and the weight of the concrete footing. The weight of the concrete footing is proportional to its volume. For this exercise, I am only interested in round footings. The table in Figure 1 includes values for square footings. I don't use square footings, so I am not interested in these values.
  • I assume a concrete density of 150 lbf per cubic foot (pcf).
  • Different soils can support different amounts of weight per square foot. In the table of Figure 1, the assumed soil loading values are 1500 psf, 2000 psf, 2500 psf, and 3000 psf.
  • I am assuming that the footing thickness is restricted to integer values of 6 or greater inches.
  • The concrete footing thickness (τ inches) is related to the diameter of the pad (D inches) by formula \tau\ge \frac{{D-5.5}}{2} , where τ and D are expressed in inches.
  • I then use Solver to compute the minimum required footing diameter.

While my focus in this post is on the use of Solver, you can solve this problem using Mathcad – just no integer programming. See Appendix A for the details.

Analysis

Formula Derivation

Equation 1 is the key formula for this post.

Eq. 1 \displaystyle {{A}_{{Footing}}}=\frac{{{{A}_{{Deck}}}\cdot {{\sigma }_{{Deck}}}}}{{{{\sigma }_{{Soil}}}-\tau \cdot {{\rho }_{{Concrete}}}}}

where

  • AFooting is the area of the footing.
  • ADeck is the tributary area of the deck being served by the footing.
  • σDeck is deck loading factor required by the applicable building code.
  • σSoil is soil loading factor required by the applicable building code.
  • ρConcrete is density of the concrete.
  • τ is the thickness of the footing. The footing thickness must conform to the constraint \tau\ge \frac{{D-5.5}}{2}, where τ and D are expressed in inches. The thickness of a footing can never be less than 6 inches.

I derive this formula in Figure 2 (see the yellow highlight).

Figure M: Derivation of Equation 1.

Figure 2: Derivation of Equation 1.

Excel VBA

The following VBA code snippet shows my VBA routine. I generated this routine by using the macro recorder to provide me clues on how to invoke the Solver routine.

Sub Deck_Table_Optimizer()

 Dim deckArea As Range
 Set deckArea = Range("_biff")

 Dim rngObjectCell As Range
 For Each rngObjectCell In deckArea
 SolverReset 'Clear out all Solver settings
 Range("_A").Value = rngObjectCell.Value 'This is the footing thickness we will be varying
 SolverOk SetCell:="_V", MaxMinVal:=2, ValueOf:=0, ByChange:="_t", Engine:=1, EngineDesc:="GRG Nonlinear"
 SolverAdd CellRef:="_t", Relation:=4, FormulaText:="integer" 'I want integer footing thickness
 SolverAdd CellRef:="_A", Relation:=2, FormulaText:=CStr(rngObjectCell.Value) 'Allowed soil pressure
 SolverAdd CellRef:="_t", Relation:=3, FormulaText:="6" 'The footing thickness greater than 6 inches.
 SolverAdd CellRef:="_t", Relation:=3, FormulaText:="_constraint"
 SolverSolve True
 rngObjectCell.Offset(0, 1).Value = Round(Range("_D").Value + 0.45, 0)
 rngObjectCell.Offset(0, 2).Value = Range("_t").Value
 Next

End Sub

Figure 3 shows the equivalent Solver dialog for this routine. Here is my key for the variables:

  • _V is the concrete volume, which we will minimize.
  • _t is the footing thickness
  • _A is the deck area
  • _D is the diameter of the footing
Figure M: Solver Dialog.

Figure 3: Solver Dialog.

Results

Figure 4 shows my Solver results. The number in red indicate where my results differ from the corresponding results in Figure 1. Both Figure 1 and Figure 4 are "correct" in that they meet the requirements – I assume the optimization criteria was different for Figure 1. In fact, Figure 1 may have been generated without an optimization criteria at all.

Figure M: Solver Generated Version of Figure 1.

Figure 4: Solver Generated Version of Figure 1.

Conclusion

In my seminar next month, I will be presenting this exercise as an example of how to use Solver and VBA to solve a repetitive nonlinear optimization problem. It surprised me how easy it was to use Solver for this type of application.

Appendix A: Mathcad Solution.

While Mathcad does not provide an integer programming feature, you can solve this problem using Mathcad. Let it solve the problem using non-integers and then round up. It may not be optimal, but it will be close. Figure 5 show my Mathcad algorithm.

Figure M: Mathcad Version of Optimization Algorithm.

Figure 5: Mathcad Version of Optimization Algorithm.

Figure 6 show the final result with Figure 1 right beside.

Figure M: Mathcad Result with Figure 1 Adjacent.

Figure 6: Mathcad Result with Figure 1 Adjacent.

Save

Save

Save

Save

Save

Save

Save

Save

This entry was posted in Construction, Excel. Bookmark the permalink.

9 Responses to Using Excel's Solver and VBA For Repetitive Table Calculation

  1. Ronan Mandra says:

    Mark, I like how you apply Solver to a column of tributary data by pulling each one into a separate working area, one at a time.

    • mathscinotes says:

      This example was used to illustrate a number of things:

      • Use of macro recorder to enable learning how to program something new.
      • Use of local variables with a macro. The same macro was used for each tab. It works because variables local to each active sheet were used.
      • Sometimes the quickest way to solve a problem is just to replicate tabs, which was done here. I could have spent more time on the VBA and included the tributary area in the routine. However, I was only going to solve this problem once and more automation was not worth it.

      Glad you liked it. I am trying to include more Excel because I am getting requests from my staff for my Excel-oriented training. My Mathcad evangelism is slowly working, but there are some Excel diehards.

      mark

  2. Ronan Mandra says:

    Mark,
    I found a minor typo in your SolvingExample.xslm. In your 1500psf tab, cell D18 has a value of #Name. It's a calculated cell with value "= (_R-5.5)/2." I believe you meant to use reference "_D" vice "_R".

    I've used Excel for a number of years and have taken a number of courses in Linear Programming as part of an Master's in Operations Research (not completed). Just an FYI, there is an open source solver called OpenSolver that works with Excel or Google Spreadsheets that does not have the size limitations that the Excel Solver has,
    ref: http://opensolver.org/.

    Thanks for the Excel training as I'm always learning something from your notes.

    • mathscinotes says:

      Thanks for the detailed review. The correction has been made. I AGAIN had to learn that there is no such thing as a minor change that doesn't need testing. I appreciate the reference to the opensolver program – I had never heard of it, and I definitely can make use of it.

      As far as Excel goes, my son is the real guru in the family. He wants me to put together a web site where people contribute their solutions to specific problems – he has hundreds of his personal solutions he wants to present. His work is mainly with Excel and large financial databases. He wants me to contribute engineering content. I am busy trying to put that web page together now. With my normal work, the new web site, and the grandchild, we are very busy.

      Hope all is well with you and your family.

      mark

  3. Ronan Mandra says:

    Mark,
    Looking forward to seeing your Excel website.

    Also, wishing you and your family the best during this Christmas or Hanukkah or Holiday Season.

    Ronan

  4. hitmusic says:

    good article thanks for sharing

  5. sultan says:

    hey how can i contact with you ?

Comments are closed.