Comparing information on 2 different pivot tables

I have two different pivot tables. One with house for sale prices, the other with house for rent prices.

How do I take the information from one and compare it with the other?

I want to be able to take the purchase price from one pivot table and have excel take the yearly rent from the other and apply a formula (rent/purchase price)

Unfortunately the data has city names only in the sale price table, not in the other.

Is this possible?

Sorry the for sale example is so small, the uploader wouldn't let me put my full size one on

Ryan

For Rent inc Pivot.xlsxFull List workings in pivot (small).xlsx

Anyone got any ideas? I normally get an answer within minutes, don't tell me you're stumped!

Bump. Do I need to repost?

Thanks so much for this.

Can you please walk me through the formula you have created and how you did it?

I would like to be able to isolate various sized houses too, so I can compare all 3 bedroom houses, for example, which is why I originally was comparing the 2 pivot tables.
Also, there is a chance there will be various suburbs in different cities that have the same name, so ideally it would be divided into cities too.

Should I still go ahead and put this into the commercial section? I feel like I will have a lot more questions before I have fully completed what I am planning on doing.

Here is what I did. As I mentioned, I copied your Purchase raw data table as a new sheet Purchase in the Rent file. Your existing Rent raw data is in sheet Rent. I created a new sheet called Results. The first column of Results is a pivot table based on the Rent raw data to generate a list of unique Suburb names. In column B I have the following formula to calculate the ratio of rent/purchase price for each suburb. You did not specify how to calculate this so I am dividing the average annual rent by the average sale price.

=IFERROR(AVERAGEIF(Rent!A:A,A2,Rent!H:H)/AVERAGEIFS(Purchase!H:H,Purchase!B:B,A2,Purchase!H:H,"<>0"),"")

IFERROR is set up to show a blank cell if the ratio cannot be calculated. This will occur if there are no purchases for this suburb, because you can't divide by zero.

The top part of the division

AVERAGEIF(Rent!A:A,A2,Rent!H:H)

takes the average of all rents from sheet Rent that are for the suburb named in column of Results.

The bottom of the division

AVERAGEIFS(Purchase!H:H,Purchase!B:B,A2,Purchase!H:H,"<>0")

is similar, and takes the average of all sales prices from sheet Purchase that are for the suburb named in column of Results. The second condition of "<>0" excludes any purchases with a price of 0 from the average (there are many labeled "Price by negotiation" which have a price of 0).

The next two columns are simply the counts of transactions that are used in the average. You may not need this, but I included it as a sanity check to make sure my numbers were all making sense. You many find it useful; if not, it's fine to delete those columns.

To add attributes for City and number of rooms, you would expand the pivot table I created to include those as additional columns. The you would have to modify the first AVERAGEIF formula to be AVERAGEIFS and take into account suburb, city, and size for rents. You would also have to modify the second AVERAGEIFS formula to do the same thing for purchases.

I can help with this if you provide an updated set of Rent data that includes city.

OK.
.
.
.
Slowly but surely I'm getting it. I've taken a break from this project for a while because it turns it turns out life is hectic around christmas/new year and things like this need some proper attention.
Firstly, the rent/purchase calculation is correct, the only difference is that I would display it as a percentage and call it yield.
I would love it if you could expand it to show city and amount of rooms.
Also, I am planning to do this with thousands of entries scraped from an advertising site, does that make any difference?
I'd like to use the formula multiple times with results scraped over a period of time in order to keep it up to date, is that easy enough for me to do?

As I said earlier I have bought credits for the commercial section of the site, so I am happy to contribute for any time you spend on this project. It is very helpful and I couldn't do it without your help