September 2009 - Posts
This is a bit of a tricky subject, in that it depends what you mean by reversing. This particular entry will explore reversing blocks of a String, versus an exact reverse of everything.
So, here's an example, how do I get from this:
4189 > 3743 > 2040 > 949 > 195 > 5
To this:
5 > 195 > 949 > 2040 > 3743 > 4189
There's no way to do this natively, in Excel, that I can think of. At least, not without really working your tail off. So, we'll, naturally, roll our own.
We'll make this a two-step process, a UDF to accept the data, and a Sub Routine to sort it. That would appear as the following:
Public Function stringReverse(ByRef strIn As String) As String
Dim tmpArr() As String
tmpArr = Split(strIn)
Call Reverse(tmpArr)
stringReverse = Join(tmpArr, " ")
End Function
Public Sub Reverse(ByRef lParams() As String)
Dim i As Long, tmp As String
For i = 0 To UBound(lParams) / 2 - 1
tmp = lParams(i)
lParams(i) = lParams(UBound(lParams) - i)
lParams(UBound(lParams) - i) = tmp
Next i
End Sub
A couple of things to note.
1) This requires a VBA environment greater than Excel 97, as it uses String Functions that were introduced in VB6.
2) This is a fairly efficient process, you might note that the processing Sub Routine reduces the iteration against the array by 2.
There's a few different ways to implement this. The first is with VBA, e.g.,
Sub foo()
MsgBox stringReverse("4189 > 3743 > 2040 > 949 > 195 > 5")
End Sub
And you might be wondering at this point, why not use VBA's native strReverse() function? E.g.,
Sub bar()
MsgBox strReverse("4189 > 3743 > 2040 > 949 > 195 > 5")
End Sub
Well, strReverse() reverses everything a little too well. Notice what happens with 4189, for example - it becomes 9184 - not quite what we're after.
The other manner in which to use this, which is why we created a UDF as the initial call, is as a Worksheet User Defined Function call, e..g,
=stringreverse(A1)
In addition to the text examples in this blog post, I'm attaching a working example. It includes VBA and is attached with no assurances or warranties - open it at your own risk. Otherwise, have a nice evening.
This entry is based on a thread I responded to, here:
http://www.mrexcel.com/forum/showthread.php?t=76494
Occasionally the question comes up as to how to derive Monthly Internal Rate of Return (IRR) values. The answer is, well, that would depend what format your data is in, in terms of timing.
If your Spreadsheet houses monthly data, IRR() will naturally do this. XIRR() will not, it wil annualize your data.
However, see the attached, it shows examples of flipping back and forth (with both), considering different data constructs. There's no VBA, this is a pure-play exercise in Worksheet Functions.
The 2nd example (Worksheet) is required to be array-entered as Transpose() won't fire, effectively, otherwise.
Also note that multiplying by 12 might serve as a decent gut-check, but won't be accurate, as you're looking at compounding growth, by month - a different mathematical paradigm, it's not linear growth.
Lastly, there's another important concept baked into the first Worksheet - tie your numbers out via different means, until you're really comfortable with various Excel functions and visualizing them.
Hope you had a good, long weekend (for those that had one)!
Today's Blog entry comes from a real-World experience I once had, while purchasing a large asset on a deferred payment schedule. The asset's price, like many large assets, wasn't fixed, but negotiable. So, prior to heading into negotiations, our team wanted to understand what various cash flows would look like if we altered the payment schedule to match a targeted Internal Rate of Return (IRR).
In order to expedite this process, I used an Excel-native utility known as Goal Seek. In fact, in order to further expedite the process, I used Goal Seek in a VBA procedure. To find Goal Seek, pends on your Excel version. Pre Excel 2007, it can be found in the 'Tools' area. Post Excel 2007, you'll want to click into the 'Data' tab, and explore the 'What If Analysis' button.
Goal Seek, Solver's younger brother, is pretty slick. It allows you to change a cell's value based on a calculated value, elsewhere. There is a trick to implementing Goal Seek when you're dealing with small numbers, like percentages. You want to change your calculation options to enable iteration, as desribed here:
Office Online Link
Maximize the number of iterations, to 32767, as mentioned, here:
Office Online Link #2
And crank your max change way down, to a very small number. As the first link notes, this will slow Goal Seek down a little bit, but your results will be much better. I.e., there's not much point in getting the wrong result, faster.
I'm attaching a simplified example of my construct. It contains a simple VBA procedure, and it is provided with no assurances or warranties - if you chose to open it, you do so at your own risk. That VBA procedure is as follows:
Sub BackSolve()
Range("B6").GoalSeek _
Goal:=Range("B8").Value, ChangingCell:=Range("G2")
End Sub
This procedure allows us to bypass the Goal Seek UI dialog, and simply process our request. What does it do? It modifies the year 5 cash payment (outflow), in Range G2, by changing to our targeted IRR in Range B8, which is an input cell (yellow). You'll note as you run this that your calculated IRR, in Range B6, will update to match our request, due to our cash outflows adjusting. This is another good trick to modeling - tieing out your results.
While the example is a simplified one, it can be implemented in larger, more sophisticated models. And with that, Happy Labor Day Weekend!