Microsoft Project - How to avoid using #Error in Cost Custom Field

Question

Hello all,

I have created a Simple formula in Custom Field Cost5. 

IIf(Len([Text3])<>0,[Cost2]*CInt(Text3))

The formula works fine when there is a value in Text3. If there is no value I get #ERROR.  Any chance I can avoid that. 

Thank you

Answer 1

What sort of data are you storing in Text3?  Have you considered using a spare Number field?  By default the number fields have a value of zero - so if you haven't entered data into the field, the formula result will be zero.

Answer 2

SFMJI.

The formula does not know what to do when Len[Text3] is equal to zero. There is no False part in the iif. Its syntax is iif( condition, true_part, false_part ). iif**( Len([Text3])<>0, [Cost2]*CInt(Text3), ??? )**

Answer 3

Thank you.

I am kind a beginner in customizing fields in MS Project to show data on Gantt Chart. I changed text fields to Number and the reports & if  statements work great.

Thanks for the direction.

ckandreou:

My long shot is that converting text to integer by using CInt and using the term "if statements" indicate that you have background in programming. So it won't be difficult to understand how it works with formulas.

If you like, you can also ask me questions thru email (check profile) and there are many articles **here** on formulas.

Kind regards.

Answer 4

You're welcome.  Because a number field cannot be blank, you can greatly simplify the formula.  It can simply be: 

[Cost2]*[NumberX} .  No need for the IIF to test if the number field is empty.

Answer 5

Thank you.

I am kind a beginner in customizing fields in MS Project to show data on Gantt Chart. I changed text fields to Number and the reports & if  statements work great.

Thanks for the direction.