Posts by joeu2004

  • SUMIF less than

    Re: SUMIF less than


    Quote from mar050703;781095


    I have attached the file showing on the Reports Tab - You will see D1 is set to Oct-16, and so should add up where the Table[Date] is Oct-16.


    You are confusing the appearance of a date due to cell formatting in D1 with the actual Excel date (10/1/2016 in my region).


    Also note that the value of Actual!B3 is text ("Oct-16"), not an Excel date. It does not appear to be referenced (now). But bear in mind that we cannot compare text "Oct-16" with an actual date formatted to appear as Oct-16.


    Change the formula in Report!D8 to:
    =SUMIFS(ActualTbl[In (Mwk)], ActualTbl[Date], ">=" & Report!$D$1, ActualTbl[Date], "<=" & EOMONTH($D$1,0))


    Change the formula in Report!C10 to:
    =SUMIFS(ActualTbl[Out ($)], ActualTbl[Date], ">=" & $D$1, ActualTbl[Date], "<=" & EOMONTH($D$1,0))


    Make similar changes to other similar formulas in the Report worksheet.

  • Quartile formula error

    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.