Your One Stop for Excel Tips & Solutions

MrExcel - Photos of MrExcel
Questions
About MrExcel
Consulting Services
Learn Excel Resources
Excel - Visual Learning
Challenge of the Month
MrExcel Seminars
Message Board
MrExcel Store
Podcast
Search
Media
Contact
Home
Having an Excel Emergency?
 
 


Click Add as Fan in top right
of our Facebook page

 
 

 

The MrExcel Challenge of The Month

May 2011

 

The Winners of this 'MrExcel Challenge' have been selected!

*Please Note that this contest is ended and winners have been selected and awarded. Most but not all work by the chosen contestants is shown;


Jon Sanderson: using 'Weatherbug Historical Observations' via Windows Azure Marketplace DataMarket

Jon writes:

"I pulled the Windows Azure Marketplace DataMarket - Weatherbug Historical Observations to get the elevation of 13,324 records of Weatherbug locations that track current weather conditions.  I then put together a table of the 50 states and which basketball conference that "state" team belongs.  All of the SEC schools for example, have state schools in their conference.  I made high level assumptions on states like Louisiana (LSU), Pennsylvania (Penn St over Penn), Ohio St, etc.  I wanted to see the average elevation by conference, and also pulled into the time zone to sort west to east to see if the logic made sense.  I feel it turned out very well, while these type of calculations aren't necessarily real world, I felt like that was the outside of the box thinking you were looking for.

Screenshots of Jon's creation

 

Tushar Mehta: Sent in several entries using PowerPivot and Windows Azure Marketplace DataMarket

Tushar writes:

Entry #1

This dashboard adapted a visualization technique I created some time ago to color shapes in an Excel object so that the net result is a visual dashboard that conveys information in a natural context – in this case a map of the U.S.

I integrated several Excel components with my software to create this solution.  Data from the Azure Data Market, together with USPS web data were added to a PowerPivot database, which was the source for a PivotTable (PT) in Excel.  Slicers filtered the PivotTable and downstream analysis updated a map of the U.S.

 

Entry #2

This dashboard shows U.S. crime statistics on a state level in their ‘natural’ context, i.e., a U.S. map.  At the same time, a more traditional bar chart shows the data in a different by equally relevant layout.

In the snapshot below I have intentionally left visible elements of the Excel environment.  While the row and column headers and gridlines serve no useful purpose in the dashboard they do remind the viewer that this is an Excel workbook.

 

Entry #3

This dashboard shows U.S. crime statistics on a state level in their ‘natural’ context, i.e., a U.S. map.  At the same time, a more traditional bar chart shows the data in a different by equally relevant layout.

The use of the map of the U.S. with appropriately located bubbles lets one see data in their natural context, something that may reveal patterns that might otherwise not be obvious – at least not without more work – a lot more work in some cases.  The map I used did not include Alaska and Hawaii.  So, I located their bubbles in their general direction.

The charts are synchronized and controlled by the 2 slicers, one for the year and the other for the type of crime.

The first chart ( REF _Ref294975583 \h Figure 1) uses the same color scheme for all data values.  Consequently, it relies strictly on the size of the bubble to convey information about the relative number of crimes of interest.

Figure  SEQ Figure \* ARABIC 1 – Murder statistics for 2008 across U.S. states.
The data are adjusted to represent the crime rate per 1,000 of the state’s population

 

The second chart uses ‘conditional’ colors to show three ranges of values.  The conditional colors are created by plotting each data point in one, and only one, of three series.  The three series are then colored differently.

Figure  SEQ Figure \* ARABIC 2 – Murder statistics for U.S. states for 2008.
The data are adjusted to represent the crime rate per 1,000 of the state’s population

 

Congratulations to our winners!! -The MrExcel Team

 

* Terms and Conditions are subject to change - but that probably won't happen.

 

 

There are over 150,000 pages at MrExcel.com
Use Google
to search our site:

 


The MrExcel Library: See It!

 
Need PowerPivot?


 

Need Azure [ADM]?

     
 

Excel, PowerPivot, Azure Data Marketplace, XBOX360 and Kinect are registered trademarks of the Microsoft® Corporation.
MrExcel
TM is a registered trademark of Tickling Keys, Inc.

All contents Copyright 1998-2011 by MrExcel Consulting | Domestic and International Copyright Applies | All Rights Reserved