Excel function problem

Hi  Experts,

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


Stanley
FORMAT.xlsm
candychan611Asked:
Who is Participating?
 
PMGreenstedConnect With a Mentor Commented:
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
 
Gerwin Jansen, EE MVETopic Advisor Commented:
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
 
jppintoConnect With a Mentor Commented:
Change your formula on cell D3 for this:

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

jppinto
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Saqib Husain, SyedEngineerCommented:
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
 
jppintoCommented:
@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
 
PMGreenstedCommented:
@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
 
jppintoCommented:
My formula:

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

Your formula:

=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
 
candychan611Author Commented:
Thanks  experts's helping XD
0
 
jppintoCommented:
@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
 
PMGreenstedCommented:
@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
 
jppintoCommented:
"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
 
PMGreenstedCommented:
jppinto: I think you did. Look again.
0
 
jppintoCommented:
Upsss... :)

I posted too many times today that I confused questions.. LOLOL
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.