# Excel Workbook

## Quick Peer Review

For those of you in the business of peer reviewing other people’s data entries, the ability to minimize tedious checks is essential.  Today we’ll look at how =SUM(ABS(…)) is a convenient tool to reduce a simple 3-step process to a simpler 1-step process.

Your intern has just copied a large table of numbers from Worksheet1 to Worksheet2, and you want to make sure nothing went wrong in the process.  In best practice, you’d want to check not only the numbers but also the relative position of the numbers… did the intern switch some rows or columns around?  A good system of lookup formulas should be employed to ensure the integrity of this data transfer.  However, for now let’s just focus on the most basic case where all we care is that the two tables match identically.

It’s a huge table of 100 rows and 20 columns, so you really don’t want to look at each number with your bare eyes.  A typical way to check is to set up a third table of the same dimentions, and subtract the corresponding cells in the two tables:

(Worksheet3!A1) =Worksheet1!A1-Worksheet2!A1

(Worksheet3!A2) =Worksheet1!A2-Worksheet2!A2

After that, a quick glance for nonzero values will help you find places where discrepancies exist.  This is not difficult or time consuming by any means, but you still have to (1) type in a formula, (2) copy formula to the correct range, and (3) check for nonzero values.  Instead, try this array formula :

<=SUM(ABS(Worksheet1!A1:T100-Worksheet2!A1:T100))>     (Don’t forget to press  CTRL+ALT+Enter )

This formula does an element-wise subtraction of the second table from the first table, and totals the absolute differences into a single number.  If the result of this formula is nonzero, then we’ve got a discrepancy problem worth further investigation – otherwise the job is done!  The ABS() function here serves an important role at making sure positive discrepancies don’t happen to offset negative discrepancies.

## Automatic Goal Seek

This article demonstrates a simple VBA method to utilize the Goal Seek tool automatically, similar to a native Excel formula.

Goal Seek is an Excel built-in tool that uses numeric approximation methods to “back solve” for an input value to a system of formulas when given a desired output value.  If you are not familiar with Goal Seek. many tutorials online can guide you to using this tool.  I hate it, and try to avoid it at all costs… but like everything else that I hate, there come times when I just have to face it like a man.

The problem?  For one, it’s not transparent, and we can’t exactly tell what Excel did in the background.  But more importantly, it’s a pain to apply and to udpate.  While all other formulas can automatically re-calculate upon changes to the input values, Goal Seek requires you to click on the menu to bring up the dialog box, then fill out three input boxes manually.

See the spreadsheet above.  Everything in blue are input values to this dynamic model, which can affect the results.  We want to set E14 to a value specified in D3. so these two cells correspond to the first two parameters of Goal Seek (“ Set cell ” and “ To value “).  As an alternative, we put the difference between the two in F6. and set this cell to 0 (x=y is equivalent to x-y=0).  This approach will slightly simplify the coding later on, and is an easier check for the user.

The “Necessary Adjustment” in D6 plays an accomondating role in adjusting each of the values so that the final calculation reaches target.  For Goal Seek. it is the third parameter “ By changing cell “.  Initially we have no idea what this adjustment should be, and leave it at 0.  At this time, F6 is showing 0.8%, indicating that the necessary criteria haven’t been fulfilled.

Let’s do a Goal Seek now – set F6 to 0 by changing D6 .  If you record this into a macro, say Macro1, you can quickly repeat it by using an assigned shortcut key stroke, or hook it up with a command button for an easy UI.  A lot of people do exactly this as an enhancement to the otherwise awkward model, but it’s not good enough.  Let’s automate it.

Press Ctrl+F11 to bring up the Visual Basic Editor to see the embedding code to Macro1, and you’ll see this line of code:

Range(“F6″).GoalSeek Goal:=0, ChangingCell:=Range(“D6″)

Fair enough.  It’s almost in plain English, and shouldn’t take an VBA expert to understand.  The next step is to copy and paste this line out of Macro1, to somewhere more automatically executable.

Browse to “ Sheet1 ” under “ Microsoft Excel Objects “.  Either type or use the drop-down box to create the following VBA event:

Private Sub Worksheet_Calculate()

This subroutine will be called every time the “calculate” event is triggered on this worksheet.  Depending on whether you set the calculation to Automatic or Manual in Tools->Options. this happens when any formula-based cell changes value to a new input value.  The idea is to paste the Goal Seek code here so it will be called

whenever F6 may change; however, let’s keep it in a separate function for the sake of good programming practice:

Private Sub Worksheet_Calculate()

CheckGoalSeek

Private Sub CheckGoalSeek()

Range(“F6″).GoalSeek Goal:=0, ChangingCell:=Range(“D6″)

In the Visual Basic editor, it should look like this:

Now give it a try!  Change any of the blue input values and you’ll see Goal Seek working by itself to come up with the new adjustment values.  Awesome!  We’re done now in terms of automating a tedious task.

Note: Always save your work before you test out the macro.  depending on the version of your Excel, the repetitive call to Goal Seek may result in a run-time error.  However, the next part of this code will solve the problem.

Have you noticed how slow this enhancement has made your model?  Maybe not, if you have a relatively new computer or a small workbook like the sample file.  But if you add some temporary debugging code to the function, you’ll notice how many times your function is (unnecessarily) being triggered when you change any of the input values:

Private Sub CheckGoalSeek()

Range(“F3″).Value = Range(“F3″).Value + 1

Range(“F6″).GoalSeek Goal:=0, ChangingCell:=Range(“D6″)

Give it another run and, hey:

You just changed one input, and Goal Seek ran close to 2,000 times!  Why.

The subroutine Worksheet_Calculate is triggered every time any function calculates a new value.  There are six cells (in column E as well as cell F6 ) that depend on the changing cell D6 .  As Goal Seek cycles through possible values as the input, all these six cells have to update – and in turn call up more instances of Goal Seek .

Imagine you want to paint a wall red.  Every time you see that the wall isn’t entirely red, you call up a painter to do the job.  As soon as the first painter gets to work, you realize that it’s still not entirely red, so you call up another one.  Even when you’ve got 30 painters to simultaneously working on the wall (if that’s physically possible), there might still be a white spot remaining and you make yet another phone call… chances are, the job would be complete before the last five painters arrive on site.  This algorithm gets the job done, but heck, is super inefficient.  Add a little check to the process and avoid ever calling more than one painter!

Private Sub CheckGoalSeek()

Static isWorking As Boolean

If Round(Range(“F6″).Value, 6) <> 0 And Not isWorking Then

isWorking = True

Range(“F3″).Value = Range(“F3″).Value + 1

Range(“F6″).GoalSeek Goal:=0, ChangingCell:=Range(“D6″)

Adding in the static variable helps flagging whether the Goal Seek function has been employed.  The first time this function is called, it raises the flag and will not release it until the full iteration of Goal Seek completes.  During this time, this subroutine would still be called 2,000 instances, but it would simply bypass all the code and exit.  Try again and F3 increments only once per change of input values, indicating that the Goal Seek isn’t running wild to waste your computer’s resources.  That’s what we wanted, and now we can remove that debugging code.

The first part of the If statement checks whether the solution already exists – i.e. if Worksheet_Calculate is being triggered by some formula updates that are completely irrelevant to this system.  If so, there’s no need to call Goal Seek at all.

Here’s another catch: if you want a robust model, you’ve gotta consider any potential for error.  How about an invalid input value?  Keeping the target overall raise at 5% and changing the max raise to 0% is for sure an impossible scenario, and Goal Seek goes crazy as follows:

We realize that wasn’t very smart and change the max back to 6%… and geez it doesn’t work!  The adjustment factor had been thrown too far off anything reasonable, and now Goal Seek doesn’t know what to do with such an awkward initial guess.  Our model is stuck, and that ain’t cool.  A quick way to fix this is to reset the initial guess every time we call Goal Seek:

Private Sub CheckGoalSeek()

Static isWorking As Boolean

If Round(Range(“F6″).Value, 6) <> 0 And Not isWorking Then

isWorking = True

Range(“D6″).Value = 0

Range(“F6″).GoalSeek Goal:=0, ChangingCell:=Range(“D6″)

It’s a slight waste of resources if educated and reasonable input values can be expected at all times, but definitely a worthy investment if you want to prevent ever getting “stuck”.

Lastly, it’s dangerous to use absolute cell references in macros.  If columns or rows are added or removed, the key cells would have changed locations (from D6 to D7. for example) and the macro will get confused!  The best practice is to name the cells and refer to them by name, like the following:

Private Sub CheckGoalSeek()

Static isWorking As Boolean

If Round(Range(“ GoalSeekCell “).Value, 6) <> 0 And Not isWorking Then

isWorking = True

Range(“ ByChangingCell “).Value = 0

Range(“ GoalSeekCell “).GoalSeek Goal:=0, ChangingCell:=.Range(“ ByChangingCell “)

Source: excel.kingofmath.com

Category: Forex