#Value! error

[roccoau]

Hi,
I have an IF formular which looks at a feild & if it is greater than Zero multiplies the value by 10% but if it is zero (no value) I would like the feild to be blank or a Zero to appear. =IF(F38>0,F38*0.1,"0")
My formular is giving me a #Value! error when value is zero. What am I doing wrong ???

Tks

 

[boblarson]

=IF(F38>0,F38*0.1,0)

Get rid of the quotes around the last zero.

 

[roccoau]

Bob tks for quick reply.
Yes this works if there is a value in F38
But if there is no value at all in F38 I get error message.
What I am doing is a Quote template which does not have a value in the feild to start off with. So I don't want this error message appearing. Is it possible ??

 

[boblarson]

try this instead

=IIF(IsNull([F38]),0,IIf(F38>0,F38*0.1,0))

 

[roccoau]

Tks Bob,
When I enter this formular in excel the formular stays visible.
Formular does not work in excel. Is this for excel or only for Access ???
Or is it that I don't know what I am doing ?

Tks

 

[boblarson]

Sorry about that, I missed the fact that it was in the Excel forum. I came to it via the New Posts section.

Try this instead:

=IF(F38=" ",0,IF(F38>0,F38*0.1,0))

 

[roccoau]

No worries bob, Tks for your help.
Sorry but this still give me same error message.
Could it be because F38 has a formular in it.
I have attached a sample so you can see waht I am doing wrong.

Tks

 

[boblarson]

Since the formula for F38 is set to

=IF(SUM(F20:F37)>0,SUM(F20:F37),"")

Remove the space from between the quotes on the other:

=IF(F38="",0,IF(F38>0,F38*0.1,0))

 

[roccoau]

Thanks Bob that works perfectly.

Rgds