Forum Discussion

Copper Contributor

Mar 12, 2019

Solved

PLEASE HELP... CELL REF IN MEDIAN FORMULA

Hi - is it possible to use a cell reference as a number in the Cell:Cell format. So =MEDIAN(B2:B4740) - if Cell N1 has 4740 in it - is there some way to write =MEDIAN(B2:B&'N1') so that it adds ...

SergeiBaklan
Mar 12, 2019
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.

PETEMAGS

Copper Contributor

Mar 12, 2019

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....

SergeiBaklan

Diamond Contributor

Mar 12, 2019

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.