IRERR nested formula help please

  • Thread starter Thread starter Potsy
  • Start date Start date
P

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
 
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
 
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
Click to expand...
 
Have you tried deleting G3 to ensure it really is empty?
What does the formula =LEN(G3) return?
best wishes
 
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 -
Click to expand...

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.
 
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
 
Back
Top