none
Get rid of #ERROR in the column RRS feed

  • Question

  • Hello, 

    My formula shows values between 0 and 100. It works, but the #ERROR appears in some cells. Could you please help me get rid of the "#ERROR" in the column. May be there is a VBA code that colors the "#ERROR" in white and makes it invisible?

    IIf([Percent] Between 1 And 100,[Percent]," ")

    Best regards, Evgeny

    Thursday, April 20, 2017 12:43 PM

Answers

  • If it is a milestone task, Text20's #ERROR is caused by "division by zero" in Round( [Duration10] / 0 * 100). Text7's #ERROR is because it gets #ERROR from Text20.

    Solution:

    Text20:  iif( [Milestone], "", Round(Duration10/Duration*100) )

    Text7:   iif( Not [Milestone]  AND [Text20] Between 1 And 100, [Text20], "" )

    Alternatively;

    Text20:  iif( [Duration] = 0, "", Round(Duration10/Duration*100) )

    Text7:   iif( [Duration]<>0  AND [Text20] Between 1 And 100, [Text20], "" )

    I hope this works.


    Ismet Kocaman | eBook on Formulas

    • Marked as answer by Evgeny_EV Friday, April 21, 2017 7:29 AM
    Friday, April 21, 2017 7:15 AM

All replies

  • Hi,

    You should give us the detail of your formula, and most of all, in what condition it returns ERROR.

    Then no need for any VBA, you could simply do the following. For example if it return ERROR if your % is equal to 0, do the following:

    iif([%Complete]=0;"";formula)


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller

    Thursday, April 20, 2017 1:40 PM
    Moderator
  • Pls try using a custom text field instead of a custom number field since the latter complains when it gets an empty string "". The formula iif ( [Percent] Between 1 and 100, [Percent], "" ) returns either a value between 1 and 100 (inclusive) or an empty string. 
    Thursday, April 20, 2017 2:01 PM
  • Text1 and Number2 contain the formula. PERCENT is the renamed custom number field.


    Ismet Kocaman | eBook on Formulas

    Thursday, April 20, 2017 2:13 PM
  • Thanks for your help. Here is my formulas that I use to get the Planned Value in terms of time (% of works that should have been completed by the current date):

    1. Created Duration10 column with the formula: ProjDateDiff([Start],[Current Date])

    2. Created the Text20 column with the formula: Round(Duration10/Duration*100)

    3. Created the Text7 column with the formula: IIf([Text20] Between 1 And 100,[Text20],"")

    The problem is that the #ERROR appears for milestones with 0 duration. That is why I think that is there are no ways to fix it using formulas maybe this can be fixed by some VBA that can make the #ERROR invisible:

    Start Finish Duration Duration10 Text20 Text7
    16/02/17 02/10/17 155 days 42.8 days 28 28
    08/02/17 02/10/17 161 days 48.8 days 30 30
    21/11/16 28/02/17 64 days 100.8 days 158
    01/08/17 29/12/17 107 days -68.2 days -64
    29/12/17 29/12/17 0 days -175.2 days #ERROR #ERROR
    02/05/17 14/11/17 136 days -6.2 days -5
    02/05/17 14/11/17 136 days -6.2 days -5
    14/11/17 14/11/17 0 days -142.2 days #ERROR #ERROR
    Friday, April 21, 2017 6:59 AM
  • If it is a milestone task, Text20's #ERROR is caused by "division by zero" in Round( [Duration10] / 0 * 100). Text7's #ERROR is because it gets #ERROR from Text20.

    Solution:

    Text20:  iif( [Milestone], "", Round(Duration10/Duration*100) )

    Text7:   iif( Not [Milestone]  AND [Text20] Between 1 And 100, [Text20], "" )

    Alternatively;

    Text20:  iif( [Duration] = 0, "", Round(Duration10/Duration*100) )

    Text7:   iif( [Duration]<>0  AND [Text20] Between 1 And 100, [Text20], "" )

    I hope this works.


    Ismet Kocaman | eBook on Formulas

    • Marked as answer by Evgeny_EV Friday, April 21, 2017 7:29 AM
    Friday, April 21, 2017 7:15 AM
  • Always enclose the field names within square brackets to make them 100% field reference. 
    Friday, April 21, 2017 7:17 AM
  • Thanks a lot Ismet, it is working now
    Friday, April 21, 2017 7:29 AM
  • You're welcome and thank you for marking as answer.
    Friday, April 21, 2017 10:02 AM