Conditional Formatting .000's and %'s !

[acc58]

Hello all,

I would like to used conditional formatting to turn values which are less than 1 to a %.

The reason I am doing this is I have this enormous data set my boss gave to me and so I am setting up sort of a box with a slider on the top, and when you slide it it activates the offset function which is linked with the cell yadda yadda yadda,... you get the point (yeah, the point is my boss hates me).

Anyways, is there any way that excel can perform the following function:
-In a single cell, the value is 12,345,876.01 and suddenly it switches to .032 and poof! it turns that into 3.2%.

This is my favorite website.

Thanks in advance,

Alex

[davesexcel]

Hello all,

I would like to used conditional formatting to turn values which are less than 1 to a %.

The reason I am doing this is I have this enormous data set my boss gave to me and so I am setting up sort of a box with a slider on the top, and when you slide it it activates the offset function which is linked with the cell yadda yadda yadda,... you get the point (yeah, the point is my boss hates me).

Anyways, is there any way that excel can perform the following function:
-In a single cell, the value is 12,345,876.01 and suddenly it switches to .032 and poof! it turns that into 3.2%.

This is my favorite website.

Thanks in advance,

Alex

I laughed ....Funny Funny.....
Anyway'

the value is 12,345,876.01 and suddenly it switches to .032 and poof! it turns that into 3.2%.

How is this supposed to happen?
What makes the value become .032?

[acc58]

It turns into that becasue the cell is linked to a large database, through an offset function. The database hold 897,987 numbers and .34 numbers. Thats why.

Alex

[davesexcel]

It turns into that becasue the cell is linked to a large database, through an offset function. The database hold 897,987 numbers and .34 numbers. Thats why.

Alex

so we have 897,987 numbers
and we have .34 numbers
----------------------------
Maybe this isn't the question

suddenly it switches to .032 and poof! it turns that into 3.2%.

To format the cells to percent, you can highlite the range to format,go to the format menu and select cells
select the numbers tab and
then select percentage

[Mark@Work]

Is this the sort of thing you are after?

=IF(ABS(A1)>1,TEXT(A1,"general"),TEXT(A1/100,"general%"))

Mark.

[MrShorty]

A number format code of [<=-1]-0.000;[<1]0.0%;0.000 may also be of use to you. Note that I've excluded 1 from being formatted as 100%.

[acc58]

Thanks everybody for your help. Dave Morrison's answer, pure genius. Haha

AC