custom formats or formula??? a tough one

  • Thread starter Thread starter TC
  • Start date Start date
T

TC

have cols of data (one would be percent change) where I
would divide. I want all of the cells to display right
aligned. I also want to change the "division by zero"
result to be changed to a "-", and centered. I can do it
when I want a dollar sign or no sign...with an if statment
to change the div by zero and the accounting format.
I want to it to occur on the percent columns as well, and
can't figure out how...the "-" never comes out centered.
I think I just need a custom format but can't seem to get
it.
Please help!
..
 
One option is to format the column right justified, then format each cell
with a hyphen in it to centered.

If you want something more automatic, another option is:

Assume c1 is the percent change between a1 and b1.

c1 = if(b1=0, "- ",(a1-b1)/b1)

Format the column as percent, right justified. Adjust the trailing spaces to
center the hyphen. This will work if your column width is static.

If none of these work, it sounds like you will need a VBA program, which is
beyond my capability.
 
Back
Top