Formula to display some £ as 000s and others as 0millions?

[halesowenmum]

Hi

Setting up a document where I need to put in the total budget that each client had.

Many will be, say, in the ilk of £120,000 whilst others might be £38.4m - is there a way to ensure that when the numbers are entered:

£120,500 would be displayed as £120.5k
£38,400,000 would display as £38.4m?

Understand there's probably a bit of a combo of rounding and number formatting to employ.

Many thanks indeed.

 

[AlanY]

try

="£"&IF(A1<1000,A1,IF(A1<1000000,A1/1000&"k",A1/1000000&"m"))

 

[halesowenmum]

Apologies for the delay in responding. Thank you, this has worked absolutely perfectly. Thanks very much for this.