It’s Absolute(ly) Relative

March 23, 2011 by Leave a comment

If I had a dollar for every time someone asked me “what the heck are those dollar signs for?,” I’d be a millionaire.  Well, anyone worth their weight in gold as an Excel user should really understand what those pesky dollar signs are used for.

Most of us know how to use relative references

It’s really pretty simple.  The inclusion of dollar signs ($) in a formula changes at least part of the cell reference from relative to absolute.  Let me explain.  You see, Microsoft Excel is a very, very powerful tool. So powerful, in fact, that more times than not, it tries to help you get your work done faster and more effectively, even attempting to save you, the user, from yourself.  Like auto-numbering and spell-check in Microsoft Word, this type of “help” is what we’ve come to expect with Microsoft products.  So when you copy a formula for use somewhere else in your worksheet, which I’m sure you’ve done, Excel assumes that you want to keep the same relationship.

For example, say if you have three columns of data that represent daily transactions for your store.  Column A represents quantity sold, Column B lists the price per item, and Column C is the product of the two (A*B).  Because of this intuitiveness built into Excel, you need only enter “=A2*B2” into column C (assuming the first row is your column headings).  You can then copy and paste or fill-down the formula. And, as I’m sure you know, Excel will continue with the relative positions (A3*B3, A4*B4, etc.). Read more of this post

Advertisement

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

YOY Comparison for Most Recent Days

March 3, 2011 by 1 Comment

Comparing Same Day Sales YOY

In my last post, Showing Activity for Most Recent Days, I showed you how to find the most recent date for which you have “totals” data.  We found that whether it’s support calls or sales made each day, being able to retrieve this information quickly and efficiently can be extremely helpful.   By using the ROUNDDOWN AND MAX functions, as well as the very powerful SUMPRODUCT,  we were able to create a quick summary of the most recent days’ activity. The obvious next question?  How do those days compare with the same time last year.  In other words, using the most recent data, how are things going compared to last year?

We already have the first several pieces of the puzzle, the current year’s sales information.  To compare the data to last year, we simply find the totals for the same dates in the previous year.  We can determine these dates these dates fairly easy by using the CONCATENATE formula, along with the popular and well known MONTH and YEAR formulas. Read more of this post

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

Showing Activity for Most Recent Days

February 26, 2011 by 1 Comment

I’m working on a dashboard for a company that sell tee shirts to high school sports programs.  They get their data from an online database which includes day and time of order as well as order amount. But, you could apply the same methodology to any data that is updated often, whether you are copying and pasting into Excel, importing from or linking to an Access database, or connecting to an ODBC data source. (Of course I didn’t use the company’s actual data; I used a cool trick to generate random dates and times for events for my sample data.)

At any given point, management of Acme SportsTees, Inc. would like to see the last 10 days of sales activity.  The trick to dashboards; and in my opinion, any process or task you are trying to automate; is removing from the process as much human involvement (thinking, typing, more thinking) as possible.  So, if you are able to connect directly to the data source, or at least reduce the actions required by the administrator to just copying and pasting, you will significantly reduce the opportunity for human error and a lot of headaches. Read more of this post

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