Excel query

Robm1955

I am doing a spreadsheet in Excel 2003. I have converted a price in $ to £, and then multiplied this by a quantity. One price in particular is £67.28. When multiplied by 100, it's coming out at £6727.50. I've tried using Roundup, and it still stays the same. Can anyone tell me what I'm doing wrong. I have all the cells formatted as currency to 2 decimal points, except the quantity.

mr_fishbulb

Robm1955 wrote: »

I am doing a spreadsheet in Excel 2003. I have converted a price in $ to £, and then multiplied this by a quantity. One price in particular is £67.28. When multiplied by 100, it's coming out at £6727.50. I've tried using Roundup, and it still stays the same. Can anyone tell me what I'm doing wrong. I have all the cells formatted as currency to 2 decimal points, except the quantity.

As you have set the cell to be a currency, it displays to 2 decimal places and either rounds up or down.

The £67.28 you are getting when you convert from $ could actually be 67.275 but the .275 gets rounded up to .28 because you are using 2 decimal places.

67.275 x 100 = 6727.5

Le_Kirk

Try carrying out all your calculations first, then convert the resulting and final figure from $ to £.

Robm1955

That's what I did think, but one item in particular is $69.00 * 0.65 (conversion rate) = £44.85 + 50% gives £67.28 exactly. Multiplied by 100 = £6728.00. Do you think removing the currency format might be the answer. My worry is, that if this is out by even 50p, it could mount up in the whole spreadsheet.

glentoran99

£44.85 + 50% gives £67.28

that's £67.275

bsod

roundup (67.275,2)

the source cells before multiplying the number of units.

if you format the cells to 5 decimal places, the source of the error should become apparent.

Robm1955

Thanks for all your replies. I tried doing the calculations first, then convert from $ to £, and still get the same answer. Changing the decimal settings on my calculator though, I do get 67.275 as you say glentoran99. Spreadsheet might be right then?

new_leaf_3

Hi Robm1955,

The problem is because you are multiplying monetary values by decimals, with the resulting figure having 3 decimal places which our currency obviously does not have.

£67.28 is the correct price. And if the formula has been entered correctly when multiplied by 100 it should still return £6,728.00. With MS Excel you have to be careful about the difference between what the cell is showing (its formatting) and its real value.

In this case I would have used the following formula (assuming that the $69.00 was in cell A1):

=ROUND((A1*0.65)*1.5,2)

I would also make sure that you use the ROUND function instead of ROUNDUP, because the latter would turn £12.391 to £12.40 instead of £12.39 (if rounding to 2 decimal places) - if that makes sense?

Anyway hope that helps?

new_leaf

System

Hi

I used LibreOffice like this

Number 69 in cell A1


Cell B1 has =ROUND(A1*0.65,2) formatted as Currency

Cell C1 is =ROUND(B1*0.5,2) to calculate 50% increase

Cell D1 adds them together =B1+C1 £67.28

Cell E1 is cell D1 * 100 giving £6,728.00

Obviously you can shorten the sequence. But since you have the ROUND to Decimal places this should keep everything tidy.

ROUND

Rounds a number to a certain number of decimal places.
Syntax

ROUND(Number; Count)
Returns Number rounded to Count decimal places. If Count is omitted or zero, the function rounds to the nearest integer. If Count is negative, the function rounds to the nearest 10, 100, 1000, etc.