Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

Browse Open Actuarial Jobs

Life  Health  Casualty  Pension  Entry Level  All Jobs  Salaries


Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 10-17-2018, 05:32 PM
Noonien Soong Noonien Soong is offline
CAS
 
Join Date: Sep 2018
Posts: 17
Default Excel: sum of squares(or any operation) on a set of cells.

Say in A1:A4 I have 1, 2, 3, and 4 respectively.

Is it possible to take the sum of each of those #s squared in one cell? Sure, I could set B1=A1^2, copy that down, then take sum(B1:B4), but is there any way to do this directly in cell A5? Maybe with an array?

Peace,

Noonien Soong.
Reply With Quote
  #2  
Old 10-17-2018, 05:34 PM
nonlnear nonlnear is offline
Member
Non-Actuary
 
Join Date: May 2010
Favorite beer: Civil Society Fresh IPA
Posts: 29,988
Default

sumproduct(range,range)
Reply With Quote
  #3  
Old 10-17-2018, 07:16 PM
Dr T Non-Fan Dr T Non-Fan is online now
Member
SOA AAA
 
Join Date: Sep 2001
Location: Just outside of Nowhere
Posts: 93,164
Default

You'd think a guy with this pseudonym would know something about manipulating data.



Sorry, that was just low-hanging fruit, there.
__________________
"Facebook is a toilet." -- LWTwJO

Last edited by Dr T Non-Fan; 10-17-2018 at 07:23 PM..
Reply With Quote
  #4  
Old 10-17-2018, 07:28 PM
Maphisto's Sidekick's Avatar
Maphisto's Sidekick Maphisto's Sidekick is offline
Member
CAS
 
Join Date: Nov 2001
Location: South Park Genetics Lab
College: Ardnox
Favorite beer: The kind with alcohol
Posts: 2,724
Default

Quote:
Originally Posted by Dr T Non-Fan View Post
You'd think a guy with this pseudonym would know something about manipulating data.
Reply With Quote
  #5  
Old 10-17-2018, 07:31 PM
NerdAlert's Avatar
NerdAlert NerdAlert is offline
Member
SOA
 
Join Date: Sep 2009
Studying for a hobby.
Favorite beer: Guinness
Posts: 4,253
Default

Can’t you just sumproduct the data with itself?

ETA: Shoulda read the responses first. Ninja’d.
__________________
I'm waving through a window. Can anybody see? Is anybody waving back at me?
Reply With Quote
  #6  
Old 10-17-2018, 08:18 PM
Dr T Non-Fan Dr T Non-Fan is online now
Member
SOA AAA
 
Join Date: Sep 2001
Location: Just outside of Nowhere
Posts: 93,164
Default

Noonienja'd.

Again, the low-hanging fruit.
__________________
"Facebook is a toilet." -- LWTwJO
Reply With Quote
  #7  
Old 10-17-2018, 10:01 PM
Colymbosathon ecplecticos's Avatar
Colymbosathon ecplecticos Colymbosathon ecplecticos is offline
Member
 
Join Date: Dec 2003
Posts: 5,970
Default

To be fair, the OP asked about other functions. A feature of sumproduct (which, I admit, I have never used) is that you can include more than two columns, so you can compute the sum of nth powers with it. Using multiplicative coefficients and summing, any sum of polynomial functions (or even rational functions) of the cells is computable this way.
__________________
"What do you mean I don't have the prerequisites for this class? I've failed it twice before!"


"I think that probably clarifies things pretty good by itself."
Reply With Quote
  #8  
Old 10-17-2018, 11:13 PM
ElDucky's Avatar
ElDucky ElDucky is offline
Free Mason
 
Join Date: Jul 2004
Location: In a van, down by the river
Studying for Let me worry about blank
Favorite beer: Trappistes Rochefort 8
Posts: 42,053
Default

Quote:
Originally Posted by Dr T Non-Fan View Post
You'd think a guy with this pseudonym would know something about manipulating data.



Sorry, that was just low-hanging fruit, there.
Would he? You need the dark arts and lore to manipulate data. Dr. Soong is a scientist, not a witch.
__________________
I live near the cows.
Reply With Quote
  #9  
Old 10-18-2018, 05:39 AM
Hedges Hedges is offline
Member
SOA
 
Join Date: Apr 2002
Location: I've been Shanghaied!
Posts: 472
Default

Aren't array formulae what the OP is looking for? SUMPRODUCT is a built in function which will handle a specific type of manipulation but array formulae will handle a much wider range of calculations.

e.g. {=SUM(A1:A4*A1:A4)} is the array formula equivalent of SUMPRODUCT(A1:A4, A1:A4)

However, with array formulae, you can do much more. For example, you can do the following: for each cell in column A, take the greater of 20 or the cell value and then multiply it by the corresponding cell in column B. Finally take the greatest value of each of these.
{=MAX(IF(A1:A4<20,20,A1:A4)*(B1:B4))}

(Note that 20 could also be a reference to another single cell which contains the value 20. Or for something slightly different, it be another range of cells, e.g. C1:C4)

Note that you don't actually type in the curly braces. You use Ctrl+Shift+Enter to enter the formula and the braces appear.* So array formulae are sometimes known as CSE formulae.

Also, note that an array formula return an array result. However, apply a function that returns a single value such as SUM or MAX will give a a single number.
Thus {=(A1:A4*A1:A4)} is actually returning a range of 4 rows x 1 column. The SUM in {=SUM(A1:A4*A1:A4)} is then adding these up to produce a scalar value.
To see the full results of {=(A1:A4*A1:A4)}, you'd need to CSE the formula in range of 4x1. But entering this formula in a single cell won't produce an error - it will just give you the first row's result.

If you accidentally enter {=(A1:A4*A1:A4)} in a range of 5x1, the last row will produce a #N/A error. However, you cannot delete part of a range of a CSE formula. You have to delete (or change) the entire range. But there is a way to get rid of that annoying error. In order to delete just the offending fifth row, you need to "undo" the CSE formula by highlighting the whole range and then using Ctrl+Enter (NO Shift) to re-enter the formula - you'll see that the curly braces have disappeared.** Then delete the fifth row (allowable because its now longer part of a CSE formula) and then finally re-enter the first rows as a CSE formula again. This sounds messy but you get used to it quickly and you only have to worry about it if you've selected the "wrong" output range.

CSE formulae also work with matrices, not just vectors.

Overall, I don't use CSE formulae very often but they can be extremely useful.

I hope this was what you were looking for.

* The technical explanation is that behind the scenes, Excel releases a small, invisible troll which quickly puts in the curly braces.
** The troll collects them again, obviously.

Last edited by Hedges; 10-18-2018 at 05:51 AM..
Reply With Quote
  #10  
Old 10-18-2018, 12:21 PM
Dr T Non-Fan Dr T Non-Fan is online now
Member
SOA AAA
 
Join Date: Sep 2001
Location: Just outside of Nowhere
Posts: 93,164
Default

Array functions are not ever my first solution. Last resort. I'd rather waste space in a separate column with a Min(20,A1) function, because it's easier to document and for a first-time viewer to follow.
__________________
"Facebook is a toilet." -- LWTwJO
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 09:24 PM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.19290 seconds with 10 queries