we dream, we create, we change, we love

Archive for March 28th, 2008

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

Improving My Memory Redux

leave a comment »

“Free space” don’t mean I can get it for free;
      Still I want me a Dylan thumb drive!
Just a few extra gig of his own memory —
      One touch from Bob, and my files come alive
so when I click, they’ll all go, “Babe, it ain’t me!”
bumper sticker [] - whatev

Written by macheide

28 March 2008 at 11:55 am

Posted in whatev