Best Apartment Search Spreadsheet

Looking for spreadsheets for apartments? Introducing the best apartment hunt spreadsheet!

I’ve created a new and improved version of my Apartment Search Spreadsheet template. I spent countless hours maximizing the design until I was certain I had created the ultimate apartment comparison worksheet tool. Like any other product, apartments also have certain attributes (rents, utilities, community/neighborhood, etc.) that need to be identified and prioritized/rated based on their relevance or importance. The importance of these attributes varies from one person to another. The number of parameters involved (easily in excess of 20 parameters) creates a need for a framework that measures and normalizes the results to arrive at a numerical “score” to simplify the decision making process. An excel-based product was created to organize apartment data, assign weights and finally compute scores automatically for the user to evaluate in order to easily figure out which apartment best fits his needs. So, how does it work?

Understanding Bubble chart and Slider:

I use the Apartment Search Spreadsheet to compare features of different apartments using a bubble chart in Excel. A bubble chart is a variation of a scatter plot where each of the data point in the series is represented by a bubble that can vary in size or color depending on the area of the apartment being considered. Larger bubbles represent larger apartment scores.

The bubble chart in the scoring model uses three values per data point:

• X- Axis value : X axis represents the total scores
• Y-Axis value : Y axis represents the total monthly cost
• Bubble Size : Bubble size is proportional to the total area (in square feet)

The apartment search bubble chart is created by an embedded macro which runs through the score data and adds the bubble series, adjusts the colors and fills effects. The chart needs to be updated every time data is modified in the apartment sheet. Once updated, adjustments to the slider updates the chart automatically. The bubbles can also be replaced with apartment pictures, if available and desired. It is advisable to update the chart manually using the command button provided on top right corner of the chart.

To create Bubble charts in Microsoft Excel:

1.      Select data series

2.      Click Insert, Select the Charts

3.      Select the chart type as Bubble

The sliders are added as:

1.      Select Developer

2.      Click Insert

3.      Select the combo box in the form control box

4.      Use Format Control to Cell Link

Excel Sheet with Weighted Rankings

Weights are assigned for each of these categories based on relative importance/relevance. The slider assigns a number ranging from 1 to 100 for each category. The weight for that category is then calculated as “the assigned number divided by the sum of all the assigned numbers for all the categories”. With this weighting logic, an adjustment to the weight of a category automatically changes the weights of the other categories while keeping the total percentage at 100. The horizontal bars, next to the slider, graphically represent the values set by the slider.

Watch the video embedded below to learn how to use the slider and bubble chart features for apartment rent comparison: 

I have invested a significant amount of time and effort into perfecting the comparison spreadsheet as well as researching the best methods to find an apartment. Included in the download is a free pdf copy of my Apartment Search Guide which contains many tips on searching for a new apartment as well as how to use the new spreadsheet and an apartment hunting checklist.

Thanks, and please let me know if you have any questions or comments

.

P.S. YOU can earn 75% commission for every friend you refer. Visit our affiliate page for details.

An Excel Macro to Delete Pictures

How to delete pictures and textboxes based on a range

I recently created an Excelpicture  macro where I deleted a range of rows from my spreadsheet. However, I had a few pictures and textboxes residing inside the area that I wanted to delete. These objects aren’t tied to any individual cell or row so they were originally not being deleted like I wanted. What to do? Time to edit our Excel vba picture macro.

I added a few lines of code which essentially check if the top left corner of the object intersected with any of the cells contained within the range I wanted to delete. If so, and the object was a picture, then it was deleted. Same for the textboxes.

‘Delete pictures

Dim Sh As Shape

   For Each Sh In ActiveWorkbook.Worksheets(I).Shapes

       If Not Application.Intersect(Sh.TopLeftCell, Range("A40:AA120")) Is Nothing Then

         If Sh.Type = msoPicture Then Sh.Delete

             End If

    Next Sh

'delete text boxes

 For Each Sh In ActiveWorkbook.Worksheets(I).Shapes

       If Not Application.Intersect(Sh.TopLeftCell, Range("A40:AA120")) Is Nothing Then

                  If Sh.Type = msoTextBox Then Sh.Delete

               End If

    Next Sh

By using macros in Excel, this process was repeated inside code which looped through all the sheets in my open workbook. To loop through all worksheets:

Dim WS_CountAs Integer

 Dim I As Integer

' SetWS_Count equal to the number of worksheets in the active workbook.

WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop through all worksheets.

   For I = 1 ToWS_Count

'active sheet i

ActiveWorkbook.Worksheets(I).Activate

‘insert delete pictures and textboxes here

Next I

As with most computer programs, there are multiple ways to go about the same task and it’s up to you to pick the best one. Another option is this:

Dim shpAs Shape

    For Each shpInActiveSheet.Shapes

        'Debug.Printshp.Name, shp.TopLeftCell.Row, shp.BottomRightCell.Row

        If shp.TopLeftCell.Row>= 5 Andshp.BottomRightCell.Row<= 39 Then

shp.Delete

        End If

    Next

Take Advanced Excel Training to learn how to write VBA macros in Excel.

2012 NBA Mock Draft Spreadsheet

The NBA Finals are over and LeBron James finally won his (first) title. For us fans of lesser teams (Cleveland) it’s time to turn our attention to the promises of next season. The 2012 NBA Draft will air on ESPN on June 28, starting at 7:00pm. During the draft lottery selection on May 30, amid some conspiracy theories, the New Orleans Hornets were selected to pick first in this year’s draft (while my Cavs will be picking 4^th). Anthony Davis from the NCAA defending national champion Kentucky Wildcats is projected by many fans and experts alike to be the number one overall pick. All players must be at least 19 years old and one year removed from high school.

Now you can make your own NBA draft results with our 2012 NBA Mock DraftCreator xls. Similar to our NFL mock draft and NHL mock draft creators. this Excel spreadsheet lists all sixty picks of the two rounds of the draft. The player data database includes information on each NBA 2012 draft prospects.

How it works

The list of players is a named range called “players.” In the draft tab, next to each team’s selection you click the drop down list and it will list all the player names. Next, I used the VLOOKUP function to pull in the player’s position, school, and age from the data table located on the second sheet.

The spreadsheet can also be easily converted and used for your nba fantasy mock draft 2012.

NBA 2012 Mock Draft Creator.xls 

Download link 1

Download link 2

Feel free to leave a comment and share your latest mock draft results below!

Z-Code Winning Sports Predictions - a proven system that takes the emotion out of predicting winnters and losers to rake in profits all year long.

How to find the best score based on condition

In this example, I have a workbook with two worksheets. On Sheet1, in column A, there is a list of more than 2000 individual’s names (some of them repeated) and column B lists each person's average test score percentage. In the second worksheet, called Sheet2, column A contains a list of each person's name exactly once and in columns b, c, d I want to list their first, second, and third best score or percentage.

How to use the LARGE function in Excel

The first, second, or third best score can be obtained using the Excel large function. The LARGE function allows you to return the nth largest value in a unique data set, like the second best score. The syntax for the large function excel is: =LARGE(array,k) where an array is a range of data and k is the position from the largest value in the array. So, for the first best score k=1, second best k=2, etc.

The LARGE function excel will get the best score from the list but we need to make it so the function only applies to the individual listed in sheet 2 column A. This is where the excel array function is used.

Using arrays in Excel

An array is a range of data, like items in a table. Often times you may see array formulas referred to as "CSE formulas," because you press CTRL+SHIFT+ENTER to transform the function into an excel array formula. We need to use our LARGE function in an array in order to return the best score based on the condition of the person’s name. So we’ll use the Excel large if.

To return the 2nd largest score for Joe, the name listed in Sheet2 column A, use this excel large formula:

{=LARGE(IF(Sheet1!$A$1:$A$2000=$A2,Sheet1!$B$1:$B$2000,-1),2)}

Remember, to enter this array formula into the cell, then instead of hitting the Enter key, hit Ctrl-Shift-Ente

Download the large and array examplespreadsheet from Google docs or join our email newsletter to receive it as an .xls attachment.