Quick excel question.

[spionfox]

I have two columns of data - the first is a product code and the second a retail price including VAT. For example;
PRODUCT RETAIL
ABC123M 5.25
I now want a third column giving me a VAT exclusive price so use, for example;
=(b2/47)*40
which gives me;
PRODUCT RETAIL NETT
ABC123M 5.25 4.47

Great so far, but I now want to copy the entire column C contents (several hundred items) and paste the values only into a fresh column. Problem is that in the above example it copies the actual value of column B (which is 4.46808510638298) rather than just the 4.47 bit.

I know I can format the cells to show only two decimal places but I want the underlying value to be two decimal places as well rather than showing the whole lot.

Am I making sense?!

Thanks :icon_bigg

 

[bocadillo]

.

Am I making sense?!

Not really. Do you mean that it is copying the actual value of column C to the new column?


To fix a new column at 2 decimal points and use that value as the new base, use the FIXED function.

So:
A2 = ABC123M
B2 = 5.25
C2 = (B2/47)*40 - which displays as 4.47, although the underlying value is 4.46808510638298

D2 = FIXED(C2,2) - displays as 4.47 and has underlying value of 4.47 for onward calculations

 

[spionfox]

D2 = FIXED(C2,2) - displays as 4.47 and has underlying value of 4.47 for onward calculations

That does it, thanks, but........ :icon_wink

That means I have to have an extra column to apply the 'fixed' bit before copying that column and 'paste specialing' to apply the amended value only which is what I want.

Is there a more energy efficient way of doing this?

 

[bocadillo]

That does it, thanks, but........ :icon_wink

That means I have to have an extra column to apply the 'fixed' bit before copying that column and 'paste specialing' to apply the amended value only which is what I want.

Is there a more energy efficient way of doing this?

Why do you have to paste it? Why do you have to have an extra column?

Can you not just use the FIXED formula in the cell which you were going to paste to?

Or have I misunderstood?

 

[spionfox]

Can you not just use the FIXED formula in the cell which you were going to paste to?

Surely whatever is in the cell I am going to paste to will be overwritten by what I actually paste in!!!

Unless I am having one of those 'blonde' moments :icon_bigg

 

[bocadillo]

Surely whatever is in the cell I am going to paste to will be overwritten by what I actually paste in!!!

You've lost me.

 

[spionfox]

You've lost me.

You said:
Can you not just use the FIXED formula in the cell which you were going to paste to?

How can I use the FIXED formula in the cell I was going to paste to? The cell I was going to paste to will no longer contain the FIXED formula if I paste into it?

I've got a headache and it's not even 9 o'clock yet :icon_cry:

 

[bocadillo]

You said:
Can you not just use the FIXED formula in the cell which you were going to paste to?

How can I use the FIXED formula in the cell I was going to paste to? The cell I was going to paste to will no longer contain the FIXED formula if I paste into it?

I've got a headache and it's not even 9 o'clock yet :icon_cry:

But wasn't what you were going to paste into it the same as the return from the FIXED formula? Why paste it there when Excel will put it there for you?

 

[spionfox]

But wasn't what you were going to paste into it the same as the return from the FIXED formula? Why paste it there when Excel will put it there for you?

Because I currently have a nett price based on 15% VAT in that cell which needs to stay there until mid-night on 31st December. I wanted to prepare the nett prices based on 17½ VAT in advance without affecting the current cell.

Anyway, thanks for the FIXED reminder - I had forgotten all about that!