Cell Formatting Query: How to format based on the cells value

[The_Snook]

Hi,

I'm creating a report where all the source numbers are negative but i'd like to show them as positive. Also given that they vary in value so greatly i'd like to format any numbers less than -1,000,000 to the nearest thousand (£#,##0.0,"k") and any numbers greater than -1,000,000 to the nearest million (£#,##0.0,,"m").

I've asked a similar query in the past here: http://www.excelforum.com/excel-gene...mat-query.html
but i'm struggling to adapt that to my current needs?

Any suggestions?!?!

Thanks

Snook

[aydeegee]

Hi snook

To change the figures to positive (assuming the values to be in column A):
either
A.
in a helper column eg row 1

Please Login or Register  to view this content.

or B.
just to show the minus figure without the "-" use cell format

Please Login or Register  to view this content.

To then show as you have requested, if you have used my suggestion A., all you need to change in the format from http://www.excelforum.com/excel-gene...mat-query.html

Please Login or Register  to view this content.

is

Please Login or Register  to view this content.

otherwise if you have used my suggestion B:
[CODE][>-1000000] #,##0.0,"k";[<=-1000000] #,##0,,"m"[CODE]

Regards
Alastair