Number Format - Combine Currency Symbol & Scale

[Mark Nethercott]

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

 

[Patrick Molloy]

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]

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.

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

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