Custom Number Format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

KLondike-5 - 23 May 2005 22:41 GMT

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.

Reply to this Message

Dave Peterson - 23 May 2005 23:15 GMT

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.)

> 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
[quoted text clipped - 6 lines]
> due to their larger size. The version is Excel 2000. Any assistance is
> appreciated.

Signature

Dave Peterson

Reply to this Message