HELP !!!!! Data Sorting

[Landman1452]

I am using the current windows version on pc, using Microsoft 365 with version 2107. Below is an upload of an excel worksheet that I need help with. 

My line of work is real estate and a basic overview of what I am trying to do is input land sizes by the acre size(example:0.1 acre, 5 acres, 40 acres, 15.69 acres) as well as the price that the land sold for or is selling for into a list. The list comprises various websites that I gather my land sizes and prices. Once the information is inputted into the list, the various sizes of land and the various prices that are associated with the corresponding lot sizes are then averaged out into a table that is above the list. The table corresponds to the prices from the list below and averages out all the prices that are associated with the range of lot sizes. (Example:0-0.99 - $132,000, 1-1.99-$64,500,2-2.99-$44,123,etc.) Then in the same table, there are smaller tables that produce a percentage of what the average price is. 

What I am having trouble with is the connection between the table that is price-per acre=$ and the table that averages out the per acre amount that corresponds to that range of acres.

[#2]

@Landman1452 

You clearly are quite comfortable with Excel...I can see that you've written some very sophisticated formulas throughout that spreadsheet.

The one way in which you were NOT sophisticated is in the fundamental design of the database that serves as the heart of the analysis you're seeking to do. That is the database at the bottom of your sheet: instead of making it a single database, you've made ten, one for each source for the "on the market" and again one for each source of the "Sold". If it were a single database, all of the derivatives would be far simpler. Excel is really good at parsing single databases; we humans often get in the way by making multiple tables that are (or appear) cleaner and more understandable to us. Learn to let Excel excel at what it can do; let it do the heavy lifting of breaking apart/sorting/comparing, and doing from a single database.

"But," you object, "I need to be able to identify whether the source is Zillow or Realtor (etc)."

Easily accomplished. Add two columns to the three that are there for each of the five sources. I've started it for you in Sheet1 of the attached. A column for Source, and a column for Status. And make it an official structured Excel table. When you've done that, you can be adding new rows without needing to adjust the formulas that refer to the table.

Let me suggest you take it from here. My guess: you can now use Pivot Tables straight from this single database to accomplish all of your range comparisons, etc. Come back with questions as needed.

[#3]

Thank you for getting back to me, I understand the principle you created. But the problem is more along the lines of being able to identify/group a specific range of lot sizes; everything from 0-0.99, then 1-1.99, so on until 9-9.99. Then its 10-15, 15-20, 20-25, 25-30, 30-35, 35-40. The reason for the specific grouping is because of various prices associated with that grouping in real estate.
If continuing with your sheet 1 table, is it possible to as your inputting a lot size excel identifies that lot size to a specific range (example: you have a 0.54-acre lot for $45,000), excel identifies that to be associated with the range 0-0.99 acres. The price associated with that lot size is then inputted into a table that averages out all prices that are associated with that range 0-0.99.

[#4]

@Landman1452 

See the revised attachment. I've created what we sometimes call a "business table" that can be used as the basis for a VLOOKUP to take the acreage in column B of the table and return the range associated with that.

The table looks like this, and I've named it LotSizes (as you'll see in the formula)

I added a column to the table containing this formula: =VLOOKUP([@Acres],LotSizes,2,1)

So at the top of that table now you'll see this, and the formula has been copied all the way down.

By the way, when I open the sheet I always respond to the prompts by disabling the macros--I don't usually find macros all that helpful. I've not removed them, so they're still there, and it may be that they serve a useful purpose. I prefer to let Excel's built-in functions do the work, and usually have been able to find ways to do that.

[#5]

@Landman1452 

it might not work because you might be averaging the same property summed by number of listing divided by number of listers against itself. You're going to have to be sure you're not doing that by having a specific address