Custom Number Format

  • Thread starter Thread starter KLondike-5
  • Start date Start date
K

KLondike-5

I'm trying to set a cell format to either $ or % determined by the value
input by the selection of one of two lists dependent on a $ or % selection
criteria in another cell. If the value input is within the range of -1 to
1, I need it to display in a percentage format. If it is less than -1 or
greater than 1, it needs to display as a currency format (or comma w/ 2
decimals).

I prefer using data validation lists on this sheet instead of other controls
due to their larger size. The version is Excel 2000. Any assistance is
appreciated.
 
You could use a helper cell to show the format:
=TEXT(A1,IF(ABS(A1)>1,"$#,##0.00","0.00%"))

Or you could use a custom format:
[<-1]$#,##0.00;[<=1]0.00%;$#,##0.00

(I'd turn off tools|options|Edit tab|"enable automatic percent entry", though.)
 
Back
Top