Data Validation

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 co-workers 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