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


Reply
 
Thread Tools Display Modes
  #21  
Old 09-14-2007, 04:11 PM
Phil's Avatar
Phil Phil is offline
Site Supporter
Site Supporter
 
Join Date: Sep 2001
Location: not on board the Enterprise
Posts: 5,667
Default

Is there a way to have Excel pop up a dialog box whenever a cell anywhere on it (even on a different sheet from the one you're looking at) becomes an Error value?

(or better, whenever a previously non-error cell becomes an error)
Reply With Quote
  #22  
Old 09-14-2007, 04:24 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: 78,953
Blog Entries: 6
Default

I imagine someone could write some VBA thing to check for that....
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #23  
Old 09-14-2007, 04:49 PM
dumples dumples is offline
Member
CAS
 
Join Date: Sep 2003
Posts: 1,240
Default

Call me crazy, but I thought that article was chicken little-esque - "the sky is falling" - with the whole #value thing. Obviously if you have a cell with a problem, that problem will propegate to all of it's dependent cells. The fact that it is a #value error and is very easy to detect makes it easy to figure out. The hard errors are when you try and type 1000000 and you accidently type 10000000. For some reason everything is off by a factor of 10 - at least with the #value error, you don't print something out and show it to your audience - it's clearly wrong. Also, if you have an error, you can easily find its origin using the trace error function on the auditing toolbar.

All of these new links pale in comparison to that original hdykysir.pdf file. The second link was particularly unhelpful - it gave the advice "use subtotal rather than sum" - yeah like there's a considerable advantage to seeing " subtotal(109,...) all over the place.
Reply With Quote
  #24  
Old 09-15-2007, 06:01 AM
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: 78,953
Blog Entries: 6
Default

Yes, not all of it is good advice for actuaries (or anyone else, for that matter). You have to think of why certain tips are in there, and adapt to your own use. If the point of one technique is to reduce confusion, but for your own purposes increases confusion, then ignore it.

To be more fair to that condensed list of tips, it's partly a promotion for their $30 guide....but looking at the excerpt, it doesn't really inspire me to think that it would be more helpful than the "How do you know your spreadsheet is right?" article.
__________________
It's STUMP

LinkedIn Profile

Last edited by campbell; 09-15-2007 at 06:06 AM..
Reply With Quote
  #25  
Old 01-14-2008, 11:05 AM
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: 78,953
Blog Entries: 6
Default

The latest issue of CompAct (SOA Tech section newsletter) includes an article I wrote on this subject.

Link: http://soa.org/library/newsletters/c...2008-iss26.pdf

It starts on page 11.
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #26  
Old 01-14-2008, 11:33 AM
Rick_G's Avatar
Rick_G Rick_G is offline
Member
 
Join Date: May 2005
Location: Just south of the Big Chicken
Posts: 2,075
Default

Quote:
Originally Posted by campbell View Post
The latest issue of CompAct (SOA Tech section newsletter) includes an article I wrote on this subject.

Link: http://soa.org/library/newsletters/c...2008-iss26.pdf

It starts on page 11.
Thanks for telling us about this - and for writing the article!
__________________
Rick Groszkiewicz


Now offering online seminars, live seminars, and everything else under the sun for actuarial exams.
EA-2F and EA-2L and EA-1
Reply With Quote
  #27  
Old 01-14-2008, 12:52 PM
Barbara Barbara is offline
Member
 
Join Date: Jan 2004
Posts: 1,134
Default

Quote:
Originally Posted by campbell View Post
"How do you know your spreadsheet is right?" (PDF) is an excellent article from Philip Bewig, from July 2005.

I like the look of his "Traffic Lights" error checking system. I did a quick google search, and don't see a copy of his program on the web. Does anyone know have a copy, or know where to get one?
Reply With Quote
  #28  
Old 01-14-2008, 01:08 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: 78,953
Blog Entries: 6
Default

Quote:
Originally Posted by Barbara View Post
I like the look of his "Traffic Lights" error checking system. I did a quick google search, and don't see a copy of his program on the web. Does anyone know have a copy, or know where to get one?
Take a look at this link and scroll down. I do not know if the link (scroll down to TrafficLights.xla link) is active - you've got to be a member of the Yahoo group to see it.
__________________
It's STUMP

LinkedIn Profile

Last edited by campbell; 01-14-2008 at 01:08 PM.. Reason: oops, add the link
Reply With Quote
  #29  
Old 01-29-2008, 05:47 AM
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: 78,953
Blog Entries: 6
Default

I sent my article to my mother, and she told me that she read it right after she had spent the day dealing with a spreadsheet chock-full of errors (she's an office manager for a podiatrist group). She told me she was laughing from the synchronicity.

As well, I recently got sent a review copy of this book on techniques to reduce spreadsheet error, and to provide better controls.

Super-short review: good start for a big problem, perfect to give to all new hires in entry-level positions.

Longer review: later in CompAct, I hope.
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #30  
Old 02-12-2008, 12:38 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: 78,953
Blog Entries: 6
Default

Safer business spreadsheets - a practical approach that works


Quote:
It may be different for you, but I see new graduates starting work with very poor spreadsheet skills. They are taught the basics (enough to do the work due today, anyway) by the person who was in their job before them, rather than by a senior person with experience and understanding of risk control. To me, this is like getting 18 year olds to teach 17 year olds to drive - do that for a few years and see what you get.

I also remember seeing research that showed that spreadsheet users typically over estimated their skills. It's no wonder we have problems.

This happens even in highly professional firms with excellent training programs. I suspect it's partly because many senior managers can't mentor youngsters because they never had the opportunity to develop strong spreadsheet skills themselves; partly because training programs focus on technical skills and not risk control; and also because spreadsheet issues fall between two stools - managers think they are an IT problem, and IT thinks they are a problem for the business units.
I think this is true in the actuarial world as well. It seems the standard way of learning how to deal with spreadsheets is throw other people's spreadsheets at a newbie and have people who know what's reasonable check results. Maybe they learn good spreadsheet practices by trial & error.
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
Reply

Tags
machine learning, predictive analytics, speadsheet error, spreadsheets

Thread Tools
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 01:08 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, 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.44011 seconds with 10 queries