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

 Enter your email to subscribe to DW Simpson weekly actuarial job updates. li.signup { display: block; text-align: center; text-size: .8; padding: 0px; margin: 8px; float: left; } Entry Level Casualty Health Life Pension All Jobs

#1
02-11-2019, 02:44 PM
 gaudettj Member SOA Join Date: Nov 2013 Location: Canada Favorite beer: All of the above Posts: 240
Excel Skills

In a few past interviews, I have been asked "What functions do you use in Excel?" after already having a high level discussion on different types of analysis / reporting that I have worked on in my current role. Does anyone have any thoughts on what the interviewer may be looking for here? I've always been super confused on how redundant the question seemed, and have never really figured out a good way to answer.

For context, I have about 3 years of experience using Excel and Access and Business Intelligence tools on a daily basis.
__________________
________________________
VEE's P(8) FM(7) IFM(7-MFE) SRM(VEE credit) LTAM(6) STAM PA | JOB ACTUARIAL JOB
#2
02-11-2019, 03:05 PM
 ShundayBloodyShunday Member CAS Join Date: Apr 2013 Posts: 2,640

"High level discussions" doesn't say anything about whether or not you understand the nitty-gritty details.

Also, there are multiple ways you could do something in Excel to "get an answer." E.g., there is VLOOKUP/HLOOKUP vs. INDEX + MATCH.

So I would just talk about a few of the functions you like to use to organize and manage data flow within the workbook you're using.
#3
02-11-2019, 03:10 PM
 Canadiens Fan Member SOA Join Date: Oct 2013 Location: Canada Studying for LFV-C Favorite beer: Blanche de Chambly Posts: 4,386

VLOOKUP and IF (and its variants) are the common Excel functions that actuaries use.
__________________
ASA LP LFV-C LRM
#4
02-11-2019, 03:17 PM
 gaudettj Member SOA Join Date: Nov 2013 Location: Canada Favorite beer: All of the above Posts: 240

so basically I was on the right track with vlookup, if (and other conditional functions), subtotals, etc.? Because that's the answer I gave each time (along with a brief explanation of how I use it).
__________________
________________________
VEE's P(8) FM(7) IFM(7-MFE) SRM(VEE credit) LTAM(6) STAM PA | JOB ACTUARIAL JOB
#5
02-11-2019, 03:25 PM
 gaudettj Member SOA Join Date: Nov 2013 Location: Canada Favorite beer: All of the above Posts: 240

I just didn't know if there was something else I was missing when explaining my skill set. I think my biggest insecurity with this is that most of the analysis work I do doesn't really happen in Excel.

The typical data flow is:

corp databases
--> Access (if further joining/aggregating is required that can't easily be performed in BI)
--> Excel (where the data is compiled for a final report, typically using vlookups to the various datasets involved)
__________________
________________________
VEE's P(8) FM(7) IFM(7-MFE) SRM(VEE credit) LTAM(6) STAM PA | JOB ACTUARIAL JOB
#6
02-11-2019, 04:19 PM
 DyalDragon Member SOA Join Date: Apr 2009 Location: Here Studying for the hell of it... College: AASU Favorite beer: This one... Posts: 31,818

If campbell is the one interviewing you, make sure you mention your heavy dependence on RAND()
__________________
P FM MFE MLC C VEE FAP FAP FAP FAP FAP FAP FAP FAP
Predictive Analytics

Quote:
 Originally Posted by Androzani Major Maybe a better statement is that I enjoyed having experienced it both ways?
#7
02-11-2019, 04:47 PM
 Helena Lake Member SOA AAA Join Date: Jan 2019 Favorite beer: Whisky Posts: 925

I'd suggest you should be able to reference VLOOKUP/HLOOKUP vs. INDEX + MATCH., as mentioned by Shunday above... as well as SUMIFS, COUNTIFS, etc. INDIRECT is handy, RAND() and RANDBETWEEN() are super useful for a lot of excel-based modeling. And if you've got some VBA skills, this is a good time to mention them. If you're proficient with the statistical functions in Excel, bring them up.

Lots of people say they're good at Excel, but have never used the Lookup functions, let alone anything more sophisticated. Chances are, your interviewers are looking to weed out the people who don't know how much they don't know about Excel, and think that they're "expert" users because they can make a simple spreadsheet that looks nice... but can't actually make Excel into a useful tool for complex non-statistically-modelable scenarios.
__________________
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.
#8
02-11-2019, 04:51 PM
 fdsafdsa Member SOA Join Date: Mar 2018 Posts: 1,880

Quote:
 Originally Posted by Helena Lake I'd suggest you should be able to reference VLOOKUP/HLOOKUP vs. INDEX + MATCH., as mentioned by Shunday above... as well as SUMIFS, COUNTIFS, etc. INDIRECT is handy, RAND() and RANDBETWEEN() are super useful for a lot of excel-based modeling. And if you've got some VBA skills, this is a good time to mention them. If you're proficient with the statistical functions in Excel, bring them up. Lots of people say they're good at Excel, but have never used the Lookup functions, let alone anything more sophisticated. Chances are, your interviewers are looking to weed out the people who don't know how much they don't know about Excel, and think that they're "expert" users because they can make a simple spreadsheet that looks nice... but can't actually make Excel into a useful tool for complex non-statistically-modelable scenarios.
Rand,
__________________
You can't control other people's actions, but you can control how you react to them.
#9
02-11-2019, 04:55 PM
 Helena Lake Member SOA AAA Join Date: Jan 2019 Favorite beer: Whisky Posts: 925

Quote:
 Originally Posted by fdsafdsa Rand,
Why the laughter? What joke have I missed?
__________________
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.
#10
02-11-2019, 05:06 PM
 1695814 Member SOA AAA Join Date: Aug 2002 Studying for nothing. I quit. Favorite beer: Root Posts: 35,543

Quote:
 Originally Posted by DyalDragon If campbell is the one interviewing you, make sure you mention your heavy dependence on RAND()
fyi, Mr Dragon was being sarcastic...I'm pretty sure...
Quote:
 Originally Posted by Helena Lake Why the laughter? What joke have I missed?
In these circles, the RNG within Excel has a poor reputation. It may be perfectly cromulent for your purposes, but it's not held in high regard for big time models.

If MPC peruses this thread she'll definitely have some more-useful reading material for you.

Last edited by 1695814; 02-11-2019 at 05:11 PM..