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

DW Simpson International Actuarial Jobs
Canada  Asia  Australia  Bermuda  Latin America  Europe


General Actuarial Non-Specific Actuarial Topics - Before posting a thread, please browse over our other sections to see if there is a better fit, such as Careers - Employment, Actuarial Science Universities Forum or any of our other 100+ forums.

Reply
 
Thread Tools Search this Thread Display Modes
  #21  
Old 11-25-2015, 12:15 PM
campbell's Avatar
campbell campbell is offline
Mary Pat Campbell
SOA AAA
 
Join Date: Nov 2003
Location: NY
Studying for duolingo and coursera
Favorite beer: Murphy's Irish Stout
Posts: 93,072
Blog Entries: 6
Default

One item that would be a quick cleanup, and shorten the formula -- you have same quantity calculated multiple times in the formula

IF($O134="to",Q$1,$O134)

That's a scalar that can be calculated in a separate column, which would simplify this formula


One basic principle of numerical programming is that you don't calculate the same exact thing more times than you have to. Usually, it needs to be calculated only once.
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #22  
Old 11-25-2015, 12:17 PM
Vorian Atreides's Avatar
Vorian Atreides Vorian Atreides is offline
Wiki/Note Contributor
CAS
 
Join Date: Apr 2005
Location: As far as 3 cups of sugar will take you
Studying for CSPA
College: Hard Knocks
Favorite beer: Most German dark lagers
Posts: 68,186
Default

Quote:
Originally Posted by campbell View Post
One basic principle of numerical programming is that you don't calculate the same exact thing more times than you have to. Usually, it needs to be calculated only once.


I'm also a firm believer in "intermediate calculation" steps . . . and hide those columns if necessary for presentation.
__________________
I find your lack of faith disturbing

Why should I worry about dying? Itís not going to happen in my lifetime!


Freedom of speech is not a license to discourtesy

#BLACKMATTERLIVES
Reply With Quote
  #23  
Old 11-25-2015, 12:20 PM
2pac Shakur 2pac Shakur is offline
Member
SOA
 
Join Date: Jun 2003
Location: short
Posts: 94,211
Default

sometimes actuaries try to get too smrt and the formulas become more efficient but less transparent imo
__________________
Reply With Quote
  #24  
Old 11-25-2015, 12:21 PM
2pac Shakur 2pac Shakur is offline
Member
SOA
 
Join Date: Jun 2003
Location: short
Posts: 94,211
Default

i hate index match
and i hate pivot tables

i love vlookups
and i love sumif/sumifs

and if you don't like it - you can F1 off
__________________
Reply With Quote
  #25  
Old 11-25-2015, 12:28 PM
Abelian Grape's Avatar
Abelian Grape Abelian Grape is offline
Meme-ber                         Meme-ber
CAS
 
Join Date: Jul 2014
Favorite beer: Allagash Curieux
Posts: 43,244
Default

sumproduct imo
__________________

live laugh love
Reply With Quote
  #26  
Old 11-25-2015, 12:37 PM
Dan Moore's Avatar
Dan Moore Dan Moore is offline
Member
SOA AAA
 
Join Date: Jan 2008
College: University of Dallas
Posts: 2,922
Blog Entries: 1
Default

Quote:
Originally Posted by Malcolm View Post
They're both hideous.
Quote:
Originally Posted by Malcolm View Post
"Slightly less hideous" doesn't really jibe with "best practice" for me.
Better to light a candle, etc.
__________________
The best time to plant an oak tree is twenty years ago. The second best time is right now.
Reply With Quote
  #27  
Old 11-25-2015, 12:55 PM
2pac Shakur 2pac Shakur is offline
Member
SOA
 
Join Date: Jun 2003
Location: short
Posts: 94,211
Default

Quote:
Originally Posted by Dan Moore View Post
I'm not sure what you're calling hideous. My point is that the first formula below is easier to read than the second formula, even though they are essentially identical. The first formula has Alt-Enters placed within the formula to tidy it up, without affecting the formula result.

=SUMPRODUCT(
INDEX(AllRates!M$5:M$40000,$N134):INDEX(AllRates!M $5:M$40000,$N134-1+IF($O134="to",Q$1,$O134)),
INDEX(DIMOD2COLA!$T$7:$U$976,1,$P134):INDEX(DIMOD2 COLA!$T$7:$U$976,IF($O134="to",Q$1,$O134),$P134),
INDEX(DIMOD2COLA!$B$7:$C$976,1,$A134):INDEX(DIMOD2 COLA!$B$7:$C$976,IF($O134="to",Q$1,$O134),$A134),
INDEX(DIMOD2COLA!$D$7:$K$976,1,$B134):INDEX(DIMOD2 COLA!$D$7:$K$976,IF($O134="to",Q$1,$O134),$B134),
INDEX(DIMOD2COLA!$L$7:$N$976,1,$C134):INDEX(DIMOD2 COLA!$L$7:$N$976,IF($O134="to",Q$1,$O134),$C134))

=SUMPRODUCT(INDEX(AllRates!M$5:M$40000,$N134):INDE X(AllRates!M $5:M$40000,$N134-1+IF($O134="to",Q$1,$O134)),INDEX(DIMOD2COLA!$T$7: $U$976,1,$P134):INDEX(DIMOD2 COLA!$T$7:$U$976,IF($O134="to",Q$1,$O134),$P134),I NDEX(DIMOD2COLA!$B$7:$C$976,1,$A134):INDEX(DIMOD2 COLA!$B$7:$C$976,IF($O134="to",Q$1,$O134),$A134),I NDEX(DIMOD2COLA!$D$7:$K$976,1,$B134):INDEX(DIMOD2 COLA!$D$7:$K$976,IF($O134="to",Q$1,$O134),$B134),I NDEX(DIMOD2COLA!$L$7:$N$976,1,$C134):INDEX(DIMOD2 COLA!$L$7:$N$976,IF($O134="to",Q$1,$O134),$C134))
Highlight the entire formula and then use alt enter and then use a different formula - only way alt enter helps imo
__________________
Reply With Quote
  #28  
Old 11-25-2015, 01:20 PM
campbell's Avatar
campbell campbell is offline
Mary Pat Campbell
SOA AAA
 
Join Date: Nov 2003
Location: NY
Studying for duolingo and coursera
Favorite beer: Murphy's Irish Stout
Posts: 93,072
Blog Entries: 6
Default



I agree re: pivot tables in production. Should not be used.
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #29  
Old 11-25-2015, 02:43 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,643
Default

Quote:
Originally Posted by Vorian Atreides View Post


I'm also a firm believer in "intermediate calculation" steps . . . and hide those columns if necessary for presentation.
+1

And I like to see the presentation on a separate tab, same as input goes on a separate tab.

Comments can then be used liberally on the calculation tabs. I don't want them inside the formula, though.
__________________
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
  #30  
Old 11-25-2015, 02:45 PM
2pac Shakur 2pac Shakur is offline
Member
SOA
 
Join Date: Jun 2003
Location: short
Posts: 94,211
Default

Quote:
Originally Posted by JMO View Post
+1

And I like to see the presentation on a separate tab, same as input goes on a separate tab.

Comments can then be used liberally on the calculation tabs. I don't want them inside the formula, though.
yup

instead of sheet 1 sheet 2 sheet 3
default in excel should be
inputs - calculations - output

although i sometimes put input on the output page to see updated iterative results immediately
__________________
Reply With Quote
Reply

Tags
actex, excel, professionalism, spreadsheet, webinar

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 02:51 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, 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.39248 seconds with 9 queries