Trend Analysis

[k-cain]

Excel friends, I could really use your help if you are out there! I have attached a spreadsheet that contains inspection scores for apartment complexes within various states. There are many variables like property city, state, category, Section of the Act (SOA), etc. The three most important columns are shaded in yellow, blue, and pink. These represent the most recent inspection score (yellow), 2nd inspection score (blue), and 3rd inspection score (pink), respectively. I have also included a column that calculates the percentage difference between the 1st and 2nd score and one for the percentage difference between the 1st and 3rd score.

Here's what I need: I'm trying to create a chart (or charts) that analyzes trends among the inspection scores. I want to see if I can find a pattern between properties with low scores. For example, is there a way to visualize any correlations between property SOA and a low score?

I appreciate any assistance you can offer. Take a look at my spreadsheet and feel free to play around or send me an example. Thank you!!

[MrShorty]

How much of your question is Excel specific (how do I create a chart in Excel?) and how much is the more generic "how to display this kind of data in a chart?" (independent of the application used to generate the chart?

If you are unfamiliar with creating charts in Excel, I would probably suggest you start with something like:
https://support.office.com/en-us/art...8-9c00cd03ff0c
https://support.office.com/en-us/art...2-fb094596f118
http://www.excel-easy.com/data-analysis/charts.html

These should get you familiar with the chart editing and formatting controls, and get you started creating charts.

If this is a more generic question, then I would probably start by asking myself exactly what I want to see with this analysis.

Are you looking to see the trend from 1st inspection to 3rd inspection? As explained in the links above, data layout can be important in creating charts. I might add some helper columns to copy the results of these into contiguous cells. Something like:

Please Login or Register  to view this content.

Use these three columns in a line chart (you may need to execute a "switch row/column" command or go into the Select Source Data dialog to make sure it is using row 1 as the horizontal category axis data). You will likely want to only add a handful of rows to the chart, since 4000 lines on the chart will probably be way too much information on one chart.

Or maybe try a column chart, with column A or B as the horizontal axis data, with column H, K, and N as the Y series data. Again, 4000 data sets will be a lot to put on a single chart, but it will allow you to see something of the trend across properties and inspections.

From there, just try some different things to see what you like and don't like.

[stasinek]

Hello, little changed data layout (sheet "new data"). I've added a chart and some analytics. See if anything can be seen from these data?

[k-cain]

Stasinek, thank you so much!! This is very helpful. Can you provide me with a brief explanation of your analysis so that I can better understand what you did?

[k-cain]

Stasinek, thank you so much!! This is very helpful. Can you provide me with a brief explanation of your analysis so that I can better understand what you did?

[stasinek]

Chart and analytics is the result of creating pivot tables. Data source for PT is a sheet of "new data". To make the data readable PT need to change some of their arrangement (logins - "new data"). Calculations in the spreadsheet "analytic" is the result of field computing. Formulas for calculation fields are a aruszu "formulas". Sorry for my English - GoogleTranslator