Answered by:
Get rid of #ERROR in the column
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,
Thursday, April 20, 2017 1:40 PM 
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.
 Edited by Ismet Kocaman Thursday, April 20, 2017 2:05 PM
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.
 Edited by Ismet Kocaman Friday, April 21, 2017 7:17 AM
Friday, April 21, 2017 7:17 AM 
Thanks a lot Ismet, it is working nowFriday, April 21, 2017 7:29 AM

You're welcome and thank you for marking as answer.
 Edited by Ismet Kocaman Friday, April 21, 2017 10:02 AM
Friday, April 21, 2017 10:02 AM