Values into pounds and pence

[StuMcFish]

First off I would like to say hi as I'm new to the forum. I'm not great on excel and that's why I'm here.

My question is, if I have a list of numbers in a column can I get a formula that will change the values of less than 100 into pence and then values of 100 or more into pounds and pence. I have managed to sort out the less than 100 into pence with this

[<100]#,##0"p";General

when I custom format the column but I cant get the pounds to work. When I do it a number such as 620 will end up as £620.00 instead of £6.20. Can anyone help me with this problem please?

[shg]

Welcome to the board.

You can't do that with a custom number format. You can divide by multiples of 1000, so that, for example, millions appear as thousands, but there is no way to divide by 100.