StumpedGump1987
New Member
- Joined
- Mar 28, 2023
- Messages
- 1
- Office Version
- 365
- 2021
- Platform
- Windows
Hello! I've got what I believe to be a pretty tricky one I could use some help on. 
I'm using the following formula to find the distance between sets of longitude and latitude for properties. I'm comparing one property on row 2 of sheet 'For Sale 3.28.2023' to multiple properties in sheet 'Sold 6 months prior 3.2823' using the following formula:
={UNIQUE(IFERROR(2*ASIN(SQRT(POWER(SIN(('Sold 6 months prior 3.2823'!Z:Z-'For Sale 3.28.2023'!Z2)*PI()/180/2),2)+COS('Sold 6 months prior 3.2823'!Z:Z*PI()/180)*COS('Sold 6 months prior 3.2823'!Z:Z*PI()/180)*POWER(SIN(('Sold 6 months prior 3.2823'!AA:AA-'For Sale 3.28.2023'!AA2)*PI()/180/2),2)))*3959,""))}
This formula is successfully returning an array of distances from the original property in row 2 of my 'For Sale 3.28.2023' sheet from the range of properties in sheet 'Sold 6 months prior 3.2823'. I now want to show the average price per square ft (column P on the 'Sold 6 months prior 3.2823') of properties on sheet 'Sold 6 months prior 3.2823' that are under 25 miles from the property in row 2 of the 'For Sale 3.28.2023'. I want to have this single average in one cell which will be the same cell as the formula above, so I just want to adapt that formula if possible. How can I change the formula above to show that?
Can anyone help?! Sorry for the super complicated problem in advance.
Open to creative solutions!!

I'm using the following formula to find the distance between sets of longitude and latitude for properties. I'm comparing one property on row 2 of sheet 'For Sale 3.28.2023' to multiple properties in sheet 'Sold 6 months prior 3.2823' using the following formula:
={UNIQUE(IFERROR(2*ASIN(SQRT(POWER(SIN(('Sold 6 months prior 3.2823'!Z:Z-'For Sale 3.28.2023'!Z2)*PI()/180/2),2)+COS('Sold 6 months prior 3.2823'!Z:Z*PI()/180)*COS('Sold 6 months prior 3.2823'!Z:Z*PI()/180)*POWER(SIN(('Sold 6 months prior 3.2823'!AA:AA-'For Sale 3.28.2023'!AA2)*PI()/180/2),2)))*3959,""))}
This formula is successfully returning an array of distances from the original property in row 2 of my 'For Sale 3.28.2023' sheet from the range of properties in sheet 'Sold 6 months prior 3.2823'. I now want to show the average price per square ft (column P on the 'Sold 6 months prior 3.2823') of properties on sheet 'Sold 6 months prior 3.2823' that are under 25 miles from the property in row 2 of the 'For Sale 3.28.2023'. I want to have this single average in one cell which will be the same cell as the formula above, so I just want to adapt that formula if possible. How can I change the formula above to show that?
Can anyone help?! Sorry for the super complicated problem in advance.
Open to creative solutions!!