Custom number format not working for negative numbers

[rw2]

Hello,

I'm having trouble getting to grips with Excels custom number formats, and haven't been able to find an answer...

I'm using the following custom format to shorten values with M (million) and K (thousand):

[>999999]£#.#,,"M";[>999]£#,"K";£#

This works well, unless my values are negative, in which case it doesn't work. I've tried to edit the formula to work for negative numbers too, but keep failing. Can anyone help please?

Thanks

[Richard Buttrey]

Hi,

This article http://excelribbon.tips.net/T010227_...om_Format.html suggests it isn't possible.

I'm trying to rack my brains since I think I've seen something in the past which allowed you to include something like [<999999] etc..

Maybe someone else will chip in. If I find anything I'll let you know.

[MrShorty]

Richard and his link are correct, you can only use 3 or 4 conditions in number format codes. What you are describing would require 5. As suggested by his link, the solution is to use regular conditional formatting, which is not as limited as number format codes.

[rw2]

ok, good to know I shouldn't keep trying to modify the custom number format.
Thanks