Financial excel query

  BESTIE 11:40 19 Aug 04
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 04

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

R.

  BESTIE 12:18 19 Aug 04

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 04

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 04

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 04

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 04

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 04

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.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…