

FlashChat  Actuarial Discussion  Preliminary Exams  CAS/SOA Exams  Cyberchat  Around the World  Suggestions 

Thread Tools  Display Modes 
#1




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!
__________________

#3




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

#4




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 
#6




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.
__________________
COP 
#7




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.
__________________
.................................. .............. 
#9




Not such a high cost if you just want oneoff results. If it's being built into a semipermanent model, then everyone that touches the model in the future will hate you.
__________________
.................................. .............. 
#10




Quote:
This is your spreadsheet on array functions: Array Functions  not even once. 
Tags 
data, excel, finance, intern 
Thread Tools  
Display Modes  

