Conditional, custom number formatting

[Anon9149]

Hi all,

With respect to the attached document. I have raw data (column C), which is divided by the divisor (column E) to give a rounded result (column F). There are three divisors; 1, 1,000 and 1,000,000 which correspond to three DV list selections; 'No rounding', 'Thousands' and 'Millions'.

The values in column F are custom formatted (see below image). I have created this formatting because I am using this for rounding financial statements and when the 'No rounding' and 'Thousand' options are selected, it is useful to have no values shown after the decimal points otherwise the statements would look too busy, and therefore difficult to interpret.
However, when the 'Millions' option is selected, values get rounded to the nearest million, so it becomes useful to have the two values displayed after the decimal points in this instance.

My question therefore is whether it is possible to have conditional, custom formatted number types based on the selections made in the DV list i.e.
- IF 'No Rounding' or 'Thousands' is selected, number formatting should be _(* #,##0_);[Red]_(* (#,##0);_(* "-"_);_(@_)
- IF 'Millions' is selected, number formatting should be _(* #,##0.00_);[Red]_(* (#,##0.00);_(* "-"_);_(@_)

Thanks in advance

 

[p45cal]

In the attached I've put conditional formatting which looks at column D values. It might be better to get the conditional formatting to look at column E instead.

I haven't changed the (non-conditional) formatting of column F, but you might want to leave it as General.

 

[Anon9149]

Thanks p45cal.
I can't believe I didn't think of this. Sometimes you look at a spreadsheet so long, you begin to miss the obvious....
Thanks for your solution!

Stuart