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

Try

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

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

"michael.beckinsale" wrote:

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

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.



"michael.beckinsale" wrote:

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

--

Dave Peterson

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

IF(OR((A1<-1),(A11)),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:

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

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.

"michael.beckinsale" wrote:

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

--

Dave Peterson