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

2017 ACTUARIAL SALARY SURVEYS
Contact DW Simpson for a Personalized Salary Survey

Reply
 
Thread Tools Search this Thread Display Modes
  #11  
Old 06-30-2018, 12:44 AM
tommie frazier tommie frazier is offline
Member
 
Join Date: Aug 2003
Favorite beer: The kind with 2 e's
Posts: 22,835
Default

could add fields to validate nonblanks. and then use sumproduct

could use sumifs
__________________
Removed a dated athletic reference under pressure from a friend. You can still give money to help fund research on neurofibromatosis (nf).

General info at www.ctf.org

Team donation page here.
Reply With Quote
  #12  
Old 06-30-2018, 08:50 AM
Steve Grondin Steve Grondin is offline
Member
SOA AAA
 
Join Date: Nov 2001
Posts: 6,369
Default

Quote:
Originally Posted by rhoucag View Post
Once this gets handed over to another person/team or goes through an audit, it may be a blocker.
If your worksheet is "formal" enough to go through a rigorous audit, you need to design it differently. Don't use the result cell to be your audit cell.

"Hold your fire. There's no life forms. It must have short-circuited"
"What, are we being charged by laser cell now?"
Quote:
Originally Posted by rhoucag View Post
Some of the other users here have a habit of changing the whole setup when they encounter a formula they don't understand.
Your workplace has a culture problem that is much bigger than this one worksheet. Where you are in the structure (boss/coworker/grunt) dictates how you should respond.

If you are responsible for this process, lock and password protect the relevant formulas.

If your boss is the one changing things because the worksheet is too complicated for your boss to understand, simplify it. If you absolutely have to do it a certain way because there is no other way to get it to work, you have to explain why. But that seems unlikely to me. For stuff completed under my direction, I don't necessarily have to understand the details if you can show me why using a complicated approach is a superior process.

If you are the grunt (no responsibility other than being a user), explain the problem to your boss and propose a way to fix the problem.

Quote:
Originally Posted by tommie frazier View Post
could add fields to validate nonblanks. and then use sumproduct

could use sumifs
tommie is going in the right direction. If you are dealing with data entry people who can't be trusted to complete and document their task successfully in a less-structured environment, your design needs to make it easier for them to do so.

Does the data lend itself to check sums? Then add them and have the data entry folks verify they match after they finish entering the data.

If there are a limited number of non-relateable entries, have a signature or check box next to each element that says "I entered this data and then verified it after entry"

If you know validation information that applies to every cell (non-zero, for instance), apply that test in a separate cell (perhaps on a different sheet even). Then use a single cell to test if every validation passed. The final test result can be referenced on your final output sheet.

I suppose there are ways use Excel cell validation, but I am not fancy enough to use them to force completion of all data entry items.
Reply With Quote
  #13  
Old 07-01-2018, 12:05 AM
tommie frazier tommie frazier is offline
Member
 
Join Date: Aug 2003
Favorite beer: The kind with 2 e's
Posts: 22,835
Default

to pile on what I was getting at and what Steve suggested was a good direction - extra fields of testing leave a simpler formula eventually and an explicit display of what you are testing and how it will be used. if you assume future users are morans, and you should, laying it all out step by step is a sound idea.
__________________
Removed a dated athletic reference under pressure from a friend. You can still give money to help fund research on neurofibromatosis (nf).

General info at www.ctf.org

Team donation page here.
Reply With Quote
  #14  
Old 07-01-2018, 12:50 PM
Maphisto's Sidekick's Avatar
Maphisto's Sidekick Maphisto's Sidekick is offline
Member
CAS
 
Join Date: Nov 2001
Location: South Park Genetics Lab
College: Ardnox
Favorite beer: The kind with alcohol
Posts: 2,467
Default

Quote:
Originally Posted by tommie frazier View Post
to pile on what I was getting at and what Steve suggested was a good direction - extra fields of testing leave a simpler formula eventually and an explicit display of what you are testing and how it will be used. if you assume future users are morans, and you should, laying it all out step by step is a sound idea.


Also, depending on the implementation or layout, a couple of test cells, doing something like =countifs(A1:A100,""), and judiciously applied conditional formatting to the source data (e.g., if it's blank and shouldn't be, give it a red background) would be easier to follow, and make for better cleaning.
Reply With Quote
  #15  
Old 07-07-2018, 11:30 AM
Sredni Vashtar's Avatar
Sredni Vashtar Sredni Vashtar is offline
Member
 
Join Date: Mar 2010
Favorite beer: pilseners
Posts: 6,423
Blog Entries: 1
Default

Quote:
Originally Posted by rhoucag View Post
Thanks. I need to first pull various items for a couple of legal entities on one sheet. If one items gets forgotten or deleted, the sumif won't warn you. And most internal checks will still work since they will essentially treat the reserve as "zero" and internal consistency is maintained. There are also 3 scenarios I need on this item sheet, and I want to pull each scenario's sum separately.

SUMIF/SUMPRODUCT is desired since some items are the sums of multiple components (sometimes 2, 3, 4, or more).
It sounds like what you really want are two formulas.
(1) to tell you if there is a problem.
(2) one to give you a calculation.

If you want the calculation to error-out, then make (2) an if statement on (1).
__________________
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
  #16  
Old 07-07-2018, 11:59 AM
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,428
Default

First thing to do is to make your input step easier. Have a separate sheet (if you don't do this already) for inputting data. Add comments on where to find the source data. Future data entry grunts will thank you. If there are multiple sources, or even multiple pages per source, group the inputs to put all the stuff from the same page together and preferably in the same order as on the source sheet. Use cell formatting (Outline cells or something) to make it easy to see where to put data and easy to see when something hasn't been done.

Validate your data. Either on the input page or on a separate page. Some suggestions above on ways to do this. If something is missing, show a flag on the input sheet as a reminder.

After data is validated, your formulas won't need extraneous code to test it. This will make the formula page easier to understand, for your boss or for the person who inherits it. Put in comments to explain what you are doing and why.

Finally, have a separate sheet for your results, formatted exactly as you boss likes it.
If you often get the same question about results, put notes on the results page. duh!

I do wish the exams of modules or SOMETHING would teach newbies about good spreadsheet design. And about internal representation of data - but that's another subject entirely.
__________________
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 Apr 5, 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 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.

Last edited by JMO; 07-07-2018 at 12:02 PM..
Reply With Quote
  #17  
Old 07-09-2018, 05:19 PM
mathmajor's Avatar
mathmajor mathmajor is offline
Member
SOA AAA
 
Join Date: Dec 2010
Location: Nowhere in particular
Studying for Japanese
College: B.S. Applied Math
Favorite beer: La Croix Grapefruit
Posts: 9,376
Default

Quote:
Originally Posted by JMO View Post
Could one use VBA (or something) to check that all required data is present?
y tho
__________________
FSA
Reply With Quote
  #18  
Old 07-09-2018, 06:09 PM
Dr T Non-Fan Dr T Non-Fan is offline
Member
SOA AAA
 
Join Date: Sep 2001
Location: Just outside of Nowhere
Posts: 92,404
Default

Quote:
Originally Posted by rhoucag View Post
Agreed, except that SUMPRODUCT can get very nonintuitive for someone who isn't that comfortable with Excel. Once this gets handed over to another person/team or goes through an audit, it may be a blocker. Some of the other users here have a habit of changing the whole setup when they encounter a formula they don't understand.
Two or more conditions makes SUMIF harder to read IMO.
Each pair of comma'ed qualifiers go together in SUMIF, while each qualifier in SUMPRODUCT is within its own parentheses, making it easier to read.
Examples:
SUMIF(range1,range2,value2,range3,value3,range4,va lue4,....)
OR
SUMPRODUCT((range1)*(range2=value2)*(range3=value3 )*(range4=value4)*......)
__________________
DTNF's Basic Philosophy Regarding Posting: There's no emoticon for what I'm feeling! -- Jeff Albertson (CBG)
DTNF's Trademarked Standard Career Advice: "pass some exams and get back to us."
DTNF's Major advice: "Doesn't matter. Choose major that helps you with goal of Career Advice."
DTNF's Résumé Advice: Have a good and interesting answer to every item on it for the interviews.
DTNF's Law of Job Offers: You not only have to qualify for the position, but you also have to be the best candidate available for the offer.
DTNF's Work Philosophy: I am actuary. Please insert data. -- Actuary Actuarying Rodriguez.
Twitches' Advice to Crazy Women: Please just go buy your 30 cats already.
Reply With Quote
  #19  
Old 07-11-2018, 05:04 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,428
Default

Quote:
Originally Posted by mathmajor View Post
y tho
because the whole point of this thread was to figure out what to do if they aren't. Getting them in the first place is logically preferable. JMO of course.
__________________
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 Apr 5, 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 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
  #20  
Old 07-13-2018, 06:09 PM
Philip_Trick Philip_Trick is offline
CAS AAA
 
Join Date: Feb 2013
College: Boston University Alumni
Posts: 10
Default

Based on your description, you should probably add additional error checks to your worksheet - specifically some type of record item count check.

I would do something like having a list of all the required entries with a comparison of the count of each item from that list with at least one entry in my source.

So, if you must have "Apples" and "Bananas", you have a 1 *(Countifs(range, "Apples")>0) and a 1*(countifs(range,"Bananas")>0). Add those fields together and it better equal 2, otherwise either apples or bananas are missing.

Then, if you really want that SUMIFS to indicate whether it's results areflawed, put some conditional formatting on it to make it red when that count isn't right. At that point, you could even get creative and have the conditional format show up as a specific color for the missing item. Yellow = bananas missing, red = apples missing, etc.
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 02:04 AM.


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