[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

Excel function problem

Hi  Experts,

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


Stanley
FORMAT.xlsm
0
candychan611
Asked:
candychan611
2 Solutions
 
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
 
jppintoCommented:
Change your formula on cell D3 for this:

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

jppinto
0
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
PMGreenstedCommented:
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
 
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now