Unwinnable situation with Median If, arrays, pasting the formula etc

%3CLINGO-SUB%20id%3D%22lingo-sub-2095434%22%20slang%3D%22en-US%22%3EUnwinnable%20situation%20with%20Median%20If%2C%20arrays%2C%20pasting%20the%20formula%20etc%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2095434%22%20slang%3D%22en-US%22%3Ewhen%20I%20lock%20the%20cell%20range%20in%20my%20formula%2C%20it%20pastes%20the%20same%20value%20no%20matter%20what%20the%20variable%20instead%20moving%20the%20unlocked%20cell%20like%20it's%20supposed%20to%202)%20if%20I%20don't%20lock%20any%20of%20it%2C%20it%20calculates%20only%20on%20the%20subsequent%20Values%20in%20the%20range.%203)%20If%20I%20try%20pasting%20the%20array%20it%20won't%20let%20me%20repeat%20the%20formula%20after%20the%20first%20cell%2C%20because%20it%20won't%20change%20arrays%204)%20if%20I%20painstakingly%20copy%20and%20paste%20the%20formula%20from%20each%20cell%20to%20the%20next%20without%20locking%20any%20of%20the%20cells%2C%20eventually%20it%20returns%20the%20sameValue%20no%20matter%20what%20the%20variables.%205)%20the%20only%20options%20for%20paste%20special%20are%20unicode%20and%20text.%206)%20if%20I%20try%20filtering%20the%20categories%20and%20pasting%20the%20formula%2C%20it%20fills%20in%20the%20same%20value%20for%20every%20category.%207%20even%20when%20I%20select%20a%20cell%20and%20use%20the%20array%2C%20it%20produces%20the%20same%20values%20for%20the%20entire%20dataset.%20It's%20just%20impossible.%20Please%20help.%20I'm%20desperate.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2095434%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2095526%22%20slang%3D%22en-US%22%3ERe%3A%20Unwinnable%20situation%20with%20Median%20If%2C%20arrays%2C%20pasting%20the%20formula%20etc%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2095526%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F942234%22%20target%3D%22_blank%22%3E%40GrumpyOldWoman%3C%2FA%3E%26nbsp%3BI'm%20sorry%20to%20hear%20your%20frustration%20but%20your%20description%20leaves%20me%20clueless%20what%20exactly%20you%20are%20trying%20to%20do.%26nbsp%3B%20Please%20attach%20a%20sample%20spreadsheet%20showing%20how%20the%20data%20is%20set%20up%20and%20what%20you%20are%20trying%20to%20accomplish.%26nbsp%3B%20That%20said%20it%20sounds%20like%20you%20are%20having%20trouble%20with%20addressing%20an%20array%20like%20A1%3AA100%20where%20you%20need%20part%20of%20it%20to%20index%20but%20part%20of%20it%20to%20not%20index.%26nbsp%3B%20I%20just%20want%20to%20make%20sure%20you%20understand%20that%20you%20can%20use%20the%20'%24'%20to%20lock%20any%20part%20of%20it.%26nbsp%3B%20For%20example%20A%241%3AA100%20would%20only%20lock%20row%201%20of%20the%20first%20part%20so%20the%20next%20cell%20down%20would%20reference%20A%241%3AA101.%20There%20are%20also%20many%20things%20you%20can%20do%20with%20INDEX%2C%20FILTER%2C%20etc...%20but%20not%20knowing%20more%20about%20what%20you%20are%20doing%2C%20I'm%20stabbing%20in%20the%20dark.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2095629%22%20slang%3D%22en-US%22%3ERe%3A%20Unwinnable%20situation%20with%20Median%20If%2C%20arrays%2C%20pasting%20the%20formula%20etc%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2095629%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F942234%22%20target%3D%22_blank%22%3E%40GrumpyOldWoman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20would%20need%20to%20see%20an%20example%20of%20what%20you%20are%20talking%20about%20including%20the%20formulas%20you%20have%20considered%20and%20what%20you%20are%20hoping%20to%20see%20by%20way%20of%20results.%26nbsp%3B%26nbsp%3BAt%20first%20sight%2C%20I%20would%20say%20that%20it%20is%20the%20purpose%20of%20MEDIAN%20to%20return%20a%20single%20'mid'%20value%20for%20one%20or%20more%20arrays.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor
when I lock the cell range in my formula, it pastes the same value no matter what the variable instead moving the unlocked cell like it's supposed to 2) if I don't lock any of it, it calculates only on the subsequent Values in the range. 3) If I try pasting the array it won't let me repeat the formula after the first cell, because it won't change arrays 4) if I painstakingly copy and paste the formula from each cell to the next without locking any of the cells, eventually it returns the sameValue no matter what the variables. 5) the only options for paste special are unicode and text. 6) if I try filtering the categories and pasting the formula, it fills in the same value for every category. 7 even when I select a cell and use the array, it produces the same values for the entire dataset. It's just impossible. Please help. I'm desperate.
2 Replies

@GrumpyOldWoman I'm sorry to hear your frustration but your description leaves me clueless what exactly you are trying to do.  Please attach a sample spreadsheet showing how the data is set up and what you are trying to accomplish.  That said it sounds like you are having trouble with addressing an array like A1:A100 where you need part of it to index but part of it to not index.  I just want to make sure you understand that you can use the '$' to lock any part of it.  For example A$1:A100 would only lock row 1 of the first part so the next cell down would reference A$1:A101. There are also many things you can do with INDEX, FILTER, etc... but not knowing more about what you are doing, I'm stabbing in the dark.

@GrumpyOldWoman 

We would need to see an example of what you are talking about including the formulas you have considered and what you are hoping to see by way of results.  At first sight, I would say that it is the purpose of MEDIAN to return a single 'mid' value for one or more arrays.