#value!

[loopiloo]

Hi all,
I have the following formula and only on some occasions it is displaying #VALUE!
=IF(AF35>0,J35/H35,0)
There is text in the cell H35 which is derived from a link to a different sheet, however this does not seem to be the problem because some of the cells are ok and don't produce the error even if i write text into them?
Can anyone please help me solve this issue
Thanks
loopiloo

[Sam Capricci]

you could change it to this...
=IFERROR(IF(AF35>0,J35/H35,0),0)

[AlKey]

you could change it to this...
=IFERROR(IF(AF35>0,J35/H35,0),0)

Only if OP is using Excel 2007 or higher otherwise IF(ISERROR(

[Sam Capricci]

Only if OP is using Exce3 2007 or higher otherwise IF(ISERROR(

U R right.
of course the OP doesn't list which version but...
you know i'm seeing more and more posters lately not listing their excel versions.

[Speshul]

That error will happen is you have a letter or non-divisible character in J35 or H35
Try

=IF(AND(AF35>0,ISNUMBER(H35),ISNUMBER(J35),H35>0),J35/H35,0)

Also this will avoid div/0 errors

[AlKey]

@Sambo kid,
This issue has been recently addressed by Tony Valko so hopefully the administration will make it mandatory.

For pre-2007 version use:

=IF(ISERROR(IF(AF35>0,J35/H35,0)),0,IF(AF35>0,J35/H35,0))

[loopiloo]

Thanks a lot guys...
I used =IF(AND(AF25>0,ISNUMBER(H25),ISNUMBER(J25),H25>0),J25/H25,0) and it worked fine...
OP is 2007
loopiloo