Actuarial Outpost Excel: sum of squares(or any operation) on a set of cells.
 User Name Remember Me? Password
 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

 Upload your resume securely at https://www.dwsimpson.com to be contacted when our jobs meet your skills and objectives.

 Thread Tools Search this Thread Display Modes
#21
10-23-2018, 10:37 AM
 DeepPurple Member Join Date: Jun 2004 Posts: 4,292

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, 10:50 AM
 CuriousGeorge Member CAS SOA Join Date: Dec 2005 Posts: 1,371

Fight! Fight! Nerd Fight!
#23
10-23-2018, 11:32 AM
 DeepPurple Member Join Date: Jun 2004 Posts: 4,292

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, 12:43 PM
 tommie frazier Member Join Date: Aug 2003 Favorite beer: The kind with 2 e's Posts: 23,170

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?
#25
10-23-2018, 01:13 PM
 DeepPurple Member Join Date: Jun 2004 Posts: 4,292

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

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)
__________________
FSA ~ Fellow Slyder Aficionado
#27
10-23-2018, 04:15 PM
 JMO Carol Marler Non-Actuary Join Date: Sep 2001 Location: Back home again in Indiana Studying for Nothing actuarial. Posts: 37,643

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 Nov. 20, 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 El Actuario Therapists should ask the right questions, not give the right answers.
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, 01:09 AM
 Hedges Member SOA Join Date: Apr 2002 Location: I've been Shanghaied! Posts: 483

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 01:14 AM..
#29
10-24-2018, 03:56 PM
 DeepPurple Member Join Date: Jun 2004 Posts: 4,292

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, 05:04 PM
 Gareth Keenan Member CAS Join Date: Feb 2006 Location: Garden State Studying for #9 Favorite beer: butter Posts: 1,308

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

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 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 07:50 PM.

 -- Default Style - Fluid Width ---- Default Style - Fixed Width ---- Old Default Style ---- Easy on the eyes ---- Smooth Darkness ---- Chestnut ---- Apple-ish Style ---- If Apples were blue ---- If Apples were green ---- If Apples were purple ---- Halloween 2007 ---- B&W ---- Halloween ---- AO Christmas Theme ---- Turkey Day Theme ---- AO 2007 beta ---- 4th Of July Contact Us - Actuarial Outpost - Archive - Privacy Statement - Top

Powered by vBulletin®
Copyright ©2000 - 2019, 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.46347 seconds with 10 queries