Sunday, 16 December 2012

Zeros in spreadsheets are ugly - Nested IF() Statements

So I'm sure you're all aware of the formula's in my existing and previous spreadsheets has featured a lot of nested IF() statements.  The reason for them is to hide zero's where ever they may cause eye strain.

I hate zeros on a spreadsheet.

They are not needed, they tell you nothing a blank cell can't.

Here's a simple statement to hide a zero.

This will bring a blank cell if the named cell A1 has a zero in it, or is blank, otherwise it will print the word "FOOD!!" where ever you type the formulae.  It didn't have to be blank of course, you could add "Get Some Food" between the quotation marks and it would tell you to do that instead, so that's a simple one.  Things have gotten more interesting lately, take a look at the EIO: Refinery sheet for some extreme examples.

So I've been busy today focusing on the Time and Building formulae both for Raw and Extra materials, and now I've arrived at the stage where I need to add up the possible two figures.


You can't add text and a number together to get a numerical result.  It's just not happening.

That drives even more nested if() statements.

Here's the flow chart I'm about to use to help follow the logic path.  No apologies made about my handwriting skills, it's a sketch not a calligraphy art project.

Believe it or not, I've never tried this method before so I hope it works.  T = True, F=False.

edit: Works like a charm!

No comments:

Post a Comment