Help with excel (open office)

[c_dug]

Hi guys.

I'm doing an excel sheet at work. At the moment I have a row of cells dividing the two cells beside it and giving the answer e.g.

=B4/C4

and then conditional formatting turning the background of any figure over 15 red and any below green.

This is all good so far.

However for divisions including zero it is leaving #DIV/0! in the box, which then gets coloured green too.

How do I make is so that boxes containing #DIV/0! remain white?

If I get an answer before 9 tonight it will be awsome If not then whenever is still good.

Thanks

c_dug
____________________
I am a bellend, I am a man of constant sorrow, I am a gummy bear, I am a rock.

[ms51ves3]

I'm confused. Upload the spreadsheet so I can have a gander.

[benvanwell]

Surely you can just have an if statement to check if C4=0 first:

=if(c4=0, display whatever and format white, b4/c4 and conditional fomatting).
I'm sure you get the gist.

I would do it properly but I'm now trying to get my head around where everything is in 2010...
____________________
Currently Riding: Triumph Sprint ST 1050
Previous: GSF600s, GS500, BMW F45, CG125

[Frost]

https://www.timeatlas.com/5_minute_tips/general/preventing_excel_divide_by_0_error

[c_dug]

Can't upload the spreadsheet, contains data the company probably deems as sensitive.

I may do another one as an example if I can be bothered, that's a little too much like work for me to be doing at home though!

I'm not sure if it will make much difference but what we have at work technically isn't excel its "open office calc" it's more like excel from windows '98. All the same formulas work though, in fact, I haven't used '98 in quite a while but I wouldn't be surprised if this actually was excel from '98 just re-branded.

How do I upload a spreadsheet? I have nothing better to do I may as well throw one together, it wont take 10 minutes.
____________________
I am a bellend, I am a man of constant sorrow, I am a gummy bear, I am a rock.

[herulach]

amend your formula to IF(ISERR(B4/C4),"-",B4/C4)

That will stick a dash in the cell if you are getting an error. Then you can amend the conditional format to show that up seperately.
____________________
YBR 125>FZS600

[c_dug]

Sweet, thanks.

Tried making the formula but it turns out the latest excel is smart enough not to read the #DIV/0! error as less than 0.

thanks everybody
____________________
I am a bellend, I am a man of constant sorrow, I am a gummy bear, I am a rock.

[c_dug]

Hi guys,

Still having no luck with this, the open office forumulas are different to the excel ones.

Openoffice is giving me an if statement prompt think which shows:

=IF(Test;then_value;otherwise_value)

Not sure what that is trying to get me to do exactly, however it does provide an IF statement creator from which I made:

=IF(B6/C6=0;"none";B6/C6)

Im still getting the #DIV/0! messege so I'm guessing I've done some of the formula wrong, assuming openoffice swaps commas for semicolons but keeps the rest of excel's rules, can you see any mistakes in my forumla?

Any other suggestions?

Thanks
____________________
I am a bellend, I am a man of constant sorrow, I am a gummy bear, I am a rock.

[bazza]

[herulach]

If you're doing it that way, then you need to check if C6 = 0. B6/C6 will never = 0 so you're always putting the division there.
____________________
YBR 125>FZS600

[c_dug]

Couldn't get the formulas to work so in the end I used 0.00000001 instead of 0, that way it rounds down to zero anyway.
____________________
I am a bellend, I am a man of constant sorrow, I am a gummy bear, I am a rock.