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

Browse Open Actuarial Jobs

Life  Health  Casualty  Pension  Entry Level  All Jobs  Salaries


Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 06-29-2018, 06:00 PM
rhoucag rhoucag is offline
Member
 
Join Date: Oct 2001
Posts: 274
Default Excel SUMIF - get to return error if missing

I want to use SUMIF in Excel because I'd like to combine certain combinations. For example - Company A reserve plus Company B reserve. But I want the formula to return an error if either of the two are missing as both are required. Any thoughts other than using another formula ?
Reply With Quote
  #2  
Old 06-29-2018, 06:05 PM
DyalDragon's Avatar
DyalDragon DyalDragon is offline
Member
SOA
 
Join Date: Apr 2009
Location: Here
Studying for the hell of it...
College: AASU
Favorite beer: This one...
Posts: 31,530
Default

Quote:
Originally Posted by rhoucag View Post
I want to use SUMIF in Excel because I'd like to combine certain combinations. For example - Company A reserve plus Company B reserve. But I want the formula to return an error if either of the two are missing as both are required. Any thoughts other than using another formula ?
Example?


I can think of a few ways, but are you just summing up a particular month? Is it columns of data or just two values? Do you want it to update manually or automatically whenever the time periods change? etc, etc...
__________________
PRELIMS VEE FAP FAP FAP FAP FAP FAP FAP FAP

Quote:
Originally Posted by Androzani Major View Post
Maybe a better statement is that I enjoyed having experienced it both ways?
Reply With Quote
  #3  
Old 06-29-2018, 06:17 PM
Dr T Non-Fan Dr T Non-Fan is online now
Member
SOA AAA
 
Join Date: Sep 2001
Location: Just outside of Nowhere
Posts: 93,198
Default

What would be the reason that one of the two would be missing? Is that an actual error that requires further looking up? If so, should do that separately.
__________________
"Facebook is a toilet." -- LWTwJO
Reply With Quote
  #4  
Old 06-29-2018, 06:19 PM
DyalDragon's Avatar
DyalDragon DyalDragon is offline
Member
SOA
 
Join Date: Apr 2009
Location: Here
Studying for the hell of it...
College: AASU
Favorite beer: This one...
Posts: 31,530
Default

Quote:
Originally Posted by Dr T Non-Fan View Post
What would be the reason that one of the two would be missing? Is that an actual error that requires further looking up? If so, should do that separately.
I assumed info obtained from two different sources, but am curious as well.
__________________
PRELIMS VEE FAP FAP FAP FAP FAP FAP FAP FAP

Quote:
Originally Posted by Androzani Major View Post
Maybe a better statement is that I enjoyed having experienced it both ways?
Reply With Quote
  #5  
Old 06-29-2018, 06:29 PM
Childish Gambino's Avatar
Childish Gambino Childish Gambino is online now
Member
SOA
 
Join Date: Jul 2014
Posts: 25,650
Default

if(sumproduct(--(isblank(A:A)))>0,"error","it's all good fam")
__________________
When fascism comes to America, it will be wrapped in the US Flag and carrying a Cross.
Reply With Quote
  #6  
Old 06-29-2018, 06:30 PM
DeepPurple's Avatar
DeepPurple DeepPurple is offline
Member
 
Join Date: Jun 2004
Posts: 4,210
Default

Sumproduct becomes more useful than sumif when you want to start customizing the calculation.



Your original post wasn't clear on exactly what you meant. If you are summing up two ranges, just sum them. What is the condition you want to meet with the If part?


This works if you want to force an error when a field is 0 or missing

=sumproduct(range, range, 1/range).

If that doesn't give you what you need, post some ficticious data that demonstrates the input and your desired output.
__________________
Come on. Let's go space truckin'. Come on!
Reply With Quote
  #7  
Old 06-29-2018, 07:40 PM
rhoucag rhoucag is offline
Member
 
Join Date: Oct 2001
Posts: 274
Default

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).

Last edited by rhoucag; 06-29-2018 at 07:45 PM..
Reply With Quote
  #8  
Old 06-29-2018, 07:49 PM
Dr T Non-Fan Dr T Non-Fan is online now
Member
SOA AAA
 
Join Date: Sep 2001
Location: Just outside of Nowhere
Posts: 93,198
Default

Quote:
Originally Posted by DeepPurple View Post
Sumproduct becomes is more useful than sumif. Period.
Fixed.
__________________
"Facebook is a toilet." -- LWTwJO
Reply With Quote
  #9  
Old 06-29-2018, 07:52 PM
rhoucag rhoucag is offline
Member
 
Join Date: Oct 2001
Posts: 274
Default

Quote:
Originally Posted by Dr T Non-Fan View Post
Fixed.
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.
Reply With Quote
  #10  
Old 06-29-2018, 09:41 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,616
Default

Could one use VBA (or something) to check that all required data is present?
__________________
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
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 06:00 PM.


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.24518 seconds with 11 queries