I have included a link to a sample sheet. It contains home sales for 17 years and includes the property type (Type), price (SP), Value (Price/SQF), and Size (SQF). I want to find the median of each for every type, for every year, and for every month.
https://drive.google.com/file/d/1ecmNoQ5g3xr2GsaxeHwupq3Rqnu84puD/view?usp=sharing
I used this formula (for example) for finding the median value each year, but when I manually check it, it is incorrect: {=AGGREGATE(17,6,Value/((YEAR(Closing_Date)=$J127)*(Type="Condo")),2)}.
I really don't understand how that formula works (specifically, the "17,6" and the "2" at the end). I've tried using the =median with all the conditions but I get missing argument errors. Not sure what to do.
I've included space and allow editing of the test file if that helps. Once I see the formulas for one property type, I'll be able to replicate for the other 2 types.
{=AGGREGATE(17,6,Value/((YEAR(Closing_Date)=$J127)*(Type="Condo")),2)} is the right answer. After studying quartile.inc I realized I had a different error that was the causing the problem.
