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 , , , ,

Dirty Data

January 26, 2011 by Leave a comment

In the business world, we get data in all kinds of formats and ways.  Often times this data is what I call dirty and in need of cleaning.  The most common culprits can be easily fixed.  Below are some of them and my most oft-used remedies.

Weird space at the end (or beginning) of text in field
Sometimes I download data, copy and paste it from another source, or get it directly from a database.  And, for some reason,  there are fields or cells with spaces at the end.  My experience tells me that this is usually one of two things.  Either somehow (often data entry error) a person typed a space at the end or beginning on accident.  Or non printable characters (such as carriage returns or the tab key) have been appended.  The latter often happens when converting a tab delimited file. Read more of this post

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

← Older posts

Newer posts →