Forum Discussion

Landman1452's avatar
Landman1452
Copper Contributor
Aug 10, 2021
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.
mathetes's avatar
mathetes
Gold Contributor
Aug 10, 2021

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.

Resources