Having fun with pivot tables
February 15th, 2012 | 
Author:
One of the things that made me smile most at the National Conference was the number of times I overheard people having conversations about the joys of Pivot Tables in Excel.
To add to this, Pat Watt from REACH in Alaska has posted about this on the Certified Trained blog.
You can also look at previous post I have written on the subject by clicking here.
If you have been using Therap for any length of time I can’t emphasize how much you will learn by taking some searches, exporitng them to Excel, and then looking at them in a Pivot Table.
:: Justin ::
Using Excel with Therap :: Working with times
I have been working with Bill from Montana on how to easily run a report calculating which shifts certain injuries occurred on.
As you may know, I love an Excel challenge, and so, Bill and I have been exchanging emails back and forth as we got closer and closer to an elegant solution.
Key to this is getting Excel to recognise times as times. By using a feature known as Text to Columns, that is now really easy.
The other trick here is using nested IF formulas which although it looks really smart and complicated is actally rather simple. If you watch the video above (I would encourage you to double click and open it in YouTube so you can watch it in maximum resolution) you will see how to do it.
The formula is basically saying this:
- If the time is before 7:00am say “3rd Shift”
- Otherwise: If the time is before 3:00pm say “1st Shift”
- Otherwise: If the time is before 11:00pm say “2nd Shift”
- Otherwise: If the time is before midnight say “3rd Shift”
- Otherwise: it can’t be a time so say “No Time”
- Otherwise: If the time is before midnight say “3rd Shift”
- Otherwise: If the time is before 11:00pm say “2nd Shift”
- Otherwise: If the time is before 3:00pm say “1st Shift”
Enjoy
Let me know what you think or other uses you can put this to.
:: Justin ::
Posted in Excel | 
Tags: Developmental Disabilities, Excel, General Event Reports, Montana | 
4 Comments »
Excel just got even cooler :: Sparklines in Excel 2010
As you probably know by now, I am a huge Excel fan.
Well, I just discovered another reason to love it and to keep trying out new things.
I am currently using a beta version of Office 2010. Excel 2010 is very like Excel 2007, but one new feature I have found just blows my mind!
They are mini (cell sized) called sparklines. Say you have a table with a bunch of data in it and you'd like to keep an eye on trends. Well, just drop a sparkline at the end of the column or row.
Take a look:
How cool is that?
:: Justin ::
