SUM error

[DavidB]

Can anyone tell me why this: =SUM(AI13+AW13+BI13+BW13+CJ13/5)) returns 34440%
in the destination cell when AI is 89, AW is 90, BI is 78, BW is 71 and CJ is
82??? I don't get it.

Cheers
David

 

[Gary Smith]

Your formula has an extra closing parenthesis which should produce an
error message, but note that

=SUM(89+90+78+71+82/5) has the value 344.4, while

=SUM(89+90+78+71+82)/5 has the value 82.

I suspect the latter is what you want.

 

[MarkN]

Hi David,

Your problems are possibly these.

First, the resulting display of 34440% is a formatting issue. To go back to
displaying a regular number you need to click on the Format menu, select
Cells and make sure the Number tab is selected. Click on General at the top
of the list on the left and click OK.

Second the calculation. A mathematical order of operations governs the way
in which your formula is evaluated:
=AI13+AW13+BI13+BW13+CJ13/5 will mean that CJ13/5 is calculated first
followed by the addition of the other cell references, whereas:
=(AI13+AW13+BI13+BW13+CJ13)/5 will ensure that all addition is completed
prior to the total being divided by 5.

 

[Roger Govier]

Hi David

Is there any reason why you choose this method rather than the
=AVERAGE(AI13,AW13,BI13,BW13,CJ13)
as I posted yesterday?

As other have pointed out, your calculation is only dividing the figure
in CJ13 by 5, then adding that to the values in the other cells. Just
the /5 outside of the SUM() formula.

 

[DavidB]

Roger, there is absolutely no reason why I did that...but don't worry I
amended it and it now read just as you suggested. I think what happened is
that the destination cell L13 was displaying things like ####### and #Value
and 32000% so I tried to modify the formula rather than just ask your advice,
I have a thing about people thinking I'm a complete idiot so my thanks once
again...by the way what day and date is it over in Wales?

Cheers
David