SOLVED

PLEASE HELP... CELL REF IN MEDIAN FORMULA

%3CLINGO-SUB%20id%3D%22lingo-sub-364757%22%20slang%3D%22en-US%22%3EPLEASE%20HELP...%20CELL%20REF%20IN%20MEDIAN%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364757%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20-%20is%20it%20possible%20to%20use%20a%20cell%20reference%20as%20a%20number%20in%20the%20Cell%3ACell%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%26nbsp%3B%3DMEDIAN(B2%3AB4740)%26nbsp%3B%20-%20if%20Cell%20N1%20has%204740%20in%20it%20-%20is%20there%20some%20way%20to%20write%26nbsp%3B%3DMEDIAN(B2%3AB%3CSTRONG%3E%26amp%3B'N1'%3C%2FSTRONG%3E)%20so%20that%20it%20adds%20the%20value%20of%20a%20cell%20to%20equal%20the%202nd%20cell%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20makes%20sense%20-%20it%20would%20be%20an%20amazing%20help%20if%20I%20can%20solve.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20added%20a%20picture%20of%20what%20I%20am%20referencing.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20you%20in%20advance!!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-364757%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Formula%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFORMULA%20ERROR%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390835%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP...%20CELL%20REF%20IN%20MEDIAN%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390835%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F299316%22%20target%3D%22_blank%22%3E%40PETEMAGS%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390829%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP...%20CELL%20REF%20IN%20MEDIAN%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390829%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20excellent%20-%20and%20I%20understand%20it%20now%20with%20the%20comma%2Cand%20cell%20-%20Thank%20you%20again%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390680%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP...%20CELL%20REF%20IN%20MEDIAN%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390680%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F299316%22%20target%3D%22_blank%22%3E%40PETEMAGS%3C%2FA%3E%26nbsp%3B%2C%20let%20me%20imitate%20on%20a%20small%20test%20as%20attached%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20251px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F100509i90E9D44C1B6FC94C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ESecond%20column%20with%20MEDIAN%20and%20direct%20range%2C%20right%20one%20-%20with%20INDEX%20and%20N%20in%20third%20column.%20Please%20check%20the%20formula%20and%20adjust%20to%20your%20case.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390455%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP...%20CELL%20REF%20IN%20MEDIAN%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390455%22%20slang%3D%22en-US%22%3E%3DMEDIAN(B2%3AINDEX(B2%3AB1000%2CN1-1))%3CBR%20%2F%3EDear%20Sergi%20-%20You%20helped%20me%20on%20a%20look%20up%20a%20few%20weeks%20ago.%20Amazing.%20The%20Median%20above%20does%20not%20seem%20to%20work.%20The%20goal%20is%20to%20limit%20the%20range%20to%20B2%3AB(%26amp%3B%20a%20cell%20value%20in%20cell%20N1)%3CBR%20%2F%3EThe%20B%20range%20is%20normally%20between%201000%20and%205000%20but%20the%20above%20seems%20to%20just%20ignore%20N%20and%20go%20to%20infinite.%20There%20was%20als%20a%20suggestion%20The%20dynamic%20way%20to%20reference%20the%20end%20of%20the%20range%2C%20in%20your%20case%2C%20is%20this%3A%3CBR%20%2F%3E%3DMEDIAN(B2%3AINDEX(B%3AB%2CN1))%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%20which%20the%20same%20the%20red%20range%20just%20ignores%20the%20N.%20Am%20I%20doing%20something%20wrong%3F%3CBR%20%2F%3EAny%20help%20would%20be%20great.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-365031%22%20slang%3D%22en-US%22%3ERe%3A%20Look%20Up%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-365031%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F299316%22%20target%3D%22_blank%22%3E%40PETEMAGS%3C%2FA%3E%26nbsp%3B%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-365030%22%20slang%3D%22en-US%22%3ERe%3A%20Look%20Up%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-365030%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%2Cit's%20more%20universal%20-%20works%20on%20unsorted%20range%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-365014%22%20slang%3D%22en-US%22%3ERe%3A%20Look%20Up%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-365014%22%20slang%3D%22en-US%22%3EThank%20you%20-%20for%20the%20reply%20-%20I%20have%20had%20a%20solution.%20Kind%20regards%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-365012%22%20slang%3D%22en-US%22%3ERe%3A%20Look%20Up%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-365012%22%20slang%3D%22en-US%22%3EINCREDIBLE%20-%20THANK%20YOU%20SO%20SO%20MUCH%20-%20IT%20WORKS%20AS%20A%20DREAM!!!!!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-364971%22%20slang%3D%22en-US%22%3ERe%3A%20Look%20Up%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364971%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20solution%20would%20be%3A%3C%2FP%3E%3CPRE%3E%3DAGGREGATE(14%2C6%2C%24L%243%3A%24L%24230%2F(%24N%243%3A%24N%24230%26lt%3B%24S%241)%2C1)%3CBR%20%2F%3E%3CBR%20%2F%3E%3DAGGREGATE(15%2C6%2C%24L%243%3A%24L%24230%2F(%24N%243%3A%24N%24230%26gt%3B%24S%242)%2C1)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-364941%22%20slang%3D%22en-US%22%3ERe%3A%20Look%20Up%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364941%22%20slang%3D%22en-US%22%3E%3CP%3EAssuming%20your%20column%20N%20is%20sorted%20in%20ascending%20order%20the%20formula%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DINDEX(L3%3AL230%2CMATCH(S1%2CN3%3AN230%2C1))%3C%2FPRE%3E%0A%3CP%3Eand%20the%20same%20for%20S2.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20didn't%20make%20dynamic%20range%20as%20in%20previous%20post%20since%20not%20sure%20do%20you%20have%20some%20totals%20at%20the%20end%20of%20N%20range%20(as%20in%20columns%20L%20and%20M)%20and%20do%20you%20use%20entire%20range%20or%20only%20part%20of%20it.%20You%20may%20modify%20that%20part%20yourselves.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20note%20re-calculation%20takes%20some%20time%2C%20you%20may%20see%20blank%20cells%20for%20a%20while.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-364932%22%20slang%3D%22en-US%22%3ELook%20Up%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364932%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20creating%20a%20Price%20Earnings%20Of%20Stocks%20sheet%20that%20defines%20the%20Average%20of%20the%20PE%20over%20multi%20years.%20I%20am%20defining%20a%20Histogram%20that%20says%20a%20certain%20number%20in%20my%20case%2030%25%20of%20the%20values%20were%20at%20or%20below%20-%20is%20there%20a%20way%20to%20set%20the%20value%20WRITE%20A%20FORMULA%20FOR%20S3%20AND%20S5%20IN%20THE%20ATTACHED%20on%20Sheet%20P-E%20-%20such%20that%20the%20S3%20formula%20would%20look%20up%20Colum%20L%20-%20The%20Histogram%20Bin%20and%20return%20the%20value%20that%20is%20closest%20to%20S1%20value%20from%20Colum%20N.%20In%20other%20words%20there%20are%20values%20in%20the%20Bin%20with%20Frequency%20of%2030%25%20or%20less%20of%20the%20full%20range.%20This%20might%20sound%20a%20bit%20muddled%20but%20hope%20you%20understand..%20and%20would%20be%20amazing%20if%20you%20can%20help....%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-364824%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP...%20CELL%20REF%20IN%20MEDIAN%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364824%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20are%20welcome.%20What%20exactly%20you'd%20like%20to%20do%20with%20LOOKUP%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-364800%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP...%20CELL%20REF%20IN%20MEDIAN%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364800%22%20slang%3D%22en-US%22%3EWow.......%20It%20worked%20-%20thank%20you%20so%20so%20much......You%20have%20saved%20me%20an%20enormous%20amount%20of%20time....%20Is%20there%20any%20chance%20that%20you%20know%20anything%20about%20look%20Up%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-364789%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP...%20CELL%20REF%20IN%20MEDIAN%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364789%22%20slang%3D%22en-US%22%3EThe%20dynamic%20way%20to%20reference%20the%20end%20of%20the%20range%2C%20in%20your%20case%2C%20is%20this%3A%3CBR%20%2F%3E%3DMEDIAN(B2%3AINDEX(B%3AB%2CN1))%3CBR%20%2F%3EWhatever%20is%20the%20value%20of%20N1%20will%20be%20the%20row%20number%20for%20the%20end%20of%20the%20range%20in%20Column%20B.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-364773%22%20slang%3D%22en-US%22%3ERe%3A%20PLEASE%20HELP...%20CELL%20REF%20IN%20MEDIAN%20FORMULA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364773%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20like%3C%2FP%3E%0A%3CPRE%3E%3DMEDIAN(B2%3AINDEX(B2%3AB1000%2CN1-1))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
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. 

 

Thanks you in advance!!!

 

 

15 Replies
Highlighted

Hi,

 

That could be like

=MEDIAN(B2:INDEX(B2:B1000,N1-1))
Highlighted
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.
Highlighted
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?
Highlighted

You are welcome. What exactly you'd like to do with LOOKUP ?

Highlighted

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

Highlighted
Solution

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.

Highlighted

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)
Highlighted
INCREDIBLE - THANK YOU SO SO MUCH - IT WORKS AS A DREAM!!!!!!
Highlighted
Thank you - for the reply - I have had a solution. Kind regards
Highlighted

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

Highlighted

@PETEMAGS , glad to help

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

@PETEMAGS , let me imitate on a small test as attached

image.png

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.

Highlighted

Thanks excellent - and I understand it now with the comma,and cell - Thank you again @Sergei Baklan 

Highlighted

@PETEMAGS , you are welcome