Mar 12 2019 05:08 AM
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 the value of a cell to equal the 2nd cell range.
Hope this makes sense - it would be an amazing help if I can solve.
I have added a picture of what I am referencing.
Thanks you in advance!!!
Mar 12 2019 05:47 AM
Hi,
That could be like
=MEDIAN(B2:INDEX(B2:B1000,N1-1))
Mar 12 2019 06:17 AM
Mar 12 2019 06:34 AM
Mar 12 2019 07:00 AM
You are welcome. What exactly you'd like to do with LOOKUP ?
Mar 12 2019 07:26 AM
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....
Mar 12 2019 07:46 AM
SolutionAssuming 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.
Mar 12 2019 08:34 AM
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)
Mar 12 2019 09:30 AM
Mar 12 2019 09:32 AM
Mar 12 2019 10:01 AM
Thanks @Detlef Lewin ,it's more universal - works on unsorted range
Mar 28 2019 03:12 PM
Mar 29 2019 03:52 AM
@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.
Mar 29 2019 07:33 AM
Thanks excellent - and I understand it now with the comma,and cell - Thank you again @Sergei Baklan
Mar 12 2019 07:46 AM
SolutionAssuming 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.