Solved

# Strange Equation

Posted on 2011-05-09
282 Views
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
Question by:pma111

LVL 22

Assisted Solution

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

LVL 85

Expert Comment

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

LVL 3

Author Comment

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

LVL 85

Expert Comment

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

LVL 3

Author Comment

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

LVL 3

Author Comment

Sample of data D-J (tab delimeted)

95,400       5.4      5.92      6.95      1.03      1.55       £986.21
0

LVL 85

Expert Comment

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

LVL 85

Accepted Solution

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.