• 409K Members
• 6,377 Online
• 466K Conversations
SOLVED

Occasional Contributor

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.

15 Replies

Hi,

That could be like

`=MEDIAN(B2:INDEX(B2:B1000,N1-1))`

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.

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 ?

# Look Up Tables

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....

Solution

# Re: Look Up Tables

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.

# Re: Look Up Tables

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)`

# Re: Look Up Tables

INCREDIBLE - THANK YOU SO SO MUCH - IT WORKS AS A DREAM!!!!!!

# Re: Look Up Tables

Thank you - for the reply - I have had a solution. Kind regards

# Re: Look Up Tables

Thanks @Detlef Lewin ,it's more universal - works on unsorted range

# Re: Look Up Tables

=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 @Sergei Baklan

@PETEMAGS , you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies