Conditional Number Format

  • Thread starter Thread starter Alan Cocks
  • Start date Start date
A

Alan Cocks

Is it possible to do a conditional number format?

Certain cells are sometimes shown in metres and sometimes in kilometres.
When they are metres the format is # ### ##0" m"

When they are km the format is # ##0.000" km"

It depends on whether the heading is "co-ordinates" (m) or "distance" (km)

Alan
 
Alan Cocks said:
Is it possible to do a conditional number format?

Certain cells are sometimes shown in metres and sometimes in kilometres.
When they are metres the format is # ### ##0" m"

When they are km the format is # ##0.000" km"

It depends on whether the heading is "co-ordinates" (m) or "distance" (km)

Alan
Click to expand...

You will need two different formats, each applied to the appropriate cells
(or columns).
 
Paul said:
You will need two different formats, each applied to the appropriate cells
(or columns).
Click to expand...
Paul
I need to have the two formats as a conditional format in the one cell.
Alan
 
Alan -

Select the cells, press Ctrl-1 (one) to format, click on the Number tab,
select Custom from the bottom of the left hand list, and type this into
the box at the right:

[<10000]# ##0" m";# ##0.000," km"

- Jon
 
Jon

Thanks for your reply - something new I have learned.
Is it possible to do a conditional number format (like this one) based on
the value of another cell?

Alan


Jon Peltier said:
Alan -

Select the cells, press Ctrl-1 (one) to format, click on the Number tab,
select Custom from the bottom of the left hand list, and type this into
the box at the right:

[<10000]# ##0" m";# ##0.000," km"

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Alan said:
Is it possible to do a conditional number format?

Certain cells are sometimes shown in metres and sometimes in kilometres.
When they are metres the format is # ### ##0" m"

When they are km the format is # ##0.000" km"

It depends on whether the heading is "co-ordinates" (m) or "distance" (km)

Alan
Click to expand...
Click to expand...
 
Alan -

As far as I know, you can only refer to the cell's own value. You could
of course revert to a VBA solution....

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Alan said:
Jon

Thanks for your reply - something new I have learned.
Is it possible to do a conditional number format (like this one) based on
the value of another cell?

Alan


Alan -

Select the cells, press Ctrl-1 (one) to format, click on the Number tab,
select Custom from the bottom of the left hand list, and type this into
the box at the right:

[<10000]# ##0" m";# ##0.000," km"

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Alan Cocks wrote:

Is it possible to do a conditional number format?

Certain cells are sometimes shown in metres and sometimes in kilometres.
When they are metres the format is # ### ##0" m"

When they are km the format is # ##0.000" km"

It depends on whether the heading is "co-ordinates" (m) or "distance"
Click to expand...
(km)
Alan
Click to expand...
Click to expand...
Click to expand...
 
Thanks Jon

I thought this was probably the case.

Alan
Jon Peltier said:
Alan -

As far as I know, you can only refer to the cell's own value. You could
of course revert to a VBA solution....

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Alan said:
Jon

Thanks for your reply - something new I have learned.
Is it possible to do a conditional number format (like this one) based on
the value of another cell?

Alan


Alan -

Select the cells, press Ctrl-1 (one) to format, click on the Number tab,
select Custom from the bottom of the left hand list, and type this into
the box at the right:

[<10000]# ##0" m";# ##0.000," km"

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Alan Cocks wrote:


Is it possible to do a conditional number format?

Certain cells are sometimes shown in metres and sometimes in kilometres.
When they are metres the format is # ### ##0" m"

When they are km the format is # ##0.000" km"

It depends on whether the heading is "co-ordinates" (m) or "distance"
(km)

Alan
Click to expand...
Click to expand...
Click to expand...
 
Back
Top