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

DW Simpson
Actuarial Jobs

Visit our site for the most up to date jobs for actuaries.

Actuarial Salary Surveys
Property & Casualty, Health, Life, Pension and Non-Tradtional Jobs.

Actuarial Meeting Schedule
Browse this year's meetings and which recruiters will attend.

Contact DW Simpson
Have a question?
Let's talk.
You'll be glad you did.


Reply
 
Thread Tools Display Modes
  #1  
Old 08-02-2017, 12:27 PM
hawk7820 hawk7820 is offline
CAS
 
Join Date: May 2017
Location: Greater Philadelphia
Studying for FM
College: B.S. Actuarial Science
Favorite beer: Victory
Posts: 9
Default Microsoft Excel Formula Question

Hi all,

I am compiling some data within an Excel file and I am struggling to find a formula to figure out what I am doing. My data is not too extensive, but it's not small enough that I could count manually.

Essentially, what I am trying to accomplish is to model the counts of different loss amounts for a series of claims. I want to see all that fall within each interval. For example, I want to return the number of claims that have loss amounts between $0 and $10K, $10K and $20K, etc... I tried using a COUNTIF() function but that didn't quite get what I wanted, or maybe I am just doing it incorrectly.

Does anybody know the best way to go about doing this? I'm merely an intern and do not have extensive Excel experience, though I do have a very basic background in Java, Access, and Excel, but I have not figured out a great way to find my desired result.

Thank you in advance!
__________________
P FM MLE C
Reply With Quote
  #2  
Old 08-02-2017, 12:37 PM
MountainHawk's Avatar
MountainHawk MountainHawk is offline
Member
CAS AAA
 
Join Date: Dec 2001
Location: Salem, MA
Studying for Nothing!!!!
College: Lehigh University Alum
Favorite beer: Yuengling
Posts: 64,796
Default

Column A = Data

Column B: Lower Bound
Column C: Upper Bound
Cell D1: =countifs(A:A,">="&B1,A:A,"<"&C1)
__________________

Play in the AO Prediction Game now!



1
Reply With Quote
  #3  
Old 08-02-2017, 12:37 PM
Sleeping Dragon Sleeping Dragon is offline
Member
 
Join Date: Jun 2003
Posts: 122
Default

If you have more than one condition you need to use COUNTIFS() instead of COUNTIF(). If you are using a very old version of Excel and the COUNTIFS() function if not available then you would need to use an array formula combined with COUNTIF().
Reply With Quote
  #4  
Old 08-02-2017, 12:44 PM
BruteForce's Avatar
BruteForce BruteForce is offline
Member
SOA AAA
 
Join Date: Apr 2013
Studying for More Money
Favorite beer: Wurzel Bier
Posts: 9,595
Default

Another way to do this would be to create a lookup table with the min of each row. Then you could add a column to your data using vlookup (with the last variable set to TRUE), looking to the lookup table. That should give you which bucket each line goes to. Then you can filter/sort by buckets.

If that doesn't make sense, I could build an example.
__________________
ASA

Quote:
Originally Posted by Actuary321 View Post
I would really hate to bring Pokémon to a gun fight.
Reply With Quote
  #5  
Old 08-02-2017, 01:43 PM
hawk7820 hawk7820 is offline
CAS
 
Join Date: May 2017
Location: Greater Philadelphia
Studying for FM
College: B.S. Actuarial Science
Favorite beer: Victory
Posts: 9
Default

Quote:
Originally Posted by MountainHawk View Post
Column A = Data

Column B: Lower Bound
Column C: Upper Bound
Cell D1: =countifs(A:A,">="&B1,A:A,"<"&C1)
This is exactly what I needed; I didn't know the best way to code it. Thank you!
__________________
P FM MLE C
Reply With Quote
  #6  
Old 08-02-2017, 01:50 PM
ALivelySedative's Avatar
ALivelySedative ALivelySedative is offline
Member
CAS
 
Join Date: Dec 2013
Location: Land of the Pine
College: UNC-Chapel Hill Alum
Favorite beer: Red Oak
Posts: 1,396
Default

MountainHawk's suggestion is still more appropriate, but you can also use the data analysis tool to create a 'histogram' and just remove the physical chart display. Just have to specify your data and define your 'bins' in a separate column somewhere.
__________________
1/P | 2/FM | 3F/MFE | LC | ST |4/C | 5 | 6 || 7 | 8 | 9
VEE: Econ, Fin, Stat
OC1, OC2
COP
Reply With Quote
  #7  
Old 08-02-2017, 02:06 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: 30,075
Default

The FREQUENCY function would do what you want, but it's an array function so... basically it just eliminates one of the columns in MH's setup.
__________________
.................................. ..............
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
  #8  
Old 08-02-2017, 02:19 PM
MountainHawk's Avatar
MountainHawk MountainHawk is offline
Member
CAS AAA
 
Join Date: Dec 2001
Location: Salem, MA
Studying for Nothing!!!!
College: Lehigh University Alum
Favorite beer: Yuengling
Posts: 64,796
Default

Quote:
Originally Posted by DyalDragon View Post
The FREQUENCY function would do what you want, but it's an array function so... basically it just eliminates one of the columns in MH's setup.
At the cost of having a god awful array function.
__________________

Play in the AO Prediction Game now!



1
Reply With Quote
  #9  
Old 08-02-2017, 02:23 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: 30,075
Default

Quote:
Originally Posted by MountainHawk View Post
At the cost of having a god awful array function.
Not such a high cost if you just want one-off results. If it's being built into a semi-permanent model, then everyone that touches the model in the future will hate you.
__________________
.................................. ..............
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
  #10  
Old 08-02-2017, 02:28 PM
MountainHawk's Avatar
MountainHawk MountainHawk is offline
Member
CAS AAA
 
Join Date: Dec 2001
Location: Salem, MA
Studying for Nothing!!!!
College: Lehigh University Alum
Favorite beer: Yuengling
Posts: 64,796
Default

Quote:
Originally Posted by DyalDragon View Post
Not such a high cost if you just want one-off results. If it's being built into a semi-permanent model, then everyone that touches the model in the future will hate you.
This is your spreadsheet:


This is your spreadsheet on array functions:




Array Functions --- not even once.
__________________

Play in the AO Prediction Game now!



1
Reply With Quote
Reply

Tags
data, excel, finance, intern

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 07:50 AM.


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.23676 seconds with 9 queries