Got something to say or just want fewer pesky ads? Join us... 😊

[Help] Irritating Excel conundrum



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 :down:
 


Wardy

NSC's Benefits Guru
Oct 9, 2003
11,219
In front of the PC
Is the column wide enough to show the full amount?


Sent from my iPad using Tapatalk
 












clapham_gull

Legacy Fan
Aug 20, 2003
25,179
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

Well-known member
Feb 25, 2016
2,829
Also, please try displaying to 3 decimal places and let us know whether it shows .000, or something else?

CC19303C-523D-45A3-9FF3-37CFB97A28D7.jpeg
 




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

Well-known member
Oct 1, 2017
2,292
Brighton
[MENTION=5384]Norman Potting[/MENTION]

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


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

Well-known member
Sep 4, 2011
11,902
Sussex
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 :down:

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


Saunders

Well-known member
Oct 1, 2017
2,292
Brighton
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.
 
Last edited:


father_and_son

Well-known member
Jan 23, 2012
4,646
Under the Police Box
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.
 






Wardy's twin

Well-known member
Oct 21, 2014
8,402
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
 








Saunders

Well-known member
Oct 1, 2017
2,292
Brighton
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

Legacy Fan
Aug 20, 2003
25,179
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.
 



Paying the bills

Latest Discussions

Paying the bills

Paying the bills

Paying the bills

Albion and Premier League latest from Sky Sports


Top
Link Here