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



Reply
 
Thread Tools Search this Thread Display Modes
  #11  
Old 09-25-2018, 05:05 PM
DeepPurple's Avatar
DeepPurple DeepPurple is offline
Member
 
Join Date: Jun 2004
Posts: 4,231
Default

Again I say if you use "IF" statements, you are making life much harder on yourself and anyone trying to read your code.

=B1/SUMPRODUCT($B$1:$B$100,--(LEFT($A$1:$A$100,1)=LEFT(A1,1)))

assumes your data is in cells a1:b100


sumproduct is likely the most powerful excel function that doesn't get used enough. It puts sumif to shame. The only weirdness is that boolean T & F's need to be turned into 1's and 0's. I use "--" to do that.
__________________
Come on. Let's go space truckin'. Come on!
Reply With Quote
  #12  
Old 09-25-2018, 05:22 PM
IANAE IANAE is offline
Member
CAS AAA
 
Join Date: Oct 2017
Posts: 206
Default

Quote:
Originally Posted by DeepPurple View Post
Again I say if you use "IF" statements, you are making life much harder on yourself and anyone trying to read your code.

...

sumproduct is likely the most powerful excel function that doesn't get used enough. It puts sumif to shame.
Reply With Quote
  #13  
Old 09-25-2018, 05:31 PM
ALivelySedative's Avatar
ALivelySedative ALivelySedative is offline
Member
CAS
 
Join Date: Dec 2013
Location: Land of the Pine
College: UNC-Chapel Hill Alum
Favorite beer: Red Oak
Posts: 2,771
Default

Quote:
Originally Posted by DeepPurple View Post
Again I say if you use "IF" statements, you are making life much harder on yourself and anyone trying to read your code.

=B1/SUMPRODUCT($B$1:$B$100,--(LEFT($A$1:$A$100,1)=LEFT(A1,1)))

assumes your data is in cells a1:b100


sumproduct is likely the most powerful excel function that doesn't get used enough. It puts sumif to shame. The only weirdness is that boolean T & F's need to be turned into 1's and 0's. I use "--" to do that.
I find this impressive. Will steal.
Reply With Quote
  #14  
Old 09-25-2018, 06:43 PM
DeepPurple's Avatar
DeepPurple DeepPurple is offline
Member
 
Join Date: Jun 2004
Posts: 4,231
Default

Quote:
Originally Posted by ALivelySedative View Post
I find this impressive. Will steal.
Thank you for the compliment. I was reared on APL, and the boolean vector stuff is an adaptation of APL-isms. "slashiotarho" was a common phrase and x.+ was a sumproduct. I still miss APL.
__________________
Come on. Let's go space truckin'. Come on!
Reply With Quote
  #15  
Old 09-25-2018, 07:43 PM
IANAE IANAE is offline
Member
CAS AAA
 
Join Date: Oct 2017
Posts: 206
Default

Quote:
Originally Posted by DeepPurple View Post
I was reared on APL, and the boolean vector stuff is an adaptation of APL-isms.
...

I still miss APL.
I suspected the APL connection.

The ability to employ matrices and vectors in Excel formulas is extremely underappreciated.
Reply With Quote
  #16  
Old 09-25-2018, 08:31 PM
Dr T Non-Fan Dr T Non-Fan is offline
Member
SOA AAA
 
Join Date: Sep 2001
Location: Just outside of Nowhere
Posts: 93,509
Default

"Eh, I'll just let the magic of the pivot table do it all for me."
__________________
"Facebook is a toilet." -- LWTwJO
Reply With Quote
  #17  
Old 09-26-2018, 02:42 PM
Sredni Vashtar's Avatar
Sredni Vashtar Sredni Vashtar is offline
Member
 
Join Date: Mar 2010
Favorite beer: pilseners
Posts: 6,871
Blog Entries: 1
Default

Quote:
Originally Posted by IANAE View Post
sumproduct is likely the most powerful excel function that doesn't get used enough. It puts sumif to shame. The only weirdness is that boolean T & F's need to be turned into 1's and 0's. I use "--" to do that.
Eh, it's powerful but usually unnecessary.

Though it was especially sexy before sumifs().
__________________
Sredni Vashtar went forth,
His thoughts were red thoughts and his teeth were white.
His enemies called for peace, but he brought them death.
Sredni Vashtar the Beautiful.

Last edited by Sredni Vashtar; 09-26-2018 at 02:47 PM..
Reply With Quote
  #18  
Old 09-26-2018, 04:21 PM
ALivelySedative's Avatar
ALivelySedative ALivelySedative is offline
Member
CAS
 
Join Date: Dec 2013
Location: Land of the Pine
College: UNC-Chapel Hill Alum
Favorite beer: Red Oak
Posts: 2,771
Default

Quote:
Originally Posted by DeepPurple View Post
Thank you for the compliment. I was reared on APL, and the boolean vector stuff is an adaptation of APL-isms. "slashiotarho" was a common phrase and x.+ was a sumproduct. I still miss APL.
Used it today. Can't wait for a colleague to see it and be all "wtf?".
Reply With Quote
  #19  
Old 10-19-2018, 02:16 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,660
Default

Quote:
Originally Posted by Dr T Non-Fan View Post
"Eh, I'll just let the magic of the pivot table do it all for me."
IATP
__________________
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 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 El Actuario View Post
Therapists should ask the right questions, not give the right answers.
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
  #20  
Old 10-19-2018, 04:25 PM
Sredni Vashtar's Avatar
Sredni Vashtar Sredni Vashtar is offline
Member
 
Join Date: Mar 2010
Favorite beer: pilseners
Posts: 6,871
Blog Entries: 1
Default

Quote:
Originally Posted by Dr T Non-Fan View Post
"Eh, I'll just let the magic of the pivot table do it all for me."
twss.
__________________
Sredni Vashtar went forth,
His thoughts were red thoughts and his teeth were white.
His enemies called for peace, but he brought them death.
Sredni Vashtar the Beautiful.
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 08:39 AM.


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.25093 seconds with 9 queries