This calculation looks normal

[otuatail]

This looks like a proper calculation but excel dosn't like it

=SUM((G5:G21)/17)

TIA

Desmond.

[kev_]

Try

Please Login or Register  to view this content.

[shukla.ankur281190]

Removed by SA

[daddylonglegs]

=SUM((G5:G21)/17)

It's a valid formula but because you are dividing by 17 before summing you are feeding an "array" to the SUM function and therefore the formula is an array formula and requires CTRL+SHIFT+ENTER

Of course it's better to do it the way kev suggests because that gets the same result without array entry (and kev's version could cope with text in the range whereas yours can't)

[kev_]

@daddylonglegs That's a clear and very useful explanation

[otuatail]

Ok thanks. Could this be expanded and maybe a macro of sun kind

What I would like to do is sum a range of values and divide by the value in another cell. This value would require subtracting one from the cell value

i.e.
=SUM((G5:G21)/17)

cell U30 has the value of 18 so it would be U30-1

What I did was use a separate cell to add the values up and then do the calculation on that cell in to another cell. This looks very messy.

[daddylonglegs]

You can do that like this

=SUM(G5:G21)/(U30-1)

That's doing the sum of G5:G21 first then dividing that total by U30-1