replacing null and zero values

Discuss the spreadsheet application
Post Reply
grooveman
Posts: 1
Joined: Fri Dec 21, 2007 5:28 pm

replacing null and zero values

Post by grooveman »

Hi.

I have been trying for some time now to reformat all zeros and null values to a "-" character.

What I mean by "null" is either a cell that is emtpy, or when a formula or equation returns a nonsensical value, such as: "#DIV/0!", "#VALUE!" or some such similar thing.

I don't want to see those big, ugly errors all over my spreadsheet, I would prefer to simply see a "-". How is this accomplished?

Thank you!

G
TerryE
Volunteer
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: replacing null and zero values

Post by TerryE »

In Calc you also see the Err:XXX codes. In calc a cell has its contents, which may be a formula which itself my return a value. You then have the formatted value that are displayed within the controller view -- that is what you see in the GUI. You have quite a lot of control over how contents are formatted through use of Format codes in the Format Cells options (see the online help on this topic for more details), but Errors will display as errors unless you trap them with an =IF(ISError(expr)...) check, or the equivalent Informational functions.
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
Post Reply