IRERR nested formula help please

[Potsy]

hi just wondering if someone can help with the following nested ISERR
formula. I want to be able to show in J3 that if the value in G3 is
"0" then show as "-100%" OR if the value is "NULL" then show as "0"
but if the value is >0 then I3/G3*100

it is working to a degree but if value in G3 is NULL then still shows
as -100% I want it to show as 0% if NULL and -100% if 0 - hope it
makes sense....

currently have following in J3

=IF(ISERR(I3/G3),-100,I3/G3*100)

G3 = Order
I3 = Profit

thanks in advance

 

[Bernard Liengme]

Depending on how I interpret NULL
=IF(ISBLANK(G3),0,IF(ISERR(I3/G3),-100,I3/G3*100))
or
=IF(G3="NULL",0,IF(ISERR(I3/G3),-100,I3/G3*100))
best wishes

 

[Dave Peterson]

Maybe...

=if(g3="",0,if(g3=0,-100%,i3/g3*100))

hi just wondering if someone can help with the following nested ISERR
formula. I want to be able to show in J3 that if the value in G3 is
"0" then show as "-100%" OR if the value is "NULL" then show as "0"
but if the value is >0 then I3/G3*100

it is working to a degree but if value in G3 is NULL then still shows
as -100% I want it to show as 0% if NULL and -100% if 0 - hope it
makes sense....

currently have following in J3

=IF(ISERR(I3/G3),-100,I3/G3*100)

G3 = Order
I3 = Profit

thanks in advance

 

[Potsy]

Maybe...

=if(g3="",0,if(g3=0,-100%,i3/g3*100))









--

Dave Peterson- Hide quoted text -

- Show quoted text -

thanks guys will give it a go and report back!!!

 

[Potsy]

Maybe...

=if(g3="",0,if(g3=0,-100%,i3/g3*100))









--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave

Nope sorry none of those are working...

 

[Bernard Liengme]

Have you tried deleting G3 to ensure it really is empty?
What does the formula =LEN(G3) return?
best wishes

 

[Dave Peterson]

You may want to share what went wrong?

What's in G3, what's in I3?

 

[Potsy]

Have you tried deleting G3 to ensure it really is empty?
What does the formula =LEN(G3) return?
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email






- Show quoted text -

Hi Bernard, it is cell refrenced from another sheet - it is blank, but
is recording 1 character on =LEN(G3) as advised. I guess that is the
problem will change to put NO ORDER if we have started and leave 0 as
an unstarted project. Thanks for your help Bernard/Dave.

 

[Bernard Liengme]

Thanks for the feed back.
If =LEN(SUBSTITUTE(G3,CHAR(160),"")) returns 0, then your cell has a a
special space character generally used in HTML.
Try =if(OR(g3="", G3=CODE(160)) ,0,if(g3=0,-100%,i3/g3*100))
best wishes