[Help] Irritating Excel conundrum

[Norman Potting]

Here's the problem. I have set up a simple Excel spreadsheet (use Office 365) for an analysis of some bills for work. Nothing complex, Excel just needs to do some adding up. Problem is that, although the individual sums contain odd pence, when adding up Excel rounds everything to the nearest pound so every total has ".00" pence. Have Googled and the remedies that come up are a) generally incomprehensible and b) don't work - formatting them as either "currency" or "accounting" with two decimal places makes no difference,

Help - please! Turning off, and turning back on again, also hurling abuse at the monitor, doesn't help either

 

[Wardy]

Is the column wide enough to show the full amount?


Sent from my iPad using Tapatalk

 

[FatSuperman]

What command are you using to add them up - just ‘sum’ ?

 

[Norman Potting]

Columns are wide enough and using "sum" or "auto sum" is coming up with the same answer.

 

[razer]

You just need to hover over the cell with the number in it and convert it to number format. That usually sorts it for me.

 

[Norman Potting]

You just need to hover over the cell with the number in it and convert it to number format. That usually sorts it for me.

No unfortunately

 

[clapham_gull]

Formatting has no effect on the value.

And Excel won't round unless told to.

Try creating a new column that references the original one with the =value(A1) formula to force a conversion to a number and add that one up instead.

Sent from my MAR-LX1A using Tapatalk

 

[FatSuperman]

Also, please try displaying to 3 decimal places and let us know whether it shows .000, or something else?

 

[Norman Potting]

Formatting has no effect on the value.

And Excel won't round unless told to.

Try creating a new column that references the original one with the =value(A1) formula to force a conversion to a number and add that one up instead.

Sent from my MAR-LX1A using Tapatalk

Still no unfortunately.

 

[Saunders]

[MENTION=5384]Norman Potting[/MENTION]

Check your settings and make sure you have '.' as your decimal point and not ','

 

[Norman Potting]

Mr. F Superman - It shows the column to 3 dp's - First column contains a list of bill totals ex VAT, second column shows the Vat figure at 20% calculated from the first column and the third column is the sum of the first two.
Saunders - Do you mean Windows settings (which part) or something else.

Thanks your patience and help with this.

 

[pastafarian]

Here's the problem. I have set up a simple Excel spreadsheet (use Office 365) for an analysis of some bills for work. Nothing complex, Excel just needs to do some adding up. Problem is that, although the individual sums contain odd pence, when adding up Excel rounds everything to the nearest pound so every total has ".00" pence. Have Googled and the remedies that come up are a) generally incomprehensible and b) don't work - formatting them as either "currency" or "accounting" with two decimal places makes no difference,

Help - please! Turning off, and turning back on again, also hurling abuse at the monitor, doesn't help either

If you convert the currency or accounting format to "general" doesnt that give the correct sum.......or are you using £ to display prices

 

[Saunders]

Click on File, then Options then Advanced. At the bottom of the first screen it will show you the seperators for your Decimal and Thousands seperation.

Edit to add this is in Excel.

 

[father_and_son]

Just to check... Have you tried summing just a couple of the numbers using your formula so that you can check the maths by hand?

Not sure how many entries you have but there is a chance it actually does add up to a whole pound amount. It's a 1% chance but it might happen.

And re hurling abuse at the monitor. Monitors respond much better to carrots than sticks. Offer some encouraging words.

 

[Let's Have A Winner!]

For your VAT calculation you may need to use ROUND (i.e. ROUND(A1*120%,2)

 

[Wardy's twin]

not sure if i am missing something but i just put in the following 3 numbers and summed it and strangely got the answer i expected.... this is excel from the office 2016 dvd...

12.01
15.01
17.33
44.35

 

[Wardy's twin]

are you entering the numbers to the cells or are you loading them from some other program's output?

 

[pastafarian]

For your VAT calculation you may need to use ROUND (i.e. ROUND(A1*120%,2)

How does the ROUND in a formula differ from using the decimal place up or down function?
Asking as an excel user at a rudimentary level

 

[Saunders]

If you are still having problems upload to a google drive account and one of us can take a look.

As long as that wouldnt break GDPR for you.

 

[clapham_gull]

Another trick, notepad is you friend.

Cut and paste the base values into notepad, open a new session of Excel and cut them in and start again.

Don't cut and paste from one workbook to another.