Show cells as blanks

[Wookie]

I have a formula in which if the cell gives a zero i want it to be blank, i can do this by conditional formatting but what i want is if the month has ACT as a heading then i still want it to show zero but if it has FCAST as a hearing i want it to show as blank if formula result is zero

 

[Andrew Poulsom]

Format your FCAST column as eg "0;-0;" (no quotes).

The first part is for positives, the second part is for negatives and the third part is for zeroes. You can use what you want for parts 1 and 2, but be sure to include the last semicolon to indicate that zeroes are not shown.

 

[Wookie]

Here is formula, the column headings change between ACt and Fcast depending on which month we are in.

SUMIF('[Company.xls]OD'!$C$5,"=Act",'[Company.xls]OD'!C7),

 

[Andrew Poulsom]

I don't know why you are using SUMIF on only one cell. You could try:

=IF('[Company.xls]OD'!$C$5="Act",IF('[Company.xls]OD'!C7<>0,'[Company.xls]OD'!C7,""),"")

which will show the "" (Null) value instead of zero.

Or you could use Conditional Formatting. Assuming your heading is in cell A1 and your data is in the range A2:A4, select the data cells, choose Format|Conditional Formatting:

Condition 1 Formula is:

=AND(A$1="FCAST",A2=0)

click Format, set the Font Color to white and click OK twice.