Forum Discussion
PLEASE HELP... CELL REF IN MEDIAN FORMULA
Assuming your column N is sorted in ascending order the formula could be
=INDEX(L3:L230,MATCH(S1,N3:N230,1))
and the same for S2.
I didn't make dynamic range as in previous post since not sure do you have some totals at the end of N range (as in columns L and M) and do you use entire range or only part of it. You may modify that part yourselves.
Please note re-calculation takes some time, you may see blank cells for a while.
Hi,
That could be like
=MEDIAN(B2:INDEX(B2:B1000,N1-1))
You are welcome. What exactly you'd like to do with LOOKUP ?
I am creating a Price Earnings Of Stocks sheet that defines the Average of the PE over multi years. I am defining a Histogram that says a certain number in my case 30% of the values were at or below - is there a way to set the value WRITE A FORMULA FOR S3 AND S5 IN THE ATTACHED on Sheet P-E - such that the S3 formula would look up Colum L - The Histogram Bin and return the value that is closest to S1 value from Colum N. In other words there are values in the Bin with Frequency of 30% or less of the full range. This might sound a bit muddled but hope you understand.. and would be amazing if you can help....
Hello
My solution would be:
=AGGREGATE(14,6,$L$3:$L$230/($N$3:$N$230<$S$1),1)
=AGGREGATE(15,6,$L$3:$L$230/($N$3:$N$230>$S$2),1)
