Can't use formulas within formulas?

  • Thread starter Thread starter Michael S. Rosen
  • Start date Start date
M

Michael S. Rosen

I tried subtracting one cell from another and received a #VALUE! error. One
of the cells contains the formula "=IF(D28/D$11*100<0.01,"-",D28/D$11*100)."
I wrote it that way because if the number is insignificant (below 0.05) I
want it to display as a dash for 0. If I try to reduce the decimal places,
Excel still considers it a positive number because the extra digits are only
hidden and just displays 0.

I tried ROUNDUP and that didn't work. ROUND, however, does appear to work.
The only concern I have is consistency. I want to be sure these formulas
work each year, whatever the values may be. If that last figure ever
becomes a larger one, the formula may not work and may display a number with
incorrect rounding.

Why does one formula work in the subtraction but not another?

Thanks,
Mike
 
#VALUE was displayed because "-" is NOT a value, it is a
character.
Change your formula to:
"=IF(D28/D$11*100<0.01,0,D28/D$11*100)"
and
Create a custom format for the cell of "-#" (no "s) where
you were displaying "-".

HTH
 
Back
Top