Jonathan had an excellent comment in the Principal Payment post. Why create an array formula when the Analysis Toolpack already has the CUMPRINC function. Well, I said I couldn’t find a function to do it, and I wasn’t lying. That doesn’t mean one doesn’t exist, only that I couldn’t find it.
The reason I couldn’t find it was because I don’t have the Analysis Toolpack Add-In loaded. Every time I come across an ATP function, I think, “I don’t want to load that Add-In for one function”. So I don’t. But if I would just load it, then the next five times I need an ATP function, I wouldn’t have to say that.
So the question of the day is:
| Do you have the Analysis Toolpack Add-in Loaded? | |
| Yes, I use many functions from the ATP | |
| Yes, I use one function from the ATP | |
| No, I’ve never needed an ATP function | |
| No, I need an ATP function occassionally, but don’t want to install it | |
| I’ve never heard of the Analysis Toolpack | |
|
|
|
| Free polls from Pollhost.com | |
Sorry to be pedantic, but for some reason Microsoft left out the “c” in “ToolPak”.
Jonathan: You’re right! Boy, those guys are the model of efficiency.
If you build a model that includes formulae from the ATP, I recommend that you include a test for it on the first sheet. A formula like:
=NOT(ISERR(RANDBETWEEN(0,1)))
will return true/false depending on whether the toolpak is installed. This helps new users to understand why the model isn’t working if they haven’t got it.
I avoid ATP functions like the plague. When I develop I want my stuff to work in all versions and all languages.
Excel files using ATP functions cannot be used in a different excel ‘language version’ as they do not autotranslate and will result in #NAME errors.
Does anyone have an idea to solve the problem of the language version?
I created a file (using the ATP) in an English excel version but the final user use a Flemish excel version.
I’m mainly using the formulas worday, networkdays.
Change the excel version is not a solution.
(excel 2002 sp2)
Hi! Maybe this helps: The (lokal) Add-In has a worksheet with a translation list. For XL2003 it works like this:
(1) reference in VBE to the “funcres” add-in (i.e. the analysis toolpack).
(2) The following function looks up the local function name:
‘–snip–
Function LocalATPName(strUSATPName As String) As String
i = 0
On Error Resume Next
LocalATPName = “” ’empty string in case of error
With funcres.ThisWorkbook.Worksheets(“RES”).Range(“US_Funcs”)
LocalATPName = .Cells(Application.Match(strUSATPName, .Cells, 0), 1).Offset(0, 5).Value
End With
End Function
Sub TestIt()
MsgBox LocalATPName(“DEC2BIN”)
End Sub
‘–snip–
I’d dont think, they’ve changed anything on this sheet in the last versions. So it should also work in XL2002.
Greetings from Vienna, Austria,
-Robert-
Hi again!
I’ve just found a name “Func_table” on this sheet. Therefore this solution is slightly more elegant:
‘–snip–
Function LocalATPName(strUSATPName As String) As String
i = 0
On Error Resume Next
LocalATPName = “”
With funcres.ThisWorkbook.Worksheets(“RES”).Range(“Func_table”)
LocalATPName = .Cells(Application.Match(strUSATPName, .Columns(1), 0), 3).Value
End With
End Function
Sub TestIt()
MsgBox LocalATPName(“DEC2BIN”)
End Sub
‘–snip–
Greetings,
-Robert-