At 15:24 10/11/2008 +0000, Graham Smith wrote:
I have the following formula
=SUM(C2*(C2-1)+(D2*(D2-1)+(E2*(E2-1)+(F2*(F2-1)+(G2*(G2-1))))))/(SUM(C2:G2)*((SUM(C2:G2))-1))
where the first part is a repeating chunk of code, which may need to
be repeated for a couple of hundred cells. Is there a clever way of
setting this up so I only need to put the first and last cell into
the formula that would tell Calc to repeat the same calculation for
all the cells in between.
You could start by simplifying the formula.
o There are five terms - like C2*(C2-1) - summed in the numerator of
your fraction. You don't need to specify the order in which these
additions take place, since addition is commutative. So you can
remove four sets of parentheses.
o And you have belt and braces in that you have both the SUM()
function and the plus signs there: =SUM(x;y) makes sense, as does
=x+y, but not =SUM(x+y)! So you can drop the "SUM".
o You also have an unnecessary set of parentheses around the second
SUM in the denominator - from which you subtract one. Remove those.
This gives:
=(C2*(C2-1)+D2*(D2-1)+E2*(E2-1)+F2*(F2-1)+G2*(G2-1))/(SUM(C2:G2)*(SUM(C2:G2)-1))
- which is a bit easier on the eye.
When you say a couple of hundred cells, it is not clear whether you
mean rows or columns. If you need this formula to apply to columns C
to G but for many rows, you can simply copy it down a column, of
course. But perhaps you mean that you need to expand the formula to
deal with not five columns but many more. If so, you can do this
using an array formula. Enter this into your result cell:
=SUM(C2:G2*(C2:G2-1))/(SUM(C2:G2)*(SUM(C2:G2)-1))
but instead of pressing Enter to complete the entry, press
Ctrl+Shift+Enter. The complete formula will now appear in the Input
Line preceded and followed by braces:
{=SUM(C2:G2*(C2:G2-1))/(SUM(C2:G2)*(SUM(C2:G2)-1))}
but note that you *cannot* achieve the same effect by typing the
braces yourself. This produces the same effect as the original
formula. But with this version, you can easily extend the formula by
changing the limits C and G without complicating the expression, of course.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
