SOLVED

Help with Percentile function

%3CLINGO-SUB%20id%3D%22lingo-sub-3244114%22%20slang%3D%22en-US%22%3EHelp%20with%20Percentile%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3244114%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20attached%20spreadsheet%20which%20contains%20a%20large%20volume%20of%20data.%20I%20have%20found%20the%20percentile%20function%20works%20for%20my%20top%20cells%20(first%2020%2C000).%20I%20am%20aware%20the%20formula%20seems%20to%20stop%20if%20the%20row%20count%20is%20beyond%20that.%20However%20even%20if%20i%20change%20to%20keep%20the%20row%20count%20the%20same%2C%20the%20formula%20seems%20to%20stop%20working.%20Does%20anyone%20have%20any%20ideas%20for%20the%20reason%20for%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3244114%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3245162%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Percentile%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3245162%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20since%20you%20have%20%23REF!%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20700px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F352766i3FB2B5931A497BC6%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20remove%20all%20works%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Regular Contributor

Hi,

 

I have the attached spreadsheet which contains a large volume of data. I have found the percentile function works for my top cells (first 20,000). I am aware the formula seems to stop if the row count is beyond that. However even if i change to keep the row count the same, the formula seems to stop working. Does anyone have any ideas for the reason for this?

 

Many thanks,

2 Replies
best response confirmed by calof1 (Regular Contributor)
Solution

@calof1 

That's since you have #REF! here

image.png

If remove all works

 

hi Sergei,

Thank you kindly for your assistance, very much appreciated.

Kind regards,