Daily Dose of Excel

DataPig posted about Custom Lists in Excel and his inability to delete them. I agree on all points; I’ve never needed Sun-Sat and we should be able to delete them if we want.

Here’s a macro to list all the Custom Lists to the Immediate Window.

Apparently I’ve used custom lists in the past because there are two there that I made. Number 5 is clearly payroll item types from Quickbooks, but I don’t recall why I would put them in a Custom List. And the last one is the first 9 days of every month of the year; also a complete mystery as to why I would need that.

This week I made the switch from Firefox to Google Chrome. The latest stable release of Chrome supports “extensions” so all those pesky annoyances that kept me away from Chrome in the past can be fixed. My only complaint with Firefox is that I have to restart it a couple of times a day because the memory usage goes through the roof.

After a week of usage, I find that Chrome memory usage continually grows just like Firefox. Chrome seems to open web pages more quickly, but I haven’t actually measured (and I wouldn’t know how). I found one website where the navigation didn’t work with Chrome. I’m not sure what they’re using that’s causing the problem. And I can’t send you there unless you happen to have an HSA account at Mellon Bank.

The two extensions that sealed the deal for me are Type Ahead Find and Reader Background View. Type Ahead Find mostly works like Firefox’s “Search for text when I start typing” feature under Tools - Options - Advanced - General. Without this feature, I would have to quite using the Internet or write my own browser.

One nice thing (for me) about Firefox is about:config where I can fiddle with a lot of settings. For instance, I want links that create diverted tabs to open the background. Chrome doesn’t do that. The only place it really bothers me is in Google Reader. When I’m on a post and press V, I want that diverted tab to open in the background so I can read it later. The Reader Background View extension provides a Shift+V option that does that. It takes some getting used to, but I almost remember to use Shift+V every time now. Almost.

There are a few other annoyances. When I download something, a download bar appears across the bottom of the browser and the only way to dismiss it is to click a button. It should go away automatically after a time or at least give me a keyboard shortcut to dismiss it. The only Delicious extension I could find that had a keyboard shortcut uses Alt+D. I’ve been using Alt+D to go to the address bar since the Second World War II. I’m training myself to use Control+L, but after five days I’m only remembering it about 5% of the time. Yep, 1 time in 20 is not good.

I’m going to give Chrome another week to grow on me, then switch back to Firefox if I’m not sold. I think Chrome will be a fine browser for the vast majority of people. But I’m definitely one of those people who likes to tweak the seemingly mundane settings and Firefox provides that. Is anyone else using Chrome?

Ron de Bruin has an ISO Date page for computing ISO Week numbers, start dates, and end dates. I needed to compute the ISO year, which should just be the YEAR() function wrapped around the end date formula. But I was getting strange results for the first three days of January this year, so I modified the formulas

B2: =DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,1)-MOD(DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,1)-2,7)+(7*(MOD(DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,1)-2,7)>3))
C2: =DATE(YEAR(A2-WEEKDAY(A2-1)+4)+1,1,1)-MOD(DATE(YEAR(A2-WEEKDAY(A2-1)+4)+1,1,1)-2,7)+(7*(MOD(DATE(YEAR(A2-WEEKDAY(A2-1)+4)+1,1,1)-2,7)>3))-1
D2: =YEAR(DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,1)-MOD((DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,1)-2),7)+
         (7*IF(MOD((DATE(YEAR(A2-WEEKDAY(A2-1)+4),1,1)-2),7)>3,1,0))+7)

They’re the same as Ron’s except it uses the WEEKDAY() offset in the DATE() calculations. The purpose of these is to supply a date and return the start of the ISO year, the end of the ISO year, and the ISO year number, respectively.

Have you even attended a really good training session? What about it made it good?

I’d like to develop some training sessions. I get a lot out of preparing and presenting (and I hope the attendees get something too). Rather than developing training that looks like every other session I’ve attended, I thought it would be worth while to take a critical look at what I like and dislike.

One of my recent training experiences was very typical. A presenter read PowerPoint slides for eight hours. I could try to explain this more deeply in my usual acerbic tone, but I think everyone reading has been to this session. Quite simply, it sucks. I have no idea who would like this type of training, but I suspect it’s nobody. It’s just a lazy presenter, I guess.

Last year, Mike Alexander and I did some training in Dallas. I wasn’t enrolled in the class, but I sat it on some of his sessions. They are terrific, but they are that way because of Mike. He can’t hand his materials over to someone else and get the same effect. He has a special ability to speak in a language that the attendees understand and he has a great sense of humor. While I’m not a wet blanket, I’m not even in the same league as Mike. Few are.

A few years ago I went to a training session in Jackson Hole. It wasn’t software training. Rather it was leadership training, or some other warm, fuzzy thing. I knew it was going to be terrible. It was the opposite of terrible. Every activity was thought provoking and engaging. We were drawing on big whiteboards or hiking through the woods or stacking rocks on top of each other. They intermixed the lessons with this physical activity and nobody was ever bored. Not for a second. Not even a little. It would be tough to do the same with an Excel class, but I need to figure out a way to introduce some level of physical activity.

Those are three training scenarios that are swimming around in my head. I’ll use the first one to remind me what not to do. Even if I can’t be as witty and funny as Mike, I can make sure I’m speaking to my audience in a language they understand, like he does. I also find his examples to be very realistic, which is important. Finally I want to be inspired by my Jackson Hole experience to try new things.

Here is what I think I want in a training session:
Road map: I want to know what we’re going to accomplish and on what schedule. I never want to wonder how long this session lasts or what we’ll cover in the next session.
Bite-sized: I want the information broken up so that nothing lasts more than an hour. Even if it’s artificially broken up, I think it’s better. Rainier Wolfcastle says that what we like about music is the notes. I think the rests are equally as important (and easier to play).
Firehose: I want so much information that I can’t possibly remember it all. And I want it delivered at a frantic pace. One problem with classroom training is that you have to move at the speed of the second slowest person in the room. There’s always one person who is in way over his head and pretty much gives up. But that second slowest guy isn’t going to give up and, therefore, he won’t let you get too far ahead. That’s good for him; less good for the lady in the front row who is playing solitaire while the rest of the people catch up. I don’t know how to solve it, but I find myself in the latter person’s role more than the former.

I think there are some givens that aren’t worth discussing. It has to be hands-on, learn as you do, learning. The facilities have to be accommodating. And various other things that we can assume.

I’m going to throw some ideas out there just to see who salutes.

• Stand up desks - would you go to a training session where you stood up half of the day? Where could the session be held that could accommodate that?
• Prep work - To attend, you have to submit some homework. It would ensure that your competence level was appropriate for the class. It could eliminate some of the tedium so the class could focus on important/interesting issues.
• Group projects - Oh boy I hated group projects in college. Remember those? What if I took a 20 person class split it into four groups and gave each group a project. Then after 30 minutes, that group would “teach” their project to the other groups. Hmmm…crowdsourcing training classes.
• Big project - If I was whipping up a training course right now I might do an hour on pivot tables, an hour on sorting, an hour on array formulas, etc. What if instead, I set up a big project, say, preparing the quarterly production reports. I’d set up all the sample data and even have a copy of last quarter’s report handy. For eight hours we prepare this quarter’s report. Along the way, we have to learn how to bring data in from Access, create a pivot table, read in a text file, and even record a macro. Maybe more engaging than if those topics are discussed discretely?

I haven’t really thought through all of those; I’m just brain dumping them to see what stinks and what doesn’t. Tell me what you love and hate about classroom training. Tell me about a really good or really bad session you attended.

I’ve recently rebuilt my computer, which means I get to figure out all those little settings that I’m accustomed to. Since re-installing MZ Tools, the accelerator keys don’t work on any of the dialog boxes. Take the Add Procedure dialog, for instance

With this open, I click Alt+A and instead of actuating the Add button, it opens the Add-in Manager button on the VBE toolbar. This is definitely not the way it worked for me before. Has anyone else experienced this?

Hi everyone,

The release date of Office 2010 is closing in and with this new version we’ll have a new programming challenge. Office 2010 comes in a 32 bit and a 64 bit version.
Especially API function declarations need to be adjusted for the 64 bit environment.
We’ll have to change a declaration like this one:

To:

So how do we know whether a Long needs to be changed to LongPtr, or to LongLong, or maybe even can be left unchanged? I decided it would be useful to gather a list of declarations on my website so we have a one-stop place where we can find the proper syntax for these things.

See:

Declaring API functions in 64 bit Office

If you have some additional API functions I could include, please let me know!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

In Page of Pages in a Cell, Jan Karel uses defined names and Excel 4 Macro commands to return the current page and total pages in a workbook. The problem is that it doesn’t work properly when the cell is repeated (e.g. File - Page Setup - Sheet - Rows to Repeat at Top). So I set about writing a VBA function that would do the same work.

Like Jan Karel’s, mine doesn’t work with repeated cells either. I don’t know why I thought it would. Once I was done with it and tested it, it became pretty obvious that it wasn’t going to work. Unlike Jan Karel’s, mine is really, really slow.

So why post it? Because I spent a bunch of time on it, that’s why. They can’t all be winners.

I had the unfortunate opportunity to learn how cash advances on credit cards work. The particular card I saw was from Capital One, but I think they all work the same. There are three important things to know about cash advances:

1. The interest rate is huge - usually in the mid-20% neighborhood
2. Interest starts on the day of the advance - no grace period like with purchases
3. Payment are applied to the purchases first, then cash advances, even if the purchase occurs after the statement date

I knew about points 1 and 2. I did not know about number 3. Even knowing 1 and 2, I still would not have discouraged this person from getting a cash advance, because I know she pays off her balance every month. Capital One can charge 25% or 50%, what difference does it make. She’ll pay the balance at her next statement and she’ll have some small interest due. Except for #3. Number 3 ensures that she will be paying interest on that cash advance for the rest of her natural life.

I should mention that I still discourage taking cash advances on credit cards. Even without #3, it’s better to get your cash in other ways. But if you’re out of town and lost your ATM card and can’t get to a bank branch - in other words it’s a freakin’ emergency and there’s no other way - then I would have said ‘Go for it’. Now I know that there is no situation so dire that you should take a cash advance on your credit card. Go find a pawn shop and borrow money from a loan shark - it’s cheaper.

Based on the three points above, I was wondering how long it would take to pay off the cash advance. The interest on each statement is not that big. If you’re the type of person who carries a balance, you probably wouldn’t even notice that some of the finance charge is coming from a cash advance. Thankfully, this person has zero finance charges every month, so it was pretty easy to spot.

Here’s the scenario: A person spends $100 per week on his credit card and pays the balance every statement. Statements cut off on the 18th of the month and payment is due on the 28th. On January 15th, in addition to the normal $100 per week, he takes a $500 cash advance. How long will it take to make the cash advance balance zero? The answer is never. Under these conditions, the cash advance balance will never be zero. Let’s check out the model.

Column B: The total payment made - always the statement balance. On 1/28/2010, $601.02 is paid because that’s the balance on the statement date.
Column C: Purchases made - $100 every 7 days. A little contrived, but what can I do.
Column D: The amount of column B that the credit card company applies to purchases. On 1/28/2010, they apply the payment to the purchase balance on 1/27/2010 - not the purchase balance on the statement date.
Column G: The amount of column B that the credit card company applies to cash advances. Even though the whole $501.02 of cash advances from the statement date was paid, only $401.02 is applied because there were purchases in the interim.
Column H: The daily interest on cash advances. There is no interest on purchases because we pay the whole balance every month. The interest rate is in H1.

Note that I’ve hidden some rows in the above image. The next payment, 2/28/2010, is applied 100% to purchases and the cash advances balance just keeps growing. Ten years later, the $502.17 payment reduces the cash advance balance to $100.70. Ten years later! In the mean time, the total interest paid was $260.11 on a $500 cash advance. Pathetic.

Everything in this model is pretty solid. I don’t cover the 3% gouge he got when he took the cash advance. And the $100 per week is an average spending pattern that’s been rigidly applied. I wonder what would happen if I made the spending habits a little more random.

The formula in C4 is =IF(WEEKDAY(A4)=6,100,""). Let’s see what happens when I change it to =IF(RANDBETWEEN(1,7)< =3,RANDBETWEEN(25,40),"").

Here are the results for 10 recalcs

In a lot of cases, the only way to get rid of the cash balance is to stop using your card for a while.

You can download CashAdvance.zip.