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

 Thread Tools Search this Thread Display Modes
#11
09-25-2018, 04:05 PM
 DeepPurple Member Join Date: Jun 2004 Posts: 4,274

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: 213

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,061

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,274

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: 213

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: 94,718

"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: 7,355 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().
__________________
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 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,061

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,660

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: 7,355 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.
__________________
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.

 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 08:51 AM.

 -- 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