Jacob J. Walker's Blog

Scholarly Thoughts, Research, and Journalism for Informal Peer Review

Archive for September, 2016

Using Match and Index on a Pivot Table: Sometimes Excel has better Performance with “Old School” Formulas

without comments

I am a huge fan of pivot tables, and often use them to help transform raw data into a different table, by “mirroring” data from a pivot table, where I have a table to the left of the pivot table, and I want to have the table show the same value the pivot table has, or possibly transform it with a formula.  Often you can just choose the cell reference that the pivot table has and you are done…  But sometimes the pivot table may have multiple columns, that vary, and you wish to do some sort of lookup on the pivot table…  Traditionally, to do this, it would require creating a GETPIVOTDATA formula.  These have always been a bit complex, and now with PowerPivot being built-in to Excel 2016, they are even MORE complex.  This is why some people choose to use cube functions instead, which can now be done on PowerPivot tables….  But I have tested both of these, and they are very slow when you have a lot of rows.   So, I have gone back to an old school method of using the MATCH function (with absolute references) to determine which column a particular heading is in, and then use the INDEX function to select the value from that heading.  This old school trick turns out to be a LOT more efficient than using GETPIVOTDATA, and thus I thought I should share this with any other Excel nerds who read my blog.

Written by Jacob Walker

September 20th, 2016 at 6:59 pm

SIS Review: PowerSchool – The U.S. Market Leader, but not Perfect

without comments

When Highlands Community Charter School recently was looking to switch to a new SIS, we ultimately chose PowerSchool, and for the past month, I’ve been working intensely to migrate from our previous system (School Pathways) to PowerSchool.  Overall this process has been smooth, but it has also highlighted to me some clear problems that PowerSchool has, and why one shouldn’t only choose a product because it has the most market share.

Read the rest of this entry »

Written by Jacob Walker

September 3rd, 2016 at 11:59 am

SIS Review: Aspen – Great for Large School Systems

without comments

My first review of various SISes is that of Aspen by Follett.  When Highlands Community Charter School recently was looking to switch to a new SIS, Aspen was in our top 3 choices, and only barely lost out to PowerSchool.  During our review process, I had the chance to look at a sandbox system (demo) of their product for about a week, and we asked a lot of questions to their sales rep, Dylan Holcomb.   As a matter of disclosure, I should note that Dylan was a friend from high school, but I think this review is fairly objective, as there are clearly things I don’t like about the product, along with many things I really like.  I have written about Aspen previously also.

Read the rest of this entry »

Written by Jacob Walker

September 2nd, 2016 at 11:59 am

A Series of Reviews of Student Information Systems (SIS)

without comments

For over 10 years I’ve worked with about 7 different Student Information Systems (SIS), too one degree or another.  Since I have generally worked with these both from the back-end (database administrator and institutional researcher) as well as from the front-end (teacher and school administrator), this puts me in a fairly unique position to be able to compare the advantages and disadvantages of each system.  Thus, I thought I should share my thoughts on the different SISes that I have had experience with, in order to help schools and school systems in choosing one.

Read the rest of this entry »

Written by Jacob Walker

September 1st, 2016 at 11:59 am