Dirty Data Again

February 11, 2011 by Leave a comment

It happened again.  I was working on a project for which I needed a quick list of all of the 50 states and their abbreviations.  I probably had it somewhere— I’m sure I’ve needed this information before—but I was too lazy to look for it.  If I was in 7th grade, I could probably have just typed them in order from memory.  But I’m not and I can’t.  So I did what any half-way smart person would do.  I googled “State Abbreviations”.  The result returned a lot of sites, as you’d expect.  I decided on using the one for our good old United States Postal Service: http://www.usps.com/ncsc/lookups/abbr_state.txt. Read more of this post

Filed under Application (Excel, Access) and Process Development, Business Stuff Tagged with , , , , ,

Mortgage Calculator

February 7, 2011 by Leave a comment

I have a lot of friends and friends of friends that are in real estate.  More importantly, I have a lot of friends who are home owners or soon-to-be home owners.  And I, too, am trying to navigate this grown-up stuff that includes providing a home for my family. So, it dawned on me the other day and I remembered one of Excel’s cool functions, Pmt().  Given the loan details— amount, interest rate, and term— the function returns the monthly payment.

So I decided to put together a simple Excel Sheet (form, really) that applies this function. It also highlights some of Excel’s other cool features, such as: Read more of this post

Filed under Application (Excel, Access) and Process Development, Business Stuff Tagged with , , ,

Pivot Tables..and now (finally)

February 2, 2011 by Leave a comment

Okay, now that we have come up with a slick way to quickly create some sample data to work with, we can finally get to the heart of the matter.  Pivot Tables!  Once you learn this and begin to play with it a little, you’ll be amazed at how much it can help you with analyzing data very, very quickly.  You’ll also finally feel good about putting “Proficient in Excel” or “knowledge of Pivot Tables” on your resume. LOL.

PivotTableSalesData-Pt3

Filed under Application (Excel, Access) and Process Development, Business Stuff

Pivot Tables.. but first

February 1, 2011 by 4 Comments

I’ve been asked by many of you to put together something on Pivot Tables.  In doing so, I found the hardest part was compiling sample data that allowed me to highlight some of the cool features that Pivot Tables have to offer. After searching the web for sample data to use and coming up empty, I decided to go the long route and just start typing.  But as luck would have it, I stumbled upon a slick way or two to generate some useful data.  Attached is that discovery in two parts.  This is my first attempt at a video blog, so please provide any feedback, good or bad.
Part I


Part II

Filed under Application (Excel, Access) and Process Development, Business Stuff Tagged with , , , , , ,

Slicing Data based on Multiple Criteria with SumProduct()

January 28, 2011 by 1 Comment

Excel’s SUMIF() Formula is a great tool.  It allows you to sum a column based on specific criteria (from another column).  But what if you want information filtered based on two (or more) criteria?

Enter SUMPRODUCT(). Read more of this post

Filed under Application (Excel, Access) and Process Development, Business Stuff Tagged with , , , ,

← Older posts

Newer posts →