Solved

# Excel function problem

Posted on 2011-05-05
323 Views
Hi  Experts,

How can i hidden all the "0" in  Sheet M1   Cell D34  , L49: L51 .

Stanley
FORMAT.xlsm
0
Question by:candychan611

LVL 37

Expert Comment

You could use conditional formatting where you set the text color equal to the cell background when the value is 0, thus 'hiding' it :)
0

LVL 33

Assisted Solution

Change your formula on cell D3 for this:

=CONCATENATE(IF(O34=0,"",O34),"   ",IF(P34=0,"",P34),"   ",IF(Q34=0,"",Q34))

jppinto
0

LVL 43

Expert Comment

In excel 2003

Tools > options > view > zero value

In excel 2010

File > Options > Advanced > Show a zero value in cells that have zero value.
0

Accepted Solution

Replace D34 with

=CONCATENATE(IF(O34=0," ",34),"   ",IF(P34=0," ",P34),"   ",IF(Q34=0," ",Q34))

Replace L49 with

=IF(O34="","",CONCATENATE(IF(O34=0," ",O34),"   ",IF(O30=0," ",O30),"  ",IF(O39=0," ",O39)," ",IF(P39=0," ",P39)))

Replace L50 with

=IF(P34="","",CONCATENATE(IF(P34=0," ",P34),"   ",IF(P30=0," ",P30),"  ",IF(O40=0," ",O40)," ",IF(P40=0," ",P40)))

Replace L51 with

=IF(Q34="","",CONCATENATE(IF(Q34=0," ",Q34),"   ",IF(Q30=0," ",Q30),"  ",IF(O41=0," ",O41)," ",IF(P41=0," ",P41)))
0

LVL 33

Expert Comment

@ssagibh: That would work for cells that were displaying one zero only...in this case, we have a concatenate that is showing a string with 3 zeros, it's not the same.

@PMGreensted: That formula is exactly what I posted before...
0

Expert Comment

@jppinto: Calm down, I was typing out the answer before I spotted your reply. Not my understanding of exactly the same, but in effect the same solution.
0

LVL 33

Expert Comment

My formula:

=CONCATENATE(IF(O34=0,"",O34),"   ",IF(P34=0,"",P34),"   ",IF(Q34=0,"",Q34))

=CONCATENATE(IF(O34=0," ",34),"   ",IF(P34=0," ",P34),"   ",IF(Q34=0," ",Q34))

The only difference is that you are leaving an extra space if any of the cells are eqqual to 0 with your IF function, instead of IF(condition, ""...) you are putting " ", leaving an extra space on the result, that shouldn't be there, so your formula is not correct.
0

Author Comment

Thanks  experts's helping XD
0

LVL 33

Expert Comment

@candychan611: It's not a question of points, but you accepted a solution that is not completly right! As I mentioned before, those formulas will leave you extra spaces on your concatenated string if the values on the cells that are beeing concatenated are empty!
0

Expert Comment

@candychan611: You're welcome.

@jppinto: You’re making an assumption that the 0’s were to be removed when the question asked for them to be hidden. We’re dealing with concatenated output here so it might actually be better to have spaces for consistency. If I were hiding a 0 in a single cell without concatenation I would use “”.
Also, the question required the formulas for 4 cells in the spreadsheet. By only giving one example you’re giving an example of how to solve the question but not supplying the full solution.
0

LVL 33

Expert Comment

"not supplying the full solution"...but I provided at least part of the solution, and I didn't even got an "assisted solution" here!
0

Expert Comment

jppinto: I think you did. Look again.
0

LVL 33

Expert Comment

Upsss... :)

I posted too many times today that I confused questions.. LOLOL
0

## Featured Post

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.

#### Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!