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.
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.
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
PETEMAGS , you are welcome