Today I want to relay an experience I encountered at work.
A
co worker of mine was having a problem in Excel. She was perplexed as to what
she was viewing.
She had a simple
worksheet where she was viewing the total cost for items. The total cost was
calculated by multiplying the value in column B to the value in column C. Now
this is one of the simplest calculations that Excel can do. However the result
she was seeing did not match.
For instance 900 Drill Bits at $8.21 cents each totals
$7,389.00, not $7,392.21. There was a difference of $3.21. The question... Why
was Excel displaying this cost?

A

B

C

D

1

Item

Quantity

Cost

Total Cost

2

Hammer

300

4.52

1,356.43

3

Saw

500

3.25

1,627.13

4

Level

700

5.56

3,892.90

5

Drill Bit

900

8.21

7,392.21

6





7



Total

14,268.66

The first thing I did was check her formula. It was
correct. =B5*C5. Next I checked to see
if she had autocalculate turned to manual since many coworkers turn this feature
off on large worksheets (and yes it is frustrating when they do that). Turning autocalculate off can display
erroneous information. But in this instance, it was on.
Well the next course of action was to check the actual
values in each cell. This is where I found the solution. Someone had formatted
column C to be 2 decimal places. When I expanded the decimal places I found
more digits.

A

B

C

D

1

Item

Quantity

Cost

Total Cost

2

Hammer

300

4.5214253

1,356.43

3

Saw

500

3.2542500

1,627.13

4

Level

700

5.5612840

3,892.90

5

Drill Bit

900

8.2135700

7,392.21

6





7



Total

14,268.66

So at the quantities she was looking at, these tiny portions
of the cost impacted the total cost.
So it was a case of what you see isn’t always what it is. It
was a good reminder to verify calculations as well as the source data.
Beginner, Data Validation
No comments:
Post a Comment