isblank or other solution?

[Peacedout]

In the attached file, I want Current B2 to be blank instead of $0 if there is no Fee entered in Projects B2. The only way I've been able to do that is to hit the spacebar in Projects B2, but I feel that's not a great solution.

I want Current B2 to be blank so that someone can write an amount (in the blank cell, instead of on top of "$0") on a hard copy for me to input into Projects B2.

Thanks in advance.

Dee

 

[Mike H..]

In the attached file, I want Current B2 to be blank instead of $0 if there is no Fee entered in Projects B2. The only way I've been able to do that is to hit the spacebar in Projects B2, but I feel that's not a great solution.

I want Current B2 to be blank so that someone can write an amount (in the blank cell, instead of on top of "$0") on a hard copy for me to input into Projects B2.

Thanks in advance.

Dee

Hi,

You can use this.

=IFERROR(IF(LEN(INDEX(Projects, MATCH($A2,Projects[Project Number],0),2))=0,"",INDEX(Projects, MATCH($A2,Projects[Project Number],0),2)),"-")

 

[prasaddn]

Hi,

You can try this:
=IFERROR(IF(ISBLANK(INDEX(Projects, MATCH($A2,Projects[Project Number],0),2)),"",INDEX(Projects, MATCH($A2,Projects[Project Number],0),2)), "-")

This will help to know if the project is really $0 or have nothing. Good question.

Regards,
Prasad DN

 

[Peacedout]

Oh...you guys. I probably could have worked on that for six weeks and never figured it out. Thanks so much! And prasaddn, good point.

 

[jeffreyweir]

@All: you don't have to use a formula to hide those zeros at all. Instead, just apply a custom number format to the cell that suppresses zeros.

This is actually a much better way to go than using formulas because

1. there's no extra processing power required to get Excel to suppress zeros (whereas using formulas to do this can become problematic if you have many thousands of formulas); and
2. it makes for much shorter formulas altogether, meaning other people are more likely to understand what your formula is doing. That is, they can concentrate on understanding the actual formula rather than potentially being confused by the formatting you're applying within that formula.

See the following links for more details:

• http://office.microsoft.com/en-nz/excel-help/create-a-custom-number-format-HP010342372.aspx
• http://jonvonderheyden.net/excel/a-comprehensive-guide-to-number-formats-in-excel/

 

[Khalid NGO]

Hi Paecedout,

Just adding another option:
You can apply CF for = 0
Font color = White

However i like Jeff's approach.

Regards,

 

[Peacedout]

Thanks, all, for your varied and useful approaches to solving the problem!