Number Format - Combine Currency Symbol & Scale
 

Hi,

There are 4 related questions:-

- Is it possible to combine [] conditions in a number format string? e.g
[=1000000][$$-409]
- If there are multiple [] conditions, is the order of precedent from left
to right?
- Is the maximum number of format fields 4? i.e., it's normally Positive;
Negative; Zero; Text
- What happens to the other format fields if I use the first two up with
[=1000000] <some formating text; [=1000] <some formatting text?

The reason I'm asking the questions, is that I'm trying to construct a
function that will format numbers in the form;

$ 1M (for numbers <= 1,000,000)
-$ 100K (for numbers between 1,000 and 1,000,000
$ - (for zero)

I have the additional challenge of repeating for ‚¬, Â¥ & £

Best regards


Mark

Number Format - Combine Currency Symbol & Scale
 

you can do all you need with a customised function and using SELECT / END
SELECT using CASE statements for each kind of number


"Mark Nethercott" wrote in
message ...
Hi,

There are 4 related questions:-

- Is it possible to combine [] conditions in a number format string? e.g
[=1000000][$$-409]
- If there are multiple [] conditions, is the order of precedent from left
to right?
- Is the maximum number of format fields 4? i.e., it's normally Positive;
Negative; Zero; Text
- What happens to the other format fields if I use the first two up with
[=1000000] <some formating text; [=1000] <some formatting text?

The reason I'm asking the questions, is that I'm trying to construct a
function that will format numbers in the form;

$ 1M (for numbers <= 1,000,000)
-$ 100K (for numbers between 1,000 and 1,000,000
$ - (for zero)

I have the additional challenge of repeating for ‚¬, Â¥ & £

Best regards


Mark

Number Format - Combine Currency Symbol & Scale
 

Thanks Patrick. I imagine that you can do it that way, but I'm using the cell
styles (2007) to allow me to easily change the currency format (from £ to $
or $ ‚¬) to cells in several workbooks and didn't want to have to wrap any
calculations in a format statement. Using a style seemed to be more in
keeping.

If it turns out the answer is an either/or - either currency or scaled, then
I can think again.

I was also trying to extend my knowledge on using the custom format strings
and have come across this issue in the past & had not been able to get to a
clear answer.

"Patrick Molloy" wrote:

you can do all you need with a customised function and using SELECT / END
SELECT using CASE statements for each kind of number


"Mark Nethercott" wrote in
message ...
Hi,

There are 4 related questions:-

- Is it possible to combine [] conditions in a number format string? e.g
[=1000000][$$-409]
- If there are multiple [] conditions, is the order of precedent from left
to right?
- Is the maximum number of format fields 4? i.e., it's normally Positive;
Negative; Zero; Text
- What happens to the other format fields if I use the first two up with
[=1000000] <some formating text; [=1000] <some formatting text?

The reason I'm asking the questions, is that I'm trying to construct a
function that will format numbers in the form;

$ 1M (for numbers <= 1,000,000)
-$ 100K (for numbers between 1,000 and 1,000,000
$ - (for zero)

I have the additional challenge of repeating for ‚¬, Â¥ & £

Best regards


Mark