[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

Strange Equation

I have a spreadsheet of data, and the results returned are slightly different to when I manually add it up on a calculator and its driving me mad.

The formula is =+A2*D2/100

A2 is typically a large figure such as 55000 and D2 is always a decimal such as 1.5.

I cannot get the same figure when i enter the same equation on a calculator. Any ideas why?

the column the ouput of this equation is in is formatted accounting with 2 decimal places. A2 is formatted custom, and column d is formatted number

Please help
0
pma111
Asked:
pma111
  • 4
  • 3
2 Solutions
 
Brian BIndependant Technology ProfessionalCommented:
It could be rounding issues due to the settings on the cell. If you clear the formatting from the cells, do you get the correct result?

Worst case, try the windows calculator on the same machine. At least that will tell you if its something about the system.
0
 
Rory ArchibaldCommented:
What answer are you getting on the calculator that you aren't getting in Excel, and what specific custom format do you have applied to A2? I suspect rounding errors.
0
 
pma111Author Commented:
For one row I get 986.21 in the spreadsheet and on the caluclator 982.62

format below

_-* #,##0_-;-* #,##0_-;_-* "-"??_-;_-@_-

column Dis also dependant on figures in column B C (namely the forumula is C-B)
Coumn B is dependant on column Y in a forumla =B2*1.0956
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Rory ArchibaldCommented:
Sounds like simple rounding - your column A is showing no decimal places even if there are some.
0
 
pma111Author Commented:
Columns quoted above are wrong

Column J9 (the summary figure) =+D9*H9/100 (formatted as accounting)
Column D9 = 95,400 (formatted custom as above)
Column H9 = +G9-F9 (formatted number)
Column G9 = 6.95 (formatted general)
Column F9 =+E9*1.0956 (formatted number)
Column E9 = 5.4 (formatted general)
0
 
pma111Author Commented:
Sample of data D-J (tab delimeted)

 95,400       5.4      5.92      6.95      1.03      1.55       £986.21
0
 
Rory ArchibaldCommented:
I make that 986.207 both in Excel and on a calculator as you are effectively doing:
=95400*1.03376/100
0
 
Rory ArchibaldCommented:
PS Hence it is simply down to the fact that you are only displying 1 DP in the intermediate cells - the actual values are:
95400   5.4   5.91624   6.95   1.03376
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now