Custom Cell Format - Please help!

[michael.beckinsale]

Hi All,

I am trying to format cells so that if the resultant % is >100% or <
100% then the cell displays *** Negative values need to be shown in
(%) rather than prefixed -

The following works with the exception that it will not display
negative values as (%)

[>1]"***";[<-1]"***";0.0%;-

Please can anybody come up with the right syntax?

I need to avoid using IF statements in the formulas

Regards

Michael

 

[Andrea Jones]

Try

[=1]0%;[<0](0.0%);"***"

Andrea Jones
www.stratatraining.co.uk
www.wrekinpublishing.com

 

[Dave Peterson]

If Andrea's response doesn't help, you may want to rephrase your question.

If the result > 100% or < 100%, show asterisks looks like you only want to see
the percentage at 100%--everything else shows asterisks.

Hi All,

I am trying to format cells so that if the resultant % is >100% or <
100% then the cell displays *** Negative values need to be shown in
(%) rather than prefixed -

The following works with the exception that it will not display
negative values as (%)

[>1]"***";[<-1]"***";0.0%;-

Please can anybody come up with the right syntax?

I need to avoid using IF statements in the formulas

Regards

Michael

 

[michael.beckinsale]

Hi All,

Sorry for the confusion.

This is what l mean:

0 to 1 Cell displays % ie 0% to 100%

1 Cell displays ***

0 to -1 Cell displays % ie (0%) to (100%)
< -1 lets say (126%) Cell displays ***

Andrea, your example put me on the right track but l still cant quite
get it right!

Hope you & Dave can help me get it sorted.

Regards

Michael

 

[Marcelo]

IF(OR((A1<-1),(A1>1)),REPT("*",3),A1)

replace A1 for your fuction if it is need and format the cell as %

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"michael.beckinsale" escreveu:

 

[Dave Peterson]

I don't think you can use this many parts in a custom format.

You could use another cell containing a formula -- like Marcelo suggested.

Or you could use a worksheet event that changes the formatting.

If you want to try...

Right click on the worksheet tab that should have this behavior. Select view
code and paste this into the code window that just opened:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'one cell at a time
If Target.Cells.Count > 1 Then
Exit Sub
End If

'only look at A2:A10
If Intersect(Target, Me.Range("A2:A10")) Is Nothing Then
Exit Sub
End If

If IsNumeric(Target.Value) = False Then
Target.NumberFormat = "General"
Else
'< -1 Cell displays ***
'0 to -1 Cell displays (#0%)
'0 to 1 Cell displays #0%
'>1 Cell displays ***
Select Case Target.Value
Case Is < -1
Target.NumberFormat = ";""***"";;"
Case Is < 0
Target.NumberFormat = ";(#0%);;"
Case Is < 1
Target.NumberFormat = "#0%"
Case Else
Target.NumberFormat = """***"";;;"
End Select
End If

End Sub
I only looked at the range A2:A10. Change that to what you need.