How do I use Conditional Formatting to get rid of #DIV/0!?

[Eslava]

I have my formulas in place and they are working. The reason why some are coming up with #DIV/0 is because I do not have the data to complete the calculation. Is there a way to make the #DIV/0 error go away until I imput the data to complete the calculation? I was thinking there might be a way to do it with conditional formatting but I don't know how to do it.

Please let me know.

Thanks!

 

[Jonmo1]

Not with conditional formatting, but you could adjust your formula to return blank instead...

First need to understand what causes the error.
#DIV/0 is caused by the # to the RIGHT of / (the divisor) being 0.

so just test that value for 0, and return blank if it is..
for example with this simple formula

=A1/B1

if B1 = 0 you get #DIV/0

so you could remedy that by
=IF(B1=0,"",A1/B1)

Hope this helps...

 

[Eslava]

Right now i have this code in place:

{=MROUND(SUMPRODUCT(IF(m_code=$A9,m_base25),(IF(m_code=$A9,weighted)))/SUM(IF(m_code=$A9,weighted)),(IF(SUMPRODUCT(IF(m_code=$A9,m_base25),(IF(m_code=$A9,weighted)))/SUM(IF(m_code=$A9,weighted))>=100,1,0.5)))}

How would I modify it so that I can come up with a blank or -- ?

 

[PatrickBirch]

You could use the following:

If(iserror(a1/b1),0,(A1/b1))

This should return a zero in place of the #Div/0 sign.

Hope this helps.

Thanks
Patrick

 

[PatrickBirch]

{=if(iserror(MROUND(SUMPRODUCT(IF(m_code=$A9,m_base25),(IF(m_code=$A9,weighted)))/SUM(IF(m_code=$A9,weighted)),(IF(SUMPRODUCT(IF(m_code=$A9,m_base25),(IF(m_code=$A9,weighted)))/SUM(IF(m_code=$A9,weighted))>=100,1,0.5))),0,{=MROUND(SUMPRODUCT(IF(m_code=$A9,m_base25),(IF(m_code=$A9,weighted)))/SUM(IF(m_code=$A9,weighted)),(IF(SUMPRODUCT(IF(m_code=$A9,m_base25),(IF(m_code=$A9,weighted)))/SUM(IF(m_code=$A9,weighted))>=100,1,0.5)))}

I think that should work?

Thanks
Patrick

 

[Jonmo1]

Well, it looks like this is the cause

SUM(IF(m_code=$A9,weighted))

If that results in 0, you're getting the #DIV/0 result..

so you could test that at the beginning of the formula

See if this works...

=IF(SUM(IF(m_code=$A9,weighted))=0,"",MROUND(SUMPRODUCT(IF(m_code=$A9,m_base25),(IF(m_code=$A9,weighted)))/SUM(IF(m_code=$A9,weighted)),(IF(SUMPRODUCT(IF(m_code=$A9,m_base25),(IF(m_code=$A9,weighted)))/SUM(IF(m_code=$A9,weighted))>=100,1,0.5))))}

 

[Jonmo1]

Patrick, FYI

Generally, it's more acceptable to test for a specific error, then to test for ANY error. Because if you blanket out ANY error to get rid of 1 Expected type of error, that may blind you from seeing another problem that you did not expect...

Hope That Helps..

 

[PatrickBirch]

That's fair enough, Although I thought from the post he probably just doesn't want to see the error sign thus why he asked to use conditional formatting to get rid of it.

Anyway, Hope the answer above solves your problem

 

[Domenic]

Maybe...

=MROUND(SUMPRODUCT(--(m_code=$A9),m_base25,weighted)/MAX(1,SUMIF(m_code,$A9,weighted)),IF(SUMPRODUCT(--(m_code=$A9),m_base25,weighted)/MAX(1,SUMIF(m_code,$A9,weighted))>=100,1,0.5))

...confirmed with just ENTER. Note that the formula will return 0 instead #DIV/0!. If so desired, the cell can be custom formatted to hide 0 values...

Format > Cell > Number > Custom > Type: 0;-0;;@

Hope this helps!

 

[MNpoker]

Note in 2007 they did improve one thing:

If(Iserror(A/b)),"",A/b)

Can now be written

Iferror(A/B,"")