locked
Inconsistent formula results RRS feed

  • Question

  • I am new to PowerPivot and am still learning it's limitations.

    I have a formula I am using to created an additional column inside of the
    PowerPivot Table that shows an inconsistent results. <o:p></o:p>

    Here is my formula.<o:p></o:p>

    =IF((TABLE[ForecastedImpactDate]-TABLE[ReportedClosedDate])>30,"F","")<o:p></o:p>

    And below is the result set. It is calculating a blank ReportedClosedDate
    and that is an error.<o:p></o:p>

    If I attempt to use the "IsBlank" function like this:

    =IF(ISBLANK(TABLE[ReportedClosedDate]),"",IF((TABLE[ForecastedImpactDate]-TABLE[ReportedClosedDate])>30,"F","")<o:p></o:p>

    Power Pivot throws a syntax validation error.

    Any guidance you can offer will be greatly appreciated.

    Thank you,

    - Merry Pettijohn

    Friday, April 27, 2012 7:05 AM

Answers

  • i just rebuilt your sample and this calculation worked just fine for me:

    =IF(ISBLANK('TABLE'[ReportedClosedDate]); 
    "";
    IF(INT('TABLE'[ForecastImpactDate]-'TABLE'[ReportedClosedDate])>30;
    "F";
    ""))

    hth,
    gerhard


    - www.pmOne.com -

    • Proposed as answer by Challen Fu Monday, April 30, 2012 7:14 AM
    • Marked as answer by Challen Fu Tuesday, May 8, 2012 10:14 AM
    Friday, April 27, 2012 12:56 PM
    Answerer

All replies

  • Are your blank cells empty or zero length strings?  I've had this problem myself and it turned out I had a zero length string.

    Friday, April 27, 2012 12:20 PM
  • i just rebuilt your sample and this calculation worked just fine for me:

    =IF(ISBLANK('TABLE'[ReportedClosedDate]); 
    "";
    IF(INT('TABLE'[ForecastImpactDate]-'TABLE'[ReportedClosedDate])>30;
    "F";
    ""))

    hth,
    gerhard


    - www.pmOne.com -

    • Proposed as answer by Challen Fu Monday, April 30, 2012 7:14 AM
    • Marked as answer by Challen Fu Tuesday, May 8, 2012 10:14 AM
    Friday, April 27, 2012 12:56 PM
    Answerer
  • hi can you please check the below forum for your reference.

    http://www.excelforum.com/excel-worksheet-functions/614583-inconsistent-formula-error.html

    Wednesday, May 2, 2012 9:51 AM