puzzlenomad.blogg.se

1. #HOW TO USE DATA ANALYSIS IN EXCEL 2010 FOR MOVING AVERAGES HOW TO#
2. #HOW TO USE DATA ANALYSIS IN EXCEL 2010 FOR MOVING AVERAGES FULL#

It’s important to do this first and to check that the totals (by rows and values) are correct before we move on to the move advanced analysis.

I do this using the excel COUNTIF, SUMIF and AVERAGEIF functions. Now we are ready to begin the basic analysis of the data – first by simply segmenting the data by single fields such as Region or Balance Band. In the example I have created a new column (F) in the spreadsheet called “Balance Band” to group customers by balance by simply using the excel IF function as follows: To go further we need to group them into bands and categories. The problem with numeric fields such as balances or interest rates is that all we can really do with them in their raw state is is to total them or average them. Step 2 – Add the required Categorisation Columns Life can get tricky in excel if you have to keep jumping between record types within a single calculation! Just make sure the different record types are clearly distinguished and can be processed independently of each other. Header Records for Customers and Detail Records for Accounts. Multiple Record Types – Sometimes you may need different record types e.g. This is fine as long as you know what approximations are built in! For example, an approximate start date might be inferred from the date of the first transaction but might only be accurate to the month rather than the day. Oldest month – Make sure the oldest month’s data it is not also a “catch-all” for older data too – if you want older data make sure it is recorded as such rather than aggregated in with the oldest month.Īpproximations – Sometimes the exact data is not available but the programmer can make an approximation. So you might have to wait for the next end of month.

#HOW TO USE DATA ANALYSIS IN EXCEL 2010 FOR MOVING AVERAGES FULL#

Most recent month – Make sure it is a full month’s data – partial months may create misleading trends. You need to be clear in what you ask for particularly around the following: There are a few things you need to be careful about concerning the copy of the live data typically provided for you by an IT person. Step 1 – Get a good clean dataset to work with However during the analysis the results will also suggest other questions you can answer which you won’t have anticipated! The objective of the analysis is to understand this data to see if we can spot any useful business insights which could inform our strategy on customer acquisition and retention.įirst Identify the questions you want answers toīefore you start any data analysis it’s always a good idea to identify a number of questions you want answers to. My example spreadsheet, which would be extracted from the live customer system, has one row per customer containing just the following fields: Pivot tables are better shown than described so if you are interested in pivot tables I demonstrate them here in this 5-min screencast.

#HOW TO USE DATA ANALYSIS IN EXCEL 2010 FOR MOVING AVERAGES HOW TO#

If you don’t want to learn how to use these 3 excel functions don’t worry – you can also achieve steps 3-5 just using excel pivot tables provided you are prepared to invest a little time in understanding this powerful excel facility. To master this approach you simply need to understand the power of 3 excel functions – IF, COUNTIF (single criteria) and COUNTIFS (multiple criteria) along with their cousins SUMIF and AVERAGEIF. The example I will use is a retail banking customer dataset however the same principles and techniques apply equally well to any live customer dataset for any business sector. In this article and screencast I share a 7-step plan, illustrated with a worked example, for Instant Customer Analytics using Excel. One of the most useful aspects of Microsoft Excel is its ability to quickly slice and dice customer data from live systems to identify important trends and behaviors which can inform strategy.