aftermath

we dream, we create, we change, we love

Archive for the ‘excel hero’ Category

An Actuary without Excel Is Like…

leave a comment »

Wall Street without the Journal? K Street without a lobbyist? A tax bill without a favor for a special interest?

No, it’s way more intrinsic than that.

I was born an actuary and will die my last death an actuary. And no matter what marketplace the designers believe they created Excel for, I will always believe it to have been God’s gift to my actuarial soul.

More Excel . . . more . . . more . . .

Written by macheide

1 December 2017 at 6:52 am

Posted in excel hero

Tagged with , ,

Chart Selector

with one comment

Long long standing ovation to Pointy Haired Dilbert for the cute little Excel image hack permitting display of a chart selected from a set of charts, and a nod to Lifehacker for passing the tip along. Coincidentally, I’d been itching to roll up my sleeves toward developing something along the same lines the past several weeks, since adding an APM chart to the head tab of my favorite Excel workbook has filled out that screen’s already cluttered real estate, although I’ve dozens of other charts I would like to have at my fingertips, if only I had space to display them all easily. This chart selector does the trick quite nicely, thank you very much.

Since first applying it to my own fave workbook yesterday, I’ve already tweaked the original tip around the edges, the better to serve my own particular needs, but in ways that ought also be as useful for other Excel workbooks. The main issues I was facing being two-fold:

  1. Immediately Refreshed Display Without Recalculation – I gave up automatic recalculation for my fave Excel workbook almost from its launch just over a year ago; and it takes several minutes on a fast machine any time I manually recalculate. (Don’t even bother to ask why. Suffice it to say I’ve got information at my fingertips for which I doubt anyone else in the world has easy access, and I want it all too close at hand to trim it down for speeds’ sake.) Anyway, nice as the original graph selector tip is, it won’t immediately refresh a graph change without either recalculation of the entire workbook, or at least recalculation of an intermediate cell (which was the temporary approach I went to for yesterday’s first pass at the trick).
     
  2. Handling a Growing Set of Charts – The only reason I had reached a plateau on graph creation was, as noted, limited front-screen real estate (although occasionally I’ve created ad hoc charts for various aftermath posts). The =IF structure of the original chart selector tip makes the addition of new charts to the selector set more laborious than necessary.

The revisions I’ve made to the trick deal with both these issues in a tight, elegant little formula. I place the names of my charts in column A of my workbook’s “Charts” tab, starting in row 1, naming that vector of chart names ChartList, running down to the final chart. The charts themselves are in column B of the “Charts” tab, each in cells sized as directed in the original chart selector tip. Cell AD1 on “LBR” – the header tab of my favorite Excel workbook – contains a dropdown list pulling in the names from ChartList; while cell AD2 of the same tab contains the chart-carrier picture with the hack described in the original tip. (Yes, that’s not a typo – for reasons irrelevant to this discussion, column AD is at about the center of the northwestmost screen of the header tab for my fave Excel spreadsheet.)

In lieu of the original tip’s =IF formula, I now use the following formula –

   =INDIRECT("'Charts'!B"&TEXT(MATCH(LBR!$AD$1,ChartList,0),"#"))

Now anytime I select a different chart name from the dropdown list on my fave Excel workbook’s header tab, the chart is instantly refreshed to the new selection, without requiring recalculation of so much as a single cell of the rest of the workbook. Meanwhile, anytime I wish to add a new chart, I simply insert a row within the ChartList range on the Charts tab, add the name of the chart in column A, slide the chart into column B (usually simply by copying one of the other charts, then revising the source data ranges), and presto, my chart selector is automatically updated, without having to go through the formula revision that would be necessary under the original tip.

bumper sticker [www.internetbumperstickers.com] - excel hero

Written by macheide

14 November 2008 at 3:09 pm

Posted in excel hero

FY Shift Trigger

leave a comment »

For my personal hobby of studying pension plan data, I’ve previously always used the first option described in last night’s FY Shift Quiz: each calendar year bucket includes all data for any employer with a fiscal year ending in that calendar year. But I’ve always been sympathetic to the obvious concern that the significant group of employers with fiscal year ending January 31 (if not during the first quarter or even the entire first half of a calendar year) tend to be more representative of the preceding calendar year. And although I’ve sometimes made manual adjustments by using filters to isolate fiscal year subsets of data, for several years I’ve wanted a trigger with a variable to make it easier for me to shift from one basis to another for selecting datasets on the basis of fiscal year ends.

Last night, after verbalizing what I wanted the trigger to do, then relaxing and letting the whole issue drop completely out of my head, the way to do it came to me visually, as an image that I could draw in the air, during the middle of an Elton John song. Slept on it without dreaming of it, then woke this morning and programmed it into my spreadsheet in one clean series of three pretty simple steps, no problems, maybe 15 minutes of work at most (mostly just fine-tuning formats and such), works quite fine! Read the rest of this entry »

Written by macheide

7 June 2008 at 7:13 am

Posted in excel hero

Filter Tip

leave a comment »

I use Alt-D-F-F to give me Excel’s data autofilter muscle so frequently, many of my spreadsheets reserve the 20th row, my final header row, for Excel’s filter dropdown boxes. Most of the first dozen rows then carry various Excel SUBTOTAL() applications and analysis, for instance SUBTOTAL(9,array) giving me the filtered sum of the array, along with a followthrough row or two comparing that sum with a prior year sum, and so on.

But while the power of having filtered SUBTOTAL functions can be quite powerful, Excel gives only gives us a Microsoft dozen (11) SUBTOTAL functions. So although SUBTOTAL(1,array) will give me an average of the filtered array, I have no SUBTOTAL function corresponding to a median. Thus, if I construct an array (e.g., pension plan discount rates for every single pension plan under the sun), filter that array (e.g., isolate pension plans sponsored by S&P 500 companies), then MEDIAN(array) won’t give me the median of just the S&P 500 set, but rather will still look behind the filter and show the median of the entire universe.
Read the rest of this entry »

Written by macheide

28 March 2008 at 12:40 pm

Posted in excel hero

Classic Columns

leave a comment »

Excel can easily count which column a cell is in via the simple built-in formula –

=COLUMN()

Read the rest of this entry »

Written by macheide

14 March 2008 at 7:35 am

Posted in excel hero

Spreadsheet Hash

leave a comment »

I’ve worn my brain down on the simplest but most tangled spreadsheet. And I have to run off here on yet another version of it, another version that once again will necessitate virtually re-building everything from scratch. The building blocks of our calculations in this one remain the same from version to version; but the way those blocks are stacked up gets so complicated, it’s not worth trying to build on earier versions. At the same time, for comparison against the other versions’ results, it is helpful to have those earlier calculations running concurrently through each of several scenarios being thrown at our model.

As good a place as any to salt the spreadsheet rather heavily with hash symbols.

Read the rest of this entry »

Written by macheide

12 March 2008 at 12:43 pm

Posted in excel hero

Excel Hero

leave a comment »

Call it actuarial arrogance if that it is, but I’ll go at far as claiming to being an “Excel hero.”

“Excel god,” as I’ve been tagged by more than one individual recently? No, and any good Excel hero does know the difference as easily as how it doesn’t take a classics major to know the difference between a Zeus and a Theseus. In Microsoft Office Excel mythology, my own belief is that a principal dividing line gets drawn between the gods who fluidly use VBA in even the simplest of spreadsheets versus the heroes who can take heavy-duty spreadsheets beyond the realms of reality without VBA. By that standard, at best I’m one of the lesser of heroes.

But for whatever it’s worth, this aftermath category will be used to document some of my Excel explorations and exploits, whether they be divine, heroic, extraordinary, merely mortal, or even downright damned.

bumper sticker [www.internetbumperstickers.com] - excel hero

Written by macheide

11 March 2008 at 5:57 pm

Posted in excel hero