#DIV/0!

[qmann]

i would like to hide this (temporarily dividing into 0) and to show when a percentage is available. Any ideas on how to hide this. It has some numbers in the column and some values.
the formula is basically z1/z2*100% where z1 may be empty at times but filled in later. Any ideas? Your help is once again greatly appreciated.

 

[dhulbert]

Try
=if(iserror(z1/z2*100%),0,(z1/z2*100%))
when your z1 cell is enpty it will show as 0

 

[xlhelp]

=if(iserror(Z1/Z2*100%),0 (or whatever you you like),Z1/Z2*100%)

"Mind is like a parachute. Works well when opened."

 

[KenWright]

=IF(Z1="","",z1/z2)

but, if the error you are getting is the one in your title, then it is z2 that is causing you the problem. You can divide 0 by anything and you will still get 0. Trying to divide BY 0 is a no no though, so:-

=IF(Z2="","",z1/z2)

or even

=IF(OR(Z1="",Z2="&quot,"",Z1/Z2)

Note though, that this will not trap it if z2 is 0, but then you haven't qualified as to whether that is a possibility, so perhaps:-

=IF(OR(Z1="",Z2="",Z2=0),"",Z1/Z2)

Regards
Ken..............

 

[qmann]

the above formula worked great.. thanks guys... actually i want to add a little more functionality.. what i want to do is say
z1 or y1 or x1/z2 * 100%.. whichever has the value in it.

 

[TrevJones]

I take it that you mean if either of the three values have 0 in them then return 0. If this is so, just multiply the three values together and if one of them is zero the answer will be zero ie IF((x1*Y1*z1)=0,0,....) or i could be completely wrong .....

 

[GlennUK]

So how about ...

=MAX(X1,Y1,Z1)/Z2*100%

which will ignore those of X1 Y1 and Z1 that are zero ( assuming that one of them is greater than zero ).

This formula doesn't check for Z2 being zero. That would look something like this ...

=IF(Z2=0,0,MAX(X1,Y1,Z1)/Z2*100%)

Cheers, Glenn.

 

[qmann]

How can i incorporate the following two formulas together

=if(iserror(z1/z2*100%),0,(z1/z2*100%))
and
=MAX(X1,Y1,Z1)/Z2*100%

I want to say okay the max of either of those cells, then divide by cell z2 but if all those cells are empty put in a 0.

 

[KenWright]

=IF(ISERROR(MAX(X1,Y1,Z1)/Z2*100%),0,MAX(X1,Y1,Z1)/Z2*100%)

Regards
Ken...............

 

[sfvb]

=IF(Z2=0,0,MAX(X1:Z1)/Z2)

This works because The ISERROR part of the equation, will only produce an error if z2 = 0, (if z2 is empty, Excel will assume it's 0), so that's all you need to test for.

Also, the MAX function returns either the MAX number, or 0 if it finds no numbers.

 

[sfvb]

Oops, I just reread the thread and noticed that GlennUK already gave you the answer before you asked for it. I just dropped off the *100%[/], which is not necessary.

something*100% = something*1 = something