How to display true 0 when source is 0 and blank is blank?

[melzjm]

Hi again,


Thank you everybody for being kind. It helps me go through my relationship with excel a bit better everyday.


I have this formula (='Test.xlsx'!$E$14) to return the value of another sheet's cell.

My problem is whenever the source cell is blank , it'll return 0 and if the value of the source cell is a true 0, it'll return 0 too.


I have tried to conditional formatting, I ended up, making all 0s blanks including the true 0s.

I used =if('Test.xlsx'!$E$14=0,"0.00%",'Test.xlsx'!$E$14) to force all 0s to display ).0% but it displays 0.0% with blanks too.


Any input is appreciated.

Thanks,

Melzjm

 

[Debraj]

Hi melzjm,


You have set your excel to display "blank" as "0", thats why its showing all "blanks" as "0"..


What.. you have not set you Excel.. !! then why ..

Excel > Option > Advance > Display Option for this Worksheet > "Show Zero in Cells that have ZERO value" is checked in your PC..


BTW..

What exactly do you want..


* Display 0 as 0%, Blank as Blank and Number as Number% then use the below..

=IF(LEN('Test.xlsx'!$E$14)>0,('Test.xlsx'!$E$14 & "%") * 1,"")

or please supply some demo data of your output..


Regards,

Deb

 

[Montrey]

=IF(ISBLANK('Test.xlsx'!$E$14),"",IF('Test.xlsx'!$E$14=0,0))

 

[SirJB7]

Hi, melzjm!

I think that it should be like this:

=IF('Test.xlsx'!$E$14)="","",'Test.xlsx'!$E$14)

Display blank even if it has a formula which should display blank, and display anything including zero if properly formatted when other values.

Regards!