LinkBack URL
About LinkBacks
How would I add the squares of cells A1:ZZ1 or A1:A1000, without having to do
=SUM(A1*A1+B1*B1+C1*C1... etc
This is a basic example of what I actually want to do, but is essentially the same problem.
How would I add the squares of cells A1:ZZ1 or A1:A1000, without having to do
=SUM(A1*A1+B1*B1+C1*C1... etc
This is a basic example of what I actually want to do, but is essentially the same problem.
You need to use an array formula.
confirmed with Ctrl+Alt+Enter instead of just Enter
Please Login or Register to view this content.
Perfect mdbct. Well done!
Just one correction. It's not ctrl+alt+enter. It's ctrl+SHIFT+enter.
None the less, that array formula is the shortest anyone can get to.
Zantier, you can also use the following formula:
=SUMPRODUCT(A1:A100,A1:A100)
It gives you exactly the same result. Enjoy!
Chaitanya
Excel Expert and Excel Consultant
Or =sumsq(a1:a100) entered as a normal formula.
Entia non sunt multiplicanda sine necessitate
Even simpler! Cool!
Is there a general way for me to sum functions applied to a group of cells?
Say you have a function FN(), and want the sum:
=SUM(FN(A1)+FN(B1)+FN(C1)....
Sorry if I'm being a pain.
EDIT: Thanks for all the quick answers for the summing squares =)
Assuming the function is array-compatible, =SUM(FN(A1:C1)) (array entered) or =SUMPRODUCT(FN(A1:C1))
Thanks for the reply.
How about if it isn't array compatible?
I was more thinking for just putting my own calculating in place of the function. e.g:
indirect("B"+A1) in place of FN(A1)
I don't know whether/how to make a custom function, and whether it's necessary/helpful to, as I'm very much a newbie.
Then it won't work. Most of the simple built-in functions are, though.How about if it isn't array compatible?
That's fine. I'd use INDIRECT sparingly, though -- it's volatile, meaning it recomputes every time Excel calculates, whether it needs to or not.I was more thinking for just putting my own calculating in place of the function. e.g: indirect("B"+A1) in place of FN(A1)
Oh, sorry. I ment indirect("B"&A1) in place of FN(A1).
I tried sum(indirect("B"&B20:B25)), but it just returns #VALUE!
You should bring up the Formula Auditing toolbar, select the cell, and then press the Evaluate button repeatedly to watch the formula evaluate step by step. You'll see why it doesn't work.
Thanks.
The first evaluation changes it from:
SUM(INDIRECT("B"&B20:B25)) to
SUM(INDIRECT("B"&#VALUE!))
EDIT:I need to go to bed now, but I'll check for any responses in the morning, thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks