Hide Null Values

[Eddieduce]

I have created a spread sheet that contains formulas that show the number signs when it identifies a null value. How can that be made to not show until a value is there?

[BigBas]

"" indicates a blank.

So, if you have a formula in a cell, you can show blank until there is a value.

For example:

=IF(A1="","",A1)

[Eddieduce]

Can that same formula be applied to negative values? In the Workbook, the data is placed in a column, formulas calculate a value, but other colums with formulas give the # because there is a negative value not present until the next update period.

[BigBas]

Without seeing the actual data/information, it is difficult to give the best answer. The concept should work universally.

For example, if cell A1 is negative, and you want to leave cell B1 blank until A1 is positive, you can use a formula like:

=IF(A1<0,"",A1)

Obviously, you'd rework the formula to suit your needs.

[Eddieduce]

It would be kind of complicated. In my workbook, I am dealing with data in time format, imported to cells as text. The reason for this is that the data I am importing is in time format ##:## but does not stop/restart at 24 but continues increasing. This is what I have been using:

Column H1=0:00
Column K2=SUM(J2-H22)
Column M2=SUM(M2-J2)

If K2 is null, I still get the #VALUE! error. I am trying to get blank if there is a null or negative.

[Bryan Hessey]

It would be kind of complicated. In my workbook, I am dealing with data in time format, imported to cells as text. The reason for this is that the data I am importing is in time format ##:## but does not stop/restart at 24 but continues increasing. This is what I have been using:

Column H1=0:00
Column K2=SUM(J2-H22)
Column M2=SUM(M2-J2)

If K2 is null, I still get the #VALUE! error. I am trying to get blank if there is a null or negative.

Hi,

If you are dealing with Hours then I presume that you have formatted the cells to [h]:mm:ss to accomodate the 24+ numbers, in which case 'Sum' that includes blank cells should not error.

To avoid an error in M2 use something like

=If(J2>M2,"",sum(M2-J2))

for K2 a similar test.

hth
---