Conditional formating

[Stephen Robinson]

Hi,

I've worked out how to do conditional formating when "x<y
go red" but how do you get "if x<y then display y"

We are having a problem with stats going over 100% so what
I want is that if it calulates over 100% then to only
display 100% and not the correct figure.

Thanks
Stephen

 

[William]

Stephen

I assume you have a row of numbers, say B1:J1, that should total 100% when
aggregated but you are encountering rounding differences when the total is
calculated in, say, cell K1

If its just a presentation issue, then select the Column K, select
"Format>Cells>Number>Custom" from the Excel toolbar and enter the following
custom number format in the "Type" box:
[<1]0.00%;"100.00%"

--
XL2002
Regards

William
(e-mail address removed)

| Hi,
|
| I've worked out how to do conditional formating when "x<y
| go red" but how do you get "if x<y then display y"
|
| We are having a problem with stats going over 100% so what
| I want is that if it calulates over 100% then to only
| display 100% and not the correct figure.
|
| Thanks
| Stephen

 

[Bob Phillips]

Stephen,

Conditional formatting can only manipulate the format, not the value.

For this, you need to accommodate date it in the formula, for instance

=IF(myformula>100%,100%,myFormula)

or

=MIN(100%,myFormula)