EXAMPLE: Rating Comparison Spreadsheet Template

In my very first post I mentioned that I use Excel all of time and in a variety of ways. One good example is when I began working at a new job after I graduated college and I needed to quickly find a new place of residence. I didn’t want to settle for just any old place. I wanted to know which location was best for me. What did I do? I turned to Excel to illustrate the pros and cons of different apartments I had chosen as candidates in order to help me decide which was the best.



Along the top you can see the names of all the apartment complexes. Down the left hand side I listed all of the features I was interested in knowing and specifically asked about when I visited the apartments. In-between resides the gathered data. The majority of the wanted features required a simple yes or no answer. The apartment rating line is the average rating given to the apartment from an online apartment rating website. I computed the yearly cost just to get an idea of how much it would be and if I could afford it. 

The final line is the most important, the scores for each apartment. How did I come up with those? Well if you look at the left hand side you might have noticed a bunch of hidden rows. That is where the magic happens. Each feature is given or subtracted points based on a factor of importance I created. The last column of the spreadsheet is the base line, my perfect living arrangement, and you can see the highest possible score is 12.5.



This is what the rows look like unhidden. I gave each listed feature a rating as some of them were more important than others. I have my own microwave already so that was not as important as having a washer and dryer in the room. Price of rent was essential but having a covered parking lot was not.



Here are the formulas I used for each feature:

Monthly Cost: =IF(B5<680,1.5,0)

Sq Feet = IF(B7>790,0.5,0)

Lease =IF(B8=6,1,0)

Pet Friendly =IF(B9="YES",1,0)

Dish-washer =IF(B10="YES",1,0)

Microwave =IF(B11="YES",0.5,0)

Washing Machine =IF(B12="YES",1.5,0)

Outdoor Pool =IF(B13="YES",1,0)

Indoor Pool =IF(B14="YES",0.5,0)

Covered Parking =IF(B15="YES",0.5,0)

Gated =IF(B16="YES",1,0)

Water =IF(B17="YES",0.5,0)

Private Entry =IF(B18="YES",1,0)

Deposit =IF(B19>200,-0.5,0)

Ap Fee =IF(B20>100,-0.5,0)

Distance to Work =IF(B21<12,0.5,0)

Online Rating =IF(B22>=0.5,0.5,0)

By using this Excel spreadsheet I no longer have to guess which apartment suits my needs, I now KNOW the best fit.