# Financial excel query

BESTIE 11:40 19 Aug 2004
Locked

I am trying to insert a formula that works out a monetary value that displays a answer in pence. All I seem able to do is finish with £ 0.73.
Can anyone give me any idea how I can finish with 0.73p

Rigga 11:52 19 Aug 2004

When you format the cell, use a general format of 0.00p

R.

BESTIE 12:18 19 Aug 2004

I am using excel 2003 and it doesn't give me an option in formatting cells to use 0.00p format.
Can I use a custom formula and if so what would it be and how i would i add it.

cherria 12:21 19 Aug 2004

Under format cells, you can use a special format of 0.00p

but are you sure this is what you want?

0.73p is not the same as 73p

£0.73 is actually correct

Also, are all the values in pence otherwise the solution above might produce 1.73p for a value of £1.73

If you are not sure if you have values of more than a pound, could I suggest a second column that takes the actual value and transforms it.

Say your value of 0.73 was in cell B12 then in C12 put

=if(B12<1,B12*100&"p","£"&B12)

this would give you £1.73 for values over 1 pound and 73p for values below 1 pound.

cherria 12:25 19 Aug 2004

For a custom format

Go to format->cells

select the number tab

select the custom option from the list.

In the box where it says Type enter 0.00p

click ok

BESTIE 12:25 19 Aug 2004

My query is regarding shares and the particular item I am trying to resolve is 575,590 / 121204.
I want to dsiplay the answer as 4.7p

Rigga 12:26 19 Aug 2004

I only have Excel 2000.

Select the cell you wish the 0.73p to display.

Then under the menu - format - cells, what do you have?

If you have a number tab, select that.

In there what do you have?

If there's a General selection select that, then in the input box shown, Type 0.00p.

You should be able to Type in the format when you select General format. as in you could type. 0.00(Pence) and it would display .73 as 0.73(pence).

hth.
R

cherria 12:36 19 Aug 2004

Ah, then the custom format of 0.00p will work fine for you as a value greater than a pound will display as eg. 173.50p which is still correct

If you do not want a fixed number of decimal places, try #.##p as the format

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best Black Friday Deals 2017

How modern book design was influenced by illustrated manuscripts

Best Black Friday Apple Deals 2017

Les meilleurs logiciels de montage vidéo gratuits (en 2017)