Format a cell that contains a 0 or a number that would round to 0 to apply a dash (-)

[cmorten82]

I want to format a cell or an entire column to have a dash in it when the cell contains a zero how ever I want it to apply the dash (-) when it rounds because I am not showing decimals. current if I put 0.05 in a cell it rounds it down to zero and in that cell it shows "0" and if I put a -0.05 in that cell it appears as a negative zero like so .(0) I have attached a couple of screen shots the one on the right is what it looks like when a number like 0.05 is entered and the one on the left is what I would like it to look like if I entered that same number in. I am think custom formatting would work which is what I currently use and it works so long as the amount I put in is .50 or larger or -.50 or smaller.

[Glenn Kennedy]

You don't like making it easy for us, do you? jpgs are of little use. it would be much easier if you had posted an actual sheet.

However, try:

=if(abs(your equation used to calculate variance)<1,"-",your equation used to calculate variance)

If that doesn't work, then please attach an ACTUAL Excel sheet.

[cmorten82]

Sorry, I have attached an actual copy. The formula kept giving me an error. What I previously did is format the column as currency then remove the decimals and the dollars signs and then I go into custom and slect the type shown in the screen shot I have attached and following the #,##0_);(#,##0) I put a ;- This then formats the cell to produce a - in the place of a zero so long as the zero exactly zero and and not 0.01 or or more or -0.01 or less

[Glenn Kennedy]

Mmm. Actually your sheet isn't much use either, as it contains no formulae, just links to another sheet that you haven't posted...

However,

Take a look at the first three rows. On the sheet where the variance is ACTUALLY CALCULATED, use this formula to calculate the variance: =IF(ABS(F8-E8)<1,"-",F8-E8) adapted for whatever calculation & ranges you are using. I've expanded the number of decimal places in columns E & F so you can see that it works!!

[cmorten82]

Fantastic! this works great thank you!

[Glenn Kennedy]

Woo Hoo!!! can you mark the thread as solved and (preferably) click the Add Reputation button at the foot of this post?