Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
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!


Reply
 
Thread Tools Search this Thread Display Modes
  #21  
Old 10-23-2018, 11:37 AM
DeepPurple's Avatar
DeepPurple DeepPurple is offline
Member
 
Join Date: Jun 2004
Posts: 4,210
Default

Quote:
Originally Posted by Hedges View Post

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!
Reply With Quote
  #22  
Old 10-23-2018, 11:50 AM
CuriousGeorge CuriousGeorge is offline
Member
CAS SOA
 
Join Date: Dec 2005
Posts: 1,259
Default

Fight! Fight! Nerd Fight!
Reply With Quote
  #23  
Old 10-23-2018, 12:32 PM
DeepPurple's Avatar
DeepPurple DeepPurple is offline
Member
 
Join Date: Jun 2004
Posts: 4,210
Default

Quote:
Originally Posted by CuriousGeorge View Post
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!
Reply With Quote
  #24  
Old 10-23-2018, 01:43 PM
tommie frazier tommie frazier is offline
Member
 
Join Date: Aug 2003
Favorite beer: The kind with 2 e's
Posts: 22,926
Default

Quote:
Originally Posted by DeepPurple View Post
=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.
Reply With Quote
  #25  
Old 10-23-2018, 02:13 PM
DeepPurple's Avatar
DeepPurple DeepPurple is offline
Member
 
Join Date: Jun 2004
Posts: 4,210
Default

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

Quote:
Originally Posted by Noonien Soong View Post
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
Reply With Quote
  #27  
Old 10-23-2018, 05:15 PM
JMO's Avatar
JMO JMO is offline
Carol Marler
Non-Actuary
 
Join Date: Sep 2001
Location: Back home again in Indiana
Studying for Nothing actuarial.
Posts: 37,607
Default

Quote:
Originally Posted by tommie frazier View Post
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 View Post
JMO is right
Quote:
Originally Posted by campbell View Post
I agree with JMO.
Quote:
Originally Posted by Westley View Post
And def agree w/ JMO.
Quote:
Originally Posted by MG View Post
This. And everything else JMO wrote.
And this all purpose permanent quote:
Quote:
Originally Posted by Dr T Non-Fan View Post
Yup, it is always someone else's fault.
MORE:
All purpose response for careers forum:
Quote:
Originally Posted by DoctorNo View Post
Depends upon the employer and the situation.
Quote:
Originally Posted by Sredni Vashtar View Post
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.
Reply With Quote
  #28  
Old 10-24-2018, 02:09 AM
Hedges Hedges is offline
Member
SOA
 
Join Date: Apr 2002
Location: I've been Shanghaied!
Posts: 472
Default

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..
Reply With Quote
  #29  
Old 10-24-2018, 04:56 PM
DeepPurple's Avatar
DeepPurple DeepPurple is offline
Member
 
Join Date: Jun 2004
Posts: 4,210
Default

Quote:
Originally Posted by Hedges View Post
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!
Reply With Quote
  #30  
Old 10-24-2018, 06:04 PM
Gareth Keenan's Avatar
Gareth Keenan Gareth Keenan is offline
Member
CAS
 
Join Date: Feb 2006
Location: Garden State
Studying for #9
Favorite beer: butter
Posts: 1,300
Default

This thread has gone off the rails in the most delightful of ways.
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:15 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.31552 seconds with 10 queries