Forum Discussion
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 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!!!
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.
15 Replies
- 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. 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.
- 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 ?
