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

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

# 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

0
pma111
• 4
• 3
2 Solutions

Independant 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

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

Author 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

Commented:
Sounds like simple rounding - your column A is showing no decimal places even if there are some.
0

Author 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

Author Commented:
Sample of data D-J (tab delimeted)

95,400       5.4      5.92      6.95      1.03      1.55       £986.21
0

Commented:
I make that 986.207 both in Excel and on a calculator as you are effectively doing:
=95400*1.03376/100
0

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

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