Custom Number Formatting

[Sam_Daniels]

What I need to do is have a specific column show as a percentage if it will be 100% or less, or change into currency if higher than that. I'm not sure if it is possible, however, it's worth a try!

 

[RoryA]

Welcome to the forum.

You could use conditional formatting, or just a custom number format like this:

[<=1]0%;$#,##0

 

[oldbrewer]

Can you tolerate a helper column eg col D with the numbers in, if so in A1

=if(D1<100.0001,d1&"%","$"&d1)

 

[wideboydixon]

It's not possible with number formatting because you can only specify different formatting for positive / negative / zero / text. You could do it using a formula in a new cell:

Book1
	A	B
1	2	£2.00
2	1	100.00%
3	0.5	50.00%
4	1.1	£1.10
5	10	£10.00
6	0	0.00%
Sheet1

Cell Formulas
Range	Formula
B1	=IF(A1<=1,TEXT(A1,"0.00%"), TEXT(A1,"£0.00"))

WBD

 

[wideboydixon]

Welcome to the forum.

You could use conditional formatting, or just a custom number format like this:

[<=1]0%;$#,##0

Strike my post! I never knew you could do that!!

WBD

 

[Sam_Daniels]

Thanks a bunch! That worked wonders! Appreciate the help.

 

[RoryA]

Yup, you can use up to 2 simple conditions in a custom number format.