Action depending on contents of a cell

Topic

New Reply

This is so straightforward and simple that I’m embarrassed that I can’t find the answer for myself!

I want have a formula in a second cell, whose result depends on the contents of a first cell, as in (trivial example):

IF cell A1 is blank THEN leave cell B1 blank ELSE multiply contents of cell A1 by 10 and place the result in cell B1. Please could you give me a second formula (or more complex single formula) which covers the case where cell A1 is zero (not blank) but I still want to end up with a blank cell B1.

Reply | Quote

Replies

if I read your post correctly, =IF(A1=0,””,A1*10) should get you started.

Brooke

Reply | Quote

Brooke: your formula works brilliantly (hangs head in shame!) John

Reply | Quote

John,

If a cell is zero, and you want blanks displayed, just format the cell.

Ie:
The formula in cell B1 is “=A1*10”

Then format cell B2:
Format, cells, “number” tab, custom, and under type, enter :
0;-0;
That means if the cell is postive, show the number as a whole number; if negative, show the number with a minus sign; if zero, don’t show anything.

(that’s to display integers- you can change the formatting for other cases- eg:
0.00;-0.00;

Brooke’s example ‘=if(A1=0,””,A1*10)’ works fine- but there’s a danger that a cell using B2 for a calculation will fall over.

If A1 was empty, as opposed to blank, then “=A1*10” would work fine in B2 anyway.

Reply | Quote

Geoff – thanks, your suggestion is good. I want to set up the (same) formula in the cells of a column, and just make the values appear in that column, working progressively downwards, whenever the cell to the immediate left is given a value, otherwise it should look as if there is nothing in the cells (IYSWIM!).

Reply | Quote

John,

I realsied after I posted that there’s another option.

Under Tools, Options, under the “View” tab, there’s a “zero options” box. Untick that, and you won’t have any zero values showing on the sheet at all.

That might not be an advantage if you want to show zeroes elsewhere on the sheet, but it’s worth bearing in mind.

Reply | Quote