Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Careers - Employment
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
  #81  
Old 02-20-2019, 09:03 PM
Sredni Vashtar's Avatar
Sredni Vashtar Sredni Vashtar is online now
Member
 
Join Date: Mar 2010
Favorite beer: pilseners
Posts: 7,223
Blog Entries: 1
Default

Quote:
Originally Posted by campbell View Post
So.... on Named Ranges.

I used to be really big for named ranges, but then when I went to a EUSpRIG conference, and we had an impromptu 2-hour debate... I saw the problem.

I mainly use PrintArea (or is it Print_Area?), and maybe two additional named ranges (usually a lookup table) in any given spreadsheet.

Named Ranges are best used if you're dealing with a bunch of VBA, but for formula-based stuff, it's generally better to use explicit cell references.
Yeah, exactly.
Named ranges are great for VBA.
And okay for some weird cases like printing or data-validation dropdowns.
And okay-ish if you are referencing something that is common and obvious like "YEAR".

Quote:
Originally Posted by Dan Moore View Post
I like a modest amount of named ranges. The advantage disappears when you have, say, over 20.
But this, the more there are the less useful they become, and the more problems they cause.
__________________
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
  #82  
Old 02-20-2019, 09:16 PM
ShundayBloodyShunday's Avatar
ShundayBloodyShunday ShundayBloodyShunday is offline
Member
CAS
 
Join Date: Apr 2013
Posts: 2,640
Default

Quote:
Originally Posted by Sredni Vashtar View Post
Yes, the actuaries at my company don't know Index(match()) so I try to avoid using it. I know most people who touch my worksheets will have to look up how it works.

That said, every bloody time I inherit a complicated tool, I find that somebody has made a horrendous mess of vlookups, hlookups, indirects, offsets, named ranges, etc. because they are missing this one key piece of knowledge.

Heck, the tool I'm updating right now was using VBA to copy over numerous ranges instead of a simple index/match.



Btw, another advantage of Index(Match()) is you can highlight the index match, and press f5, and Excel will take you to the indexed cell. Which makes it great for auditing. If you know how to use it.
I feel your pain as I've gone through a similar situation. Although, what I've inherited is a data flow that is going through several different files and it turns out that those individual files aren't used for anything but the intended analysis.
Reply With Quote
  #83  
Old 02-21-2019, 12:49 PM
Sredni Vashtar's Avatar
Sredni Vashtar Sredni Vashtar is online now
Member
 
Join Date: Mar 2010
Favorite beer: pilseners
Posts: 7,223
Blog Entries: 1
Default

Quote:
Originally Posted by Canadiens Fan View Post
I've found INDIRECT to be very useful on a few occasions, especially when I had tons of Excel tabs to link data to.
Yeah, it bugs me that Excel doesn't have a better way to formulaically reference a tab.
That's about the only use I can think of. And even then, you should probably avoid having a bunch of tabs.


Indirect is the function of last resort.
__________________
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
  #84  
Old 02-21-2019, 01:09 PM
Helena Lake's Avatar
Helena Lake Helena Lake is offline
Member
SOA AAA
 
Join Date: Jan 2019
Favorite beer: Whisky
Posts: 937
Default

Quote:
Originally Posted by Sredni Vashtar View Post
Yeah, exactly.
Named ranges are great for VBA.
And okay for some weird cases like printing or data-validation dropdowns.
And okay-ish if you are referencing something that is common and obvious like "YEAR".


But this, the more there are the less useful they become, and the more problems they cause.
I sometimes use named ranges for actual blocks of data, but it's a rare thing. I think my most common use of named ranges is for input cells, because it makes the resulting formulas easier to read. So, for example, let's say I've got a model that dynamically varies output based on the selected year, month, line of business, state, and trend rate. If it were going to be used by several people, especially if they're not actuaries, I'd likely name the input cells so that it's really obvious what it's doing.

But overall, yeah - not too many named ranges, used judiciously, and only if it really really makes sense and will be used for a long time.
__________________
Disclaimers:
I am not funny, and I'm bad at picking up humor. Unless it's puns. I get puns.
I am not clever or devious, and I don't do subtext so I'll probably miss yours.
Text doesn't have intonation, so I might misinterpret your post. I'll ask for clarification if you do the same.
I like cats.
Reply With Quote
  #85  
Old 02-21-2019, 01:15 PM
Rickyfire's Avatar
Rickyfire Rickyfire is offline
Member
CAS
 
Join Date: Dec 2007
Location: Boston Area
Studying for Exam 4
Favorite beer: None
Posts: 8,432
Default

I find Range names very useful to audit old work that I did.

e.g. - my data tab will contain things like

Data_Net_WP
Data_Net_EP
Data_Net_AY

etc - easy to remember which column is which when making lots of sumifs.
Reply With Quote
  #86  
Old 02-21-2019, 01:22 PM
Rickyfire's Avatar
Rickyfire Rickyfire is offline
Member
CAS
 
Join Date: Dec 2007
Location: Boston Area
Studying for Exam 4
Favorite beer: None
Posts: 8,432
Default

Also - linking with arrays is much faster. One of my favs for updates.
Reply With Quote
  #87  
Old 02-21-2019, 01:25 PM
Helena Lake's Avatar
Helena Lake Helena Lake is offline
Member
SOA AAA
 
Join Date: Jan 2019
Favorite beer: Whisky
Posts: 937
Default

I hate array formulas in excel. Like with a passion. Who the hell decided that a formula that quits working if you click in the damned formula bar was a good idea? Stupid array formulas!1111!11 Arrgh.
__________________
Disclaimers:
I am not funny, and I'm bad at picking up humor. Unless it's puns. I get puns.
I am not clever or devious, and I don't do subtext so I'll probably miss yours.
Text doesn't have intonation, so I might misinterpret your post. I'll ask for clarification if you do the same.
I like cats.
Reply With Quote
  #88  
Old 02-21-2019, 03:30 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: 86,117
Blog Entries: 6
Default

I avoid array formulas, because basically nobody understands them.

That said, it's a great way to prevent people from changing your formulas (unless they completely delete the array)
__________________
It's STUMP

LinkedIn Profile
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 12:32 PM.


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