Hi everyone, I'm sure there's probably a clever solution to this, but it's beyond me.
I have a dataset that represents the varying sales prices of a particular product.
My goal is to come up with what would be a "substantive" price for the product, and want to use a bell-curve approach. My methodology is that any price that is within 20% of the "substantive" price would be deemed acceptable, but prices that deviate by more than 20% are not acceptable.
If my values were evenly distributed, I could use the median or an average price to define my "substantive" price, but my population of prices isn't evenly distributed. So, the most "substantive" price may not necessarily be the median or the average.
I can figure this out myself by using goal seek (ex., if substantive price = $2,000, then 15 out of 20 sales are within range, if substantive price = $2,100, then 14 out of 20 sales are within range, etc.). However, I don't want to hunt and peck. I want a function that will automatically calculate the best fit to the bell-curve.
I've uploaded an example of what I'm talking about.
Any suggestions?
Thanks!
Bookmarks