action:Average Formula

[GymWrecker]

I'm using one spreadsheet, linking to another four, as noted below. Somehow, the SUM in cell M7 is not correct, not sure what is wrong with the formula. I formated ALL the cells as Numbers, no decimal places. Please help.


=AVERAGE('Mr. Perez'!D7+'Mr. Padilla'!D7+'Mrs Gomez'!D7+'Mr. Cruz'!D7)/4 = 2
=AVERAGE('Mr. Perez'!E7+'Mr. Padilla'!E7+'Mrs Gomez'!E7+'Mr. Cruz'!E7)/4 =3
=AVERAGE('Mr. Perez'!F7+'Mr. Padilla'!F7+'Mrs Gomez'!F7+'Mr. Cruz'!F7)/4 =2
=AVERAGE('Mr. Perez'!G7+'Mr. Padilla'!G7+'Mrs Gomez'!G7+'Mr. Cruz'!G7)/4 =5
=AVERAGE('Mr. Perez'!H7+'Mr. Padilla'!H7+'Mrs Gomez'!H7+'Mr. Cruz'!H7)/4 =1
=AVERAGE('Mr. Perez'!I7+'Mr. Padilla'!I7+'Mrs Gomez'!I7+'Mr. Cruz'!I7)/4 =2
=AVERAGE('Mr. Perez'!J7+'Mr. Padilla'!J7+'Mrs Gomez'!J7+'Mr. Cruz'!J7)/4 =0
=AVERAGE('Mr. Perez'!K7+'Mr. Padilla'!K7+'Mrs Gomez'!K7+'Mr. Cruz'!K7)/4 =1
=AVERAGE('Mr. Perez'!L7+'Mr. Padilla'!L7+'Mrs Gomez'!L7+'Mr. Cruz'!L7)/4 =1

Cell M7 has the following formula: =SUM(D7:L7) and the result showing is 16, when it should be 17. Why am I’m getting the wrong sum?

[Paul]

I'm going to throw out a guess here based on

I formated ALL the cells as Numbers, no decimal places.

Even though you're showing no decimal places, that doesn't mean they don't exist. If you were to show 1 or more decimal places, it might show you why your sum is off.

Excel will calculate formula results with decimal precision even if you format the cell to show no decimals - unless you specifically tell Excel to calculate precision as shown in the worksheet (Tools - Options - Calculation tab - Precision as Displayed), which, in many instances is not a good thing.