Quartile formula error

  • Hi all


    Im using a quartile formula by column. Problem is now the formula is returning a result when referencing a blank cell whereas before it wasnt doing this.
    See attachment to see what I mean. Please help!


    Thanks guys


    Alex

  • Re: Quartile formula error


    You have given no indication here or in the workbook of what you think is an incorrect calculation or what your expected outcomes are.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: Quartile formula error


    In O2, change formula to this:
    =IF(B2="","",MATCH(B2,QUARTILE(B$2:B$11,{4,3,2,1,0}),-1))


    Copy down/right as needed. Problem is that the cells you see as blank, actually have zero-length strings. This confuses XL into thinking there's really a value, and so it's able to correctly do a MATCH. Solution then is instead of trying to just error trap, check first if cell's value is "".

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Quartile formula error


    Quote from aalexander;779433


    Im using a quartile formula by column. Problem is now the formula is returning a result when referencing a blank cell whereas before it wasnt doing this.


    The difference in Z11 and AA11 compared to Y11 is: M11 and N11 are truly empty, whereas L11 contains an invisible null string, which is probably the result of doing copy-and-paste-value of an explicit null string ("").


    Consequently, MATCH(N11,QUARTILE(N$2:N$11,{4,3,2,1,0}),-1) is evaluated as MATCH(0,...), which returns 3 because zero is less than or equal to QUARTILE(...,2). Similarly for MATCH(M11,...).


    In contrast, MATCH(L11,QUARTILE(L$2:L$11,{4,3,2,1,0}),-1) is evaluated as MATCH("",...), which returns #N/A. So IFERROR returns the null string ("").


    Either fix M11 and N11 et al to be consistent with L11 -- not a likely solution -- or change your formula as follows:


    =IFERROR(IF(N11="","",MATCH(N11,QUARTILE(N$2:N$11,{4,3,2,1,0}),-1)),"")
    or
    =IFERROR(IF(ISNUMBER(N11)=FALSE,"",MATCH(N11,QUARTILE(N$2:N$11,{4,3,2,1,0}),-1)),"")


    PS.... I did not see Luke's response, apparently posted 4 hours earlier. My response is essentially a duplicate. But I'll let it stand because it contains a little more detail, which might be useful.