Forum Discussion
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 ...
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.
The dynamic way to reference the end of the range, in your case, is this:
=MEDIAN(B2:INDEX(B:B,N1))
Whatever is the value of N1 will be the row number for the end of the range in Column B.
=MEDIAN(B2:INDEX(B:B,N1))
Whatever is the value of N1 will be the row number for the end of the range in Column B.