ROUNDDOWN vs ROUNDUP

Today I want to explore a simple formula ROUNDDOWN and its brother ROUNDUP.

There are times when you don’t want to display the entire value. In my below example, column A shows an entire value. 

So what does ROUNDDOWN do?

The ROUNDDOWN function returns a number rounded down to a specified number of digits. Always rounding towards 0.

The ROUNDDOWN function returns a number rounded upward to a specified number of digits. Always rounding away from 0.

Here is an example of ROUNDDOWN and ROUNDUP. 

	A	B	C	D	E	F
1	Value	Result	Formula		Result	Formula
2	15.75654	15	=ROUNDDOWN(A2,0)		16	=ROUNDUP(A2,0)
3	15.75654	15.7	=ROUNDDOWN(A3,1)		15.8	=ROUNDUP(A3,1)
4	15.75654	15.75	=ROUNDDOWN(A4,2)		15.76	=ROUNDUP(A4,2)
5	15.75654	15.756	=ROUNDDOWN(A5,3)		15.757	=ROUNDUP(A5,3)
6	15.75654	15.7565	=ROUNDDOWN(A6,4)		15.7566	=ROUNDUP(A6,4)
7	15.75654	15.75654	=ROUNDDOWN(A7,5)		15.75654	=ROUNDUP(A7,5)

Now it’s important to understand what happens when you multiply a number based on the ROUNDDOWN or ROUNDUP function.

Let’s look at cell A2, The ROUNDDOWN result is 15 and the ROUNDUP is 16

If I was to multiply the original value by 2 my result would be 31.51308 (2 X 15.75654).

However if I multiply my ROUNDDOWN or ROUNDUP result, I get a very different answer.

15 X 2 = 30

16 X 2 = 32

So use caution when using ROUNDDOWN or ROUNDUP since calculations downstream may not produce the result you are expecting.

BEGINNER, ROUNDDOWN, ROUNDUP

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