Average of numbers not correct

In the attached spreadsheet I have 2 averages.

In column B I have the main numbers. B29 produces the average of that number

I used a simple subtraction in column C to get the difference between each row and B29 (variance).

I wanted to find the average of column C, but for some reason AVERAGE is not working (C29).

If I manually type out the numbers in column C, AVERAGE works correctly. But for some reason, I think because the value of cells in column C are the product of a formula, its not working for the AVERAGE in C29. Any thoughts?

If I manually type out each number, the AVERAGE function returns -.12 which is correct. But I'm confused why C29 does not work in the attachment.

B29 is =AVERAGE(B11:B27)

C11 is =B11-B29
C12 is =B12-B29
and so on

C29 is =AVERAGE(C11:C27)

Originally Posted by jeffreybrown

I get the same exact average for column C even when I paste the numbers in column D as values. Maybe you can tell use what you think it should be.

C29 in the attachment is coming back as 0. It shouldn't be that. I want to keep column C as the difference between B29 and values in each row of B.

Could it be the zeros throwing it off?

Yes the true value of B29 is 17.88etc, but I want everything to be treated as a whole number.

Even if I set every value to general, C29 returns -4.1796etc which is also incorrect.

If I make C29 show 20 decimals, it produces a number at digit 16. So I guess the true value of each cell, decimals and all, are close enough to 0.

However, is there a way to have the formulas produce only rounded digits and not just 'visually' ? Meaning the formulas will use the rounded result, rather than the true number. IE B29 will be treated as 18 and not 17.88 in formulas

Great thanks, the 17.88 versus the 18 was the issue. I just wasn't immediately aware of it since I had the formatting to 0 decimals.