Description: Using AVERAGE across a row of 12 cells returns a different amount than using SUM and dividing by 12. ie) =AVERAGE(B164,M164) = 645,200.0 =SUM(B164:M164) /12 = 645,116.7 The document was originally an Excel file (from a state data store). Opened in LibreCalc Version: 5.2.7.2 Build ID: 1:5.2.7-1+deb9u5 _Debian GNU Linux 9.7 Steps to Reproduce: 1.Used: =AVERAGE(B40,M40) 2. Noticed unequal answer to another cell with Sum (B40:M40) /12 Actual Results: 1. 52,200 (USING AVERAGE FUNCTION) 2. 52,467 (SUM same cells and dividing by 12--n of cells) Expected Results: = 52,466.67 data for your reference in one case- many other rows different numbers affected the same in that average did not correctly work but sum and divide did. 53800+53700+53600+53500+53300+52900+52500+52000+51600+51200+50900+50600=629,600 629,600/12 = 52,466.67 AVERAGE SHOULD EQUAL SUM of cells divided by the nth number of cells added. Reproducible: Sometimes User Profile Reset: Yes Additional Info: The document was a State supplied excel sheet. There were no functions in the sheet, just numbers. Began using a formula to verify discrepancy all the way thru the 40 year sheet, just outside of the table area that went to 258 N so was putting formula in rows 1-258, column O, P and Q referencing the numbers in the table. In the same sheet, copying the data to well below 258, the AVERAGE function worked as did the sum /12 and after adding a new sheet, was able to paste the numbers there and add the same average function but with correct answers. It seems there is a compatibility issue with the data in the excel file.
Created attachment 149360 [details] it is a public downloadable State workforce statistic spreadsheet. This is the original downloaded an excel file that was opened in LibreCalc.
Created attachment 149379 [details] printscreen Your formula is wrong, should be =AVERAGE(B164:M164)
Comment on attachment 149379 [details] printscreen You did not understand but, no I did not enter the WRONG formula!!!Mine was identical to yours, BUT located in the next column over, and copied down the entire column. When some were overtly different than the tally shown in column N, in column P I used sum of them / 12, and that worked. My formula was =AVERAGE (B4:M4) or whatever the row number was and in the P column =SUM(B4:M4)/12. The numbers that calculated were different and the one that was correct was the sum.../12 one. I discarded the file. And, can't reproduce it in a newly opened copy. One thing I worked on for a bit before this occurred was putting my calculations in a new worksheet and when that didn't go so well, I tried to copy some of the data into the new worksheet and I believe that was there abandoned when I finally tried working directly on the original worksheet. Please don't discount this for the reason YOU think I put in the wrong calculation because I am certain I did not. You can discount it for any other reason- I don't care. But it did happen and if someone isn't paying attention, the AVERAGE calculation could malfunction again for something more important than what I was using it for and might not go unnoticed.
Understand. We need reproducible case. I tried reproduce it with your file, but AVG and SUM formulas are equal. Attach please broken file. Thanks