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))
- =MEDIAN(B2:INDEX(B2:B1000,N1-1))
Dear Sergi - You helped me on a look up a few weeks ago. Amazing. The Median above does not seem to work. The goal is to limit the range to B2:B(& a cell value in cell N1)
The B range is normally between 1000 and 5000 but the above seems to just ignore N and go to infinite. There was als a suggestion The dynamic way to reference the end of the range, in your case, is this:
=MEDIAN(B2:INDEX(B:B,N1)) Twifoo which the same the red range just ignores the N. Am I doing something wrong?
Any help would be great.PETEMAGS , let me imitate on a small test as attached
Second column with MEDIAN and direct range, right one - with INDEX and N in third column. Please check the formula and adjust to your case.
Thanks excellent - and I understand it now with the comma,and cell - Thank you again SergeiBaklan
- Wow....... It worked - thank you so so much......You have saved me an enormous amount of time.... Is there any chance that you know anything about look Up?
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....
