Actuarial Outpost Excel question
 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

 Salary Surveys Property & Casualty, Life, Health & Pension Health Actuary JobsInsurance & Consulting jobs for Students, Associates & Fellows Actuarial Recruitment Visit DW Simpson's website for more info. www.dwsimpson.com/about Casualty JobsProperty & Casualty jobs for Students, Associates & Fellows

#11
09-25-2018, 04:05 PM
 DeepPurple Member Join Date: Jun 2004 Posts: 4,292

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!
#12
09-25-2018, 04:22 PM
 IANAE Member CAS AAA Join Date: Oct 2017 Posts: 217

Quote:
 Originally Posted by DeepPurple 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.
#13
09-25-2018, 04:31 PM
 ALivelySedative Member CAS Join Date: Dec 2013 Location: Land of the Pine College: UNC-Chapel Hill Alum Favorite beer: Red Oak Posts: 3,173

Quote:
 Originally Posted by DeepPurple 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.
#14
09-25-2018, 05:43 PM
 DeepPurple Member Join Date: Jun 2004 Posts: 4,292

Quote:
 Originally Posted by ALivelySedative 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!
#15
09-25-2018, 06:43 PM
 IANAE Member CAS AAA Join Date: Oct 2017 Posts: 217

Quote:
 Originally Posted by DeepPurple 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.
#16
09-25-2018, 07:31 PM
 Dr T Non-Fan Member SOA AAA Join Date: Sep 2001 Location: Just outside of Nowhere Posts: 95,699

"Eh, I'll just let the magic of the pivot table do it all for me."
__________________
"Facebook is a toilet." -- LWTwJO
#17
09-26-2018, 01:42 PM
 Sredni Vashtar Member Join Date: Mar 2010 Favorite beer: pilseners Posts: 8,035 Blog Entries: 1

Quote:
 Originally Posted by IANAE 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().
__________________
L’humour est la politesse du désespoir

Last edited by Sredni Vashtar; 09-26-2018 at 01:47 PM..
#18
09-26-2018, 03:21 PM
 ALivelySedative Member CAS Join Date: Dec 2013 Location: Land of the Pine College: UNC-Chapel Hill Alum Favorite beer: Red Oak Posts: 3,173

Quote:
 Originally Posted by DeepPurple 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?".
#19
10-19-2018, 01:16 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 Dr T Non-Fan "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 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.
#20
10-19-2018, 03:25 PM
 Sredni Vashtar Member Join Date: Mar 2010 Favorite beer: pilseners Posts: 8,035 Blog Entries: 1

Quote:
 Originally Posted by Dr T Non-Fan "Eh, I'll just let the magic of the pivot table do it all for me."
twss.
__________________
L’humour est la politesse du désespoir

 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 05:45 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