Tiered Commissions and Counting Zeros

I was writing some formulas for a tiered commission calculation recently that I thought I should post. But beyond just what the formulas do, it reminded me that I’ve never shared my ‘counting zeros’ opinion, so I’m wrapping that in with this post too.

You have a commission structure where you pay your salesmen 5% for every sale. If the sale is a particularly large one, you pay them a bonus commission – 8% for the portion of the sale that’s over $20,000. But you don’t want your salespeople getting so rich that they have enough money to quit. Nor do you want them to get an unfairly huge commission on an unusually large sale. So you have a third tier that reduces their commission to 1% for the portion of the sale that’s over $100,000.

Let’s look at the formulas for column H.

    H4        =MAX(MIN(2*10^4,H2)*0.05,0)
    H5        =MAX(MIN(8*10^4,H2-2*10^4)*0.08,0)
    H6        =MAX(0,H2-10^5)*0.01
  • In H4, Take the smaller of $20,000 and whatever is in H2 and multiply it by 5%. Then take that larger of that result or 0, just in case there’s a negative sale in there. (5% of $20,000 = $1,000)
  • In H5, Take the smaller of $80,000 ($100k less $20k) and the amount that H2 exceeds $20,000 and multiply by 8%. Then take the larger of that result or zero. (8% of $80,000 = $6,400)
  • In H6, Take the larger of 0 and the amount less $100,000 and multiply by 1%. (1% of $1,000 = $10)

The MIN part of the formulas in H4 and H5 make sure you don’t pay more commission on that tier than you should. The MAX part returns zero when the calculation goes negative.

About counting zeros. You may have noticed that I use terms like 2*10^4 to represent $20,000. I’m a big fan of commas, but I can’t use them in formulas (they’re kind of important for separating arguments). I picked up using scientific notation in formulas from a scientist I know and I love it. No more do I have count the zeros in

=IF(A1=25000000,600000,8000000)

to know if it’s 25 million, 2.5 million, or 250 million. Instead I write

=IF(A1=25*10^6, 6*10^5, 8*10^6)

An even better answer is to put those values in cells and refer to the cells. When they’re in cells, I can format them and use commas to count the zeros. But let’s face it, sometimes we hardcode numbers in formulas. And when I do, I’ve been using this method for larger numbers and, after a small adjustment period, it’s been great.

AutoHotkey in the VBE

I downloaded AutoHotkey recently for something not Excel related. Well, it was kind of Excel related so I guess I’ll tell the story. I’ve been using the Save As feature of my Kwik Open add-in and really enjoying the lack of folder navigation. But it has sewn the seeds of discontent. When I’m doing a Save As in other programs, like a PDF viewer, I don’t get the keyboard love. I got AHK so I could quickly jump to some of my favorite folders without leaving the keyboard.

But then I got to thinking about other ways I could use this tool. One of my pet peeves about the Visual Basic Editor (and there are many) is that typing “endif” will auto-expand to “End If”, but typing “endwith” just sits there like an insolent child. I thought maybe I could fix that with AHK. And I could. But that wasn’t enough. Check out these two AHK scripts I wrote.

#IfWinActive ahk_class wndclass_desked_gsk
::with::With{Enter}End With{Enter}{up 2}{end}
:*:then`n::Then{Enter}{Enter}End If{Enter}{up 2}`t

The first line tells it to only work in the VBE window and it comes with a windows spy utility to find out the ahk_class of whatever window you like. The second line monitors for when you type the word “with”. When you do that, it replaces it with With and End With – even better than expanding endwith. Then it moves the cursor back up to the With line so you can continue coding. If you type “with” inside a comment, it’s trouble. So that’s something to work on.

The third line looks for when you type “then” and press Enter. It replaces it with an If block and puts the cursor in the middle, ready for more code.

Watch the video to see it in action. I’m not sure why the video is blurry at the start nor do I know how to fix it.

As you might guess, I’m going to love this.

Excel Function Bible released

Hi all

Together with Norman Harker I add this page to my website :  Excel Function Bible

The Excel Function Bible is aimed at providing meaningful and practical help in finding and implementing the 468 Excel functions. It replaces and adds significantly to the help users can call for whilst building or using Excel workbooks. To make it easier to navigate through all the Excel functions example workbooks(using a nice Ribbon interface) It will also be available as a add-in soon. But for now we (Norman Harker and me) have upload 468 Example workbooks to my site. There is one example file for each worksheet function. This is an evolving project. We would appreciate users providing us with additional examples of typical or extremely useful applications of the functions especially in conjunction with other functions. All examples provided will be appropriately and prominently acknowledged. Similarly, we don’t pretend to be perfect and would appreciate reports of any bugs or errors that may have crept in.

You can download separate function files in the classification pages or download all the 468 workbooks in one time on the index page.

Regards Ron de Bruin

http://www.rondebruin.nl/index.htm

 

Avoiding Date Conversion When Pasting an HTML Table

From Get Data From a Website…, MD comments:

However, the info in the table I copy is formatted ## / ## whereby # represents a number.
So when it tries to paste 10 / 10 in the worksheet for example, it auto-changes to 10/okt in my sheet.

This is a common problem that’s hard to solve. There’s no setting I can find in Excel that tells it to stop converting things that look like dates into dates. Since I’m reading in the data and putting it in the clipboard, I can just message the data before I do it. That’s not so easy when you just want to copy and paste, but if you’re using code, you may find the technique useful.

In the above linked post, I automated Internet Explorer to login to a website. I don’t automate Internet Explorer any more, preferring XML instead. But it doesn’t matter which you use. It all ends up in an HTMLDocument, so it’s the same from there. Also, this example doesn’t log into a webpage. It uses Contextures’ Sample Data.

Sub GetTableNoDateConversion()
   
    Dim xHttp As MSXML2.XMLHTTP
    Dim hDoc As MSHTML.HTMLDocument
    Dim hTable As MSHTML.HTMLTable
    Dim hCell As MSHTML.HTMLTableCell
    Dim doClip As MSForms.DataObject
   
    'Get the webpage
    Set xHttp = New MSXML2.XMLHTTP
    xHttp.Open "GET", "http://www.contextures.com/xlSampleData01.html"
    xHttp.send
   
    'Wait for it to load
    Do: DoEvents: Loop Until xHttp.readyState = 4
   
    'Put it in a document
    Set hDoc = New MSHTML.HTMLDocument
    hDoc.body.innerHTML = xHttp.responseText
   
    'Find the third table
    Set hTable = hDoc.getElementsByTagName("table").Item(2)
   
    'Fix anything that looks like a date
    For Each hCell In hTable.Cells
        If IsDate(hCell.innerText) Then
            hCell.innerText = "'" & hCell.innerText
        End If
    Next hCell
   
    'put it in the clipboard
    Set doClip = New MSForms.DataObject
    doClip.SetText "<html>" & hTable.outerHTML & "</html>"
    doClip.PutInClipboard
   
    'paste it to the sheet
    Sheet1.Select
    Sheet1.Range("A1").Select
    Sheet1.PasteSpecial "Unicode Text"
   
    'Make the leading apostrophes go away
    Sheet1.Range("A1").CurrentRegion.Value = Sheet1.Range("A1").CurrentRegion.Value
   
End Sub

Once I get the table into an HTMLTable object, I loop through all the HTMLTableCells to see if any of them looks like a date. If so, I put an apostrophe in front of it. The apostrophe is the Excel way to say “No matter what I type next, assume it’s text”. Except when you’re pasting special as Unicode Text. In that case, it doesn’t hide the apostrophe like it should. So the last line of the code is the equivalent of pressing F2 and Enter all the say down the column and forcing Excel to reevaluate its decision to ignore that apostrophe.

NOT learning from my Errors

I was writing some formulas today that need to return TRUE if a search term appears in a cell, and FALSE otherwise. For instance, I wanted to know if FOO appeared in FOOBAR or not.

Originally I was using FIND, along these lines:
=NOT(ISERROR(FIND(“FOO”,”FOOBAR”)))
=TRUE

But then had some issues with case that screwed things up:
=NOT(ISERROR(FIND(“Foo”,”FOOBAR”)))
=FALSE

Given that for my purposes FOO was as good as Foo, I decided to replace the FIND function in this with the SEARCH function, on account of SEARCH being an insensitive bastard. (Can I say that here? No? Oops…sorry!).

So I manually edited the formula, and replaced FIND with SEACH. Then cut and pasted that formula in lots of other places. Then did lots of analysis with the output. Without learning from the error of my ways:
=SEACH(“FOO”,”FOOBAR”)
=#NAME?

Whoops…that’s not how you spell SEARCH! Why didn’t you tell me Excel? Oh…because of this:
=ISERROR(SEACH(“FOO”,”FOOBAR”))
=TRUE

Which don’t mean there ain’t no FOO (or Foo) to be found. Rather it means “Yes, I am in fact returning an error – thank you for asking – because I have no idea what this damn SEACH does.”

Murphy’s law: I never noticed that I’d screwed it up until right at the very end of my days work.

Still, not noticing till the very end is better than not noticing.

Or as Excel would put it:
=NOT(ISERROR(SEARCH(“not noticing”,”not noticing till the end is better than not noticing.”)))
=TRUE

Learning from my Errors

There’s an annoying bug in VBA whereby if you’re trying to change the .visible status of a PivotItem, and if the PivotField had a number format set to General, and if you live in New Zealand, then you’re out of luck:

Unable to set visible property

If you live in the US however, you’ll be fine. No error for you.

Don’t believe me? Either change your Windows region to New Zealand and run this code, or swing down to my place and see it for your own eyes. Here’s where you’ll find me:

247 Rintoul Street

(Aside: Check out those awesome ocean views. Why if it wasn’t for that annoying continent-sized lump of Uranium and Gold Ore off to the West, we’d pretty much have 365 degree views of the entire Pacific. Fortunately they’re busy bulldozing that annoying outcrop and shipping it off to uranium reactors and jewelery stores across the globe. So we should have a completely unfettered view in 2 billion years or so).

Ok, so this issue isn’t just an issue for New Zealanders…it actually affects any place where you haven’t got your Windows ‘region’ set to US, with New Zealand being the only place where I’ve actually encountered such egocentric behavior to date. (I don’t get out much. Or rather, they don’t let me out much. Or rather they make it clear that I can go out, but I can’t come back in.)

According to IronyAaron in the comments at this thread:

When VBA recognizes the dates in the pivot cache, it reads the US version after parsing although the item is read as a locally formatted string. This therefore causes VBA to fail when recognizing Date variables.

Bummer! So write some code that filters PivotItems, and you might find that non-US users have issues, unless they change their regional settings in Windows to US beforehand. Good luck with that.

This nasty bug caused quite a bit of workaround in my FilterPivot routine. I used to do this horrible check on every single item in a potentially exhaustively long list of PivotItems in order to avoid the possibility of an error caused by this unlikely combination occurring:

If Not IsNumeric(Pi.Value) Then
    'We need the Not IsNumeric bit above because VBA thinks that some decimals encased in strings e.g."1.1" are dates
    If IsDate(Pi.Value) Then
        If Not bDateWarning Then
            On Error GoTo ErrHandler
            Err.Raise Number:=997, Description:="Can't filter dates"
            On Error Resume Next
        End If
    Else: Pi.Visible = True
    End If
Else: Pi.Visible = True
End If

But prompted by Jerry Sullivan’s comment I found that this was only an issue for non-US regional settings, and that this issue is now fixed in Excel 2013. (Thank you, Microsoft. But why the heck didn’t you tell me you’d fixed it?)

So now I can just do this:

pi.Visible = True

Or rather, I could just do that if everyone had Excel 2013. But they don’t. So I can’t. I still have to somehow catch this error. And as written above, my code rather inefficiently looks for possible trouble caused by a combination of things that is probably unlikely to occur. (I mean, how many people would dare to have their Windows region set to a non-US region while trying to filter a PivotItem that happens to be a date in a PivotField that happens to have a General format?) All that preemptive error checking can’t be good for business.

The error of my ways?

I’m sure you’ve already seen what looks to be like the error of my ways… why bother checking for errors just so I can avoid them? Why not embrase them: just plow ahead, and if the s#!t hits the fan, just deal with it. Something like this:

On Error Goto Errhandler
pi.Visible = True

'some other code

ErrHandler:
If Err.Number <> 0 Then
    Select Case Err.Number
    Case 1004 'Error likely due to bug outlined at http://dailydoseofexcel.com/archives/2013/11/09/a-date-with-pivotitems/
        If Not IsNumeric(pi.Value) And IsDate(pi.Value) And pfOriginal.NumberFormat = "General" Then 'Yep, definately that 'Bug
            'Note that we need the Not IsNumeric bit above because VBA thinks that some decimals encased in strings e.g."1.1" are dates
            i = i + 1
            ReDim Preserve strDateItems(1 To i) 'Record the offending things to an array, so we can warn the user about the specific items
            strDateItems(i) = pi.Value
        End If
    Case Else 'Some other error code here
    End

Great: now that bug fix code in the Errhandler only gets fired up in the rare event that it is actually triggered. Sure beats neurotically checking each and every PivotItem to see if it might cause an issue.

Unfortunately in this particular case the code snippet in question sits within a larger loop, and the code immediately before needs to have an On Error Resume Next statement applied. That’s because in order to work out whether a PivotItem should be hidden, I’m adding it to a Dictionary object that also contains my desired filter terms, in order to see if it matches any of those filter terms. Which looks something like this:

On Error Resume Next
For Each pi In pfOriginal.PivotItems
    dic.Add pi.Value, 1 'The 1 does nothing
    If Err.Number <> 0 Then
    pi.visible = true
    ...

So I’d need to put an On Error Goto Errhandler before the pi.Visible = True bit so that my bug fix code in Errhandler would get triggerred, and an On Error Resume Next bit after it, so that the Dictionary test occurs for the very next item. And those will get executed for every single PivotItem – which kind of defeats the efficiency ‘dividend’ of putting my handling code within Errhandler. So I figure I might as well just do this:

pi.Visible = True
If Err.Number = 1004 Then 'Error likely due to bug outlined at http://dailydoseofexcel.com/archives/2013/11/09/a-date-with-pivotitems/
    If Not IsNumeric(pi.Value) And IsDate(pi.Value) And pfOriginal.NumberFormat = "General" Then 'Yep, definately that 'Bug
        'Note that we need the Not IsNumeric bit above because VBA thinks that some decimals encased in strings e.g."1.1" are dates
        i = i + 1
        ReDim Preserve strDateItems(1 To i) 'Record the offending things to an array, so we can warn the user about the specific items
        strDateItems(i) = pi.Value
    End If
End If

Two steps forward, one step backwards.

Maybe I shouldn’t have that On Error Resume Next in there in the first place…maybe I should catch errors from the Dictionary.add in Errhandler too, or even do the dictionary check in another procedure – something that Dick mentions here. Anyone got any advice here?

All I know is that this is a lot of work-around for a combination that is pretty unlikely, but potentially fatal to someone’s analysis.

Wonky Keyboard Shortcut

Check out the keyboard shortcut for the Reapply button.

I don’t think I’ve ever used that button. If I need it, I think I’ll just use the mouse. Even I have limits.

Find Your Dropbox Folder in VBA

Here’s a function to get the location of the Dropbox folder:

Public Function DropBox() As String
   
    Dim DropboxHostFile As String
    Dim DropboxHostFileNumber As Long
    Dim Base64EncodedPath As String
    Dim TempXMLDocument As MSXML2.DOMDocument60
    Dim Base64XMLNode As MSXML2.IXMLDOMElement
   
    DropboxHostFile = Environ("appdata") & "\Dropbox\host.db"
    DropboxHostFileNumber = FreeFile
   
    Open DropboxHostFile For Input As DropboxHostFileNumber
    Base64EncodedPath = Input$(LOF(DropboxHostFileNumber), DropboxHostFileNumber)
    Close DropboxHostFileNumber
   
    Set TempXMLDocument = New MSXML2.DOMDocument60
    Set Base64XMLNode = TempXMLDocument.createElement("b64")
    Base64XMLNode.DataType = "bin.base64"
    Base64XMLNode.Text = Split(Base64EncodedPath, vbLf)(1)
   
    DropBox = StrConv(Base64XMLNode.nodeTypedValue, vbUnicode)
   
End Function

According to Reinaldo there is a host.db file in the Roaming directory that stores this information. The Environ("appdata") function returns the Roaming directory in Win7. I open host.db and read in the text. Then I create a new XML document, thanks to Tim Hastings, to write in the Base64 text and read out the byte data that is converted to Unicode. There’s two lines in my host.db file, so I split on vbLf and only use the second line.

Here’s what it would look like if I wrote this function and didn’t want to poke my eyes out afterward.

Public Function DropBox() As String
   
    Dim sFile As String
    Dim lFile As Long
    Dim sPath As String
    Dim xDoc As MSXML2.DOMDocument60
    Dim xNode As MSXML2.IXMLDOMElement
   
    sFile = Environ("appdata") & "\Dropbox\host.db"
    lFile = FreeFile
   
    Open sFile For Input As lFile
    sPath = Input$(LOF(lFile), lFile)
    Close lFile
   
    Set xDoc = New MSXML2.DOMDocument60
    Set xNode = xDoc.createElement("b64")
    xNode.DataType = "bin.base64"
    xNode.Text = Split(sPath, vbLf)(1)
   
    DropBox = StrConv(xNode.nodeTypedValue, vbUnicode)
   
End Function

Visualizing Podcasts

I like podcasts. I listen to them almost every minute I’m behind the wheel. They’re mostly technology related, so if my wife’s in the car sometimes I have to turn on music or switch to Unprofessional or Roderick on the Line (a couple of great podcasts that are a little less techy). As I was listening to the most recent Stackexchange podcast, I thought it would be interesting to visualize the conversations in three of my favorite podcasts.

Accidental Tech Podcast: Casey opens and closes the show. He gets some words in in the middle, but they’re two small to register at this scale (unless the topic is vinyl records). Marco does the ad reads and discusses coffee and headphones and John does the rest.

Roderick on the Line: Merlin primes the pump, but once John gets rolling there’s no stopping him.

Stackexchange: Sometimes they have a guest and it’s slightly less chaotic.

Of course these charts are made in jest and do not reflect actual data or any ill will toward the podcasters. They are three of my favorite podcasts and I never fail to listen to miss them. If you like podcasts, I recommend them (and even if you don’t).

Pop quiz

Question One

You want to calculate a running (i.e. cumulative) total of the Data column.
Which of these formulas should you put in B2 and drag down, and why?
=SUM(A$2:A2)
=SUM(A2,B1)

Question Two

You want to calculate a running (i.e. cumulative) total of the Data column, and subtract 1 from it.
Which of these formulas should you put in B2 and drag down, and why?
=SUM(A$2:A2)-1
=SUM(A2,B1)-1

—edit—

If you’re ambivalent as to the approach you would take, download and try out a slightly revised thought experiment in the attached file. Then you won’t be ambivalent.

Pop-quiz_20140502-V6

This has a more realistic data set, where instead of subtracting 1 from the cumulative total, you want to subtract a varying list of cumulative expenses, so you can work out the cumulative net profit:
Pop Quiz v5

Take it that inserting/deleting rows is not an issue (assume the structure is locked down).

Note that this is intended to be a thought experiment/illustration about a common approach which happens to be very resource intensive, and a better solution that works just fine provided you don’t do anything else within that formula but calculate a cumulative total.

But feel free to post alternatives.