Average (Subtotal) , do not round up or down .

[Sunline]

Hello all ,

I have this basic average function but I have 4 x cells that are rounding up in yellow .

I don’t want them to round up or down at all .

Not sure how to do this .

Thanks .

Excel Workbook
	B	E	K
1	Dlst	WDlst%	Results
2	10	0
3	15	10	10.0
4	175	12.5	12.5
5	20	66.6	66.7
6	15	55	55.0
7	17	47	47.0
8	49	42	42.0
9	175	43	43.0
10	29	59.5	59.5
11	31	56.1	56.1
12	7	53.6	53.6
13	99	49.3	49.4
14	2	53.5	53.5
15	45	49.5	49.5
16	65	49.2	49.2
17	14	50.2	50.3
18	21	48	48.0
19	28	46.4	46.4
20	100	45.3	45.4
Sheet1

 

[barry houdini]

In K5 you are averaging 10, 15 and 175 so that's 200/3 = 66.66666.....

When you format the cell with 1 decimal place that will display as 66.7 as expected. How did you get 66.6 in E5?

If you really want to display as shown you will need to wrap SUBTOTAL in a rounding function, e.g. ROUNDOWN like this

=ROUNDDOWN(SUBTOTAL(1,$B$2:B2),1)

I don't know if that will work for all values