Trend Analysis

[Kelly]

Chandoo 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!!

 

[PaulF]

I have a start on it...

 

[NARAYANK991]

Hi Kelly ,

Can you first define what is meant by a low score ?

The minimum value of Score1 is 3 ( BTW , your scores have been left as text , where they should be changed to numbers ) , while the maximum value of Score1 is 99.

Use the following formula in H2 to convert the text to number :

=IFERROR(LEFT(I2,2)+0,"")

Is each SOA to be considered separately , or can we group the SOA together based on the 3 digit number at the beginning of the description ?

Narayan

 

[Kelly]

PaulF, I am so grateful for your help! Can you explain your analysis a little bit for me? I want to make sure I understand what you did.

NARAYANK991, I appreciate your insight as well! A low score would be anything rated 60 or below. The maximum score is 100. Each SOA needs to be considered separately.

 

[Kelly]

PaulF, I am so grateful for your help! Can you explain your analysis a little bit for me? I want to make sure I understand what you did. If it helps I have combined some of the SOA groups so that I have less categories to work with.

I have a start on it...

 

[Kelly]

Hi Kelly ,

Can you first define what is meant by a low score ?

The minimum value of Score1 is 3 ( BTW , your scores have been left as text , where they should be changed to numbers ) , while the maximum value of Score1 is 99.

Use the following formula in H2 to convert the text to number :

=IFERROR(LEFT(I2,2)+0,"")

Is each SOA to be considered separately , or can we group the SOA together based on the 3 digit number at the beginning of the description ?

Narayan

NARAYANK991, I appreciate your insight as well! A low score would be anything rated 60 or below. The maximum score is 100. Each SOA needs to be considered separately.

 

[XLPadawan]

Kelly,
Please check out my solution. I wasn't sure how you were going to define high vs low scores so I chose the median score and the average score as possible split points. However, you can choose any number you want if you don't like the median nor the average. Also, I corrected your difference calculations. Should be new value minus original value, then divide your answer by the original number. No division by 100 is necessary, just format as a % with 2 decimal places.

Oops...file is too large to upload (1.1MB) and Chandoo doesn't accept .rar compressed files. I'll email it to you if you'd care to share your email address with me (or any other way you can think of for me to get it to you).