Actuarial Outpost Excel: sum of squares(or any operation) on a set of cells.
 Register Blogs Wiki FAQ Calendar Search Today's Posts Mark Forums Read
 FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

 DW Simpson Global Actuarial & Analytics Recruitment Download our 2017 Actuarial Salary Survey now with state-by-state salary information!

#21
10-23-2018, 11:37 AM
 DeepPurple Member Join Date: Jun 2004 Posts: 4,210

Quote:
 Originally Posted by Hedges 1) {=MAX(A1:A4*(B1:B4=C1:C4))} finds the maximum value in column A where for each row the value in column B equals that in column C

=SUMPRODUCT(MAX(A1:A4*(B1:B4=C1:C4)))

That wasn't hard. I wasn't copying yours. I had already had that preprogrammed using a scaler in a single cell instead of a vector in column C. One easy change.

Counter challenge.

Given a column of probabilities in A1:A20 (summing to 1) and values in B1:B20, calculate the Coefficient of Variation of the distribution in a single cell.
'=(SUMPRODUCT(A1:A20,B1:B20,B1:B20)-SUMPRODUCT(A1:A20,B1:B20)^2)^0.5/SUMPRODUCT(A1:A20,B1:B20)
__________________
Come on. Let's go space truckin'. Come on!
#22
10-23-2018, 11:50 AM
 CuriousGeorge Member CAS SOA Join Date: Dec 2005 Posts: 1,259

Fight! Fight! Nerd Fight!
#23
10-23-2018, 12:32 PM
 DeepPurple Member Join Date: Jun 2004 Posts: 4,210

Quote:
 Originally Posted by CuriousGeorge Fight! Fight! Nerd Fight!

I don't start nerd fights, I just finish them.

Ok maybe I started this one.

But I am still waiting for my props on post #18 herein for being the best solution and as a yet unrecognized Star Trek pun
__________________
Come on. Let's go space truckin'. Come on!
#24
10-23-2018, 01:43 PM
 tommie frazier Member Join Date: Aug 2003 Favorite beer: The kind with 2 e's Posts: 22,926

Quote:
 Originally Posted by DeepPurple =SUMPRODUCT(MAX(A1:A4*(B1:B4=C1:C4))) That wasn't hard. I wasn't copying yours. I had already had that preprogrammed using a scaler in a single cell instead of a vector in column C. One easy change. Counter challenge. Given a column of probabilities in A1:A20 (summing to 1) and values in B1:B20, calculate the Coefficient of Variation of the distribution in a single cell. '=(SUMPRODUCT(A1:A20,B1:B20,B1:B20)-SUMPRODUCT(A1:A20,B1:B20)^2)^0.5/SUMPRODUCT(A1:A20,B1:B20)
sure, but ...why?
__________________
Removed a dated athletic reference under pressure from a friend. You can still give money to help fund research on neurofibromatosis (nf).

General info at www.ctf.org

Team donation page here.
#25
10-23-2018, 02:13 PM
 DeepPurple Member Join Date: Jun 2004 Posts: 4,210

Quote:
 Originally Posted by tommie frazier sure, but ...why?
2B concise
__________________
Come on. Let's go space truckin'. Come on!
#26
10-23-2018, 02:21 PM
 White Castle Member Join Date: Feb 2007 Location: More than 380 restaurants Posts: 3,282

Quote:
 Originally Posted by Noonien Soong 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.
=SUMSQ(A1:A4)
__________________
#27
10-23-2018, 05:15 PM
 JMO Carol Marler Non-Actuary Join Date: Sep 2001 Location: Back home again in Indiana Studying for Nothing actuarial. Posts: 37,607

Quote:
 Originally Posted by tommie frazier sure, but ...why?
To compete with APL programmers.
__________________
Carol Marler, "Just My Opinion"

Pluto is no longer a planet and I am no longer an actuary. Please take my opinions as non-actuarial.

My latest favorite quotes, updated Apr 5, 2018.

Spoiler:
I should keep these four permanently.
Quote:
 Originally Posted by rekrap JMO is right
Quote:
 Originally Posted by campbell I agree with JMO.
Quote:
 Originally Posted by Westley And def agree w/ JMO.
Quote:
 Originally Posted by MG This. And everything else JMO wrote.
And this all purpose permanent quote:
Quote:
 Originally Posted by Dr T Non-Fan Yup, it is always someone else's fault.
MORE:
All purpose response for careers forum:
Quote:
 Originally Posted by DoctorNo Depends upon the employer and the situation.
Quote:
 Originally Posted by Sredni Vashtar I feel like ERM is 90% buzzwords, and that the underlying agenda is to make sure at least one of your Corporate Officers is not dumb.
#28
10-24-2018, 02:09 AM
 Hedges Member SOA Join Date: Apr 2002 Location: I've been Shanghaied! Posts: 472

I'll sidestep your challenge, DP, but agree that your SUMSQ is the best solution to the original problem.

The reason I'll sidestep is that it appears that SUMPRODUCT can be used to replace the array formula in any situation. But to me this raises a question since it seems really odd behaviour by SUMPRODUCT.

e.g. in the formula SUMPRODUCT(MAX(A1:A4*(B1:B4=C1:C4))), the inner part, MAX(A1:A4*(B1:B4=C1:C4)), appears to return a vector of 4 value, each of which equals the MAX value. At least that's what I see when I evaluate using F9. It also makes some sort of sense that it doesn't return a single value, because if it did, then one should be able to enter MAX(A1:A4*(B1:B4=C1:C4)) directly (with out CSE) and get the answer. Which one can't.

So in the first formula, SUMPRODUCT seems to be receiving a vector of 4 identical values but is not summing them. This appears to be different behaviour to what you would see if you directly entered, say, SUMPRODUCT(A1:A4). In this case, A1:A4 is summed up (I guess you could argue that it's first being multiplied by a unit vector)

I'm curious as to whether I've misunderstood something or whether SUMPRODUCT really does behave differently depending on the context - the context appearing to be whether it receives a direct reference to a vector or the whether it receives a vector as a result of the evaluation of some internal formula.

Any thoughts?

Last edited by Hedges; 10-24-2018 at 02:14 AM..
#29
10-24-2018, 04:56 PM
 DeepPurple Member Join Date: Jun 2004 Posts: 4,210

Quote:
 Originally Posted by Hedges I'll sidestep your challenge, DP, but agree that your SUMSQ is the best solution to the original problem. The reason I'll sidestep is that it appears that SUMPRODUCT can be used to replace the array formula in any situation. But to me this raises a question since it seems really odd behaviour by SUMPRODUCT. e.g. in the formula SUMPRODUCT(MAX(A1:A4*(B1:B4=C1:C4))), the inner part, MAX(A1:A4*(B1:B4=C1:C4)), appears to return a vector of 4 value, each of which equals the MAX value. At least that's what I see when I evaluate using F9. It also makes some sort of sense that it doesn't return a single value, because if it did, then one should be able to enter MAX(A1:A4*(B1:B4=C1:C4)) directly (with out CSE) and get the answer. Which one can't. So in the first formula, SUMPRODUCT seems to be receiving a vector of 4 identical values but is not summing them. This appears to be different behaviour to what you would see if you directly entered, say, SUMPRODUCT(A1:A4). In this case, A1:A4 is summed up (I guess you could argue that it's first being multiplied by a unit vector) I'm curious as to whether I've misunderstood something or whether SUMPRODUCT really does behave differently depending on the context - the context appearing to be whether it receives a direct reference to a vector or the whether it receives a vector as a result of the evaluation of some internal formula. Any thoughts?
I think the inner part as you call it returns a vector of length 1 which is different than a scalar. SUMPRODUCT can convert that vector into a scalar.
__________________
Come on. Let's go space truckin'. Come on!
#30
10-24-2018, 06:04 PM
 Gareth Keenan Member CAS Join Date: Feb 2006 Location: Garden State Studying for #9 Favorite beer: butter Posts: 1,300

This thread has gone off the rails in the most delightful of ways.