More Excel help needed please :-)

[spionfox]

In cell E10 I have a value of £19.99. In cell R10 I have the formula =(E10/6)*5 which results in a value of £16.66 (the cell is formatted as a number to two decimal places).

I now want to put this value (£16.66) into another cell so I copy the contents and use 'paste special - values only' to insert it. This however results in the correct figure being shown in the new cell but the underlying value is still the mathematically correct figure. (see attached picture.)



Is there a way to 'paste special' the value to two decimal places only as I want?

Thanks.

 

[bocadillo]

TRUNCate

 

[spionfox]

TRUNCate

Do WHAT to Cate?

:icon_wink

Are you going to explain further or do I have to Google? :icon_bigg

 

[bocadillo]

Do WHAT to Cate?

:icon_wink

Are you going to explain further or do I have to Google? :icon_bigg

:icon_redf


I haven't got Excel on this machine but it's something like:


s10 =TRUNC(r10,2), where two is the number of decimal places that you want to retain.

 

[spionfox]

:icon_redf


I haven't got Excel on this machine but it's something like:


s10 =TRUNC(r10,2), where two is the number of decimal places that you want to retain.

Thanks Bob, but although the TRUNC feature would work it isn't suitable. This is because I don't want a reference to another cell as once the data has been successfully copied over the original (source) values will be deleted.

Bloody VAT change eh?!

 

[bocadillo]

Won't it work if you carry out your Paste Special after the TRUNC? You should then be able to delete everything that went before.

 

[bocadillo]

As in OpenOffice:


Original value in B4 - formula in D4 - Paste Special to F4





Then delete the original value and the formula (B4 and D4), leaving the figure you want in F4




Should work exactly the same in M$Excel, if you replace the semi-colon in the formula with a comma.

 

[spionfox]

As in OpenOffice:


Original value in B4 - formula in D4 - Paste Special to F4

View attachment 6615



Then delete the original value and the formula (B$ and D4), leaving the figure you want in F4

View attachment 6616


Should work exactly the same in M$Excel, if you replace the semi-colon in the formula with a comma.

Works perfectly - thanks Bob

Just for my future reference, I presume the 2 after the comma is for the number of decimal places to be shown. What on earth is the 0.005 bit though?

Thanks.

 

[bocadillo]

Works perfectly - thanks Bob

That's a great comfort and relief.

Just for my future reference, I presume the 2 after the comma is for the number of decimal places to be shown.

It is; I mentioned it obliquely in post #4

What on earth is the 0.005 bit though?

It's to facilitate proper rounding. Add half a penny (£0.005) to the underlying value and lop off everything after the first two decimal places. Otherwise the formula would return the nearest penny below.

Thanks.

No problem.

 

[Darth Vodka]

=round((E10/6)*5,2)

 

[spionfox]

=round((E10/6)*5,2)

I knew if I was patient an Excel user would be along to help rather than one of those open-source aficionados!!!

Cheers DV

And Bob - your method helped too

 

[bocadillo]

I knew if I was patient an Excel user would be along to help rather than one of those open-source aficionados!!!

More of a poor pensioner who no longer has dodgy access to expensive M$ software, if the truth be told.

 

[Darth Vodka]

Bloody VAT change eh?!

bloody people who hard code the number into spreadsheets

:icon_lol:

 

[Joe_Fox]

Agreed - amateurs.