Actuarial Outpost Microsoft Excel Formula Question
 Register Blogs Wiki FAQ Calendar Search Today's Posts Mark Forums Read
 FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

#1
08-02-2017, 12:27 PM
 hawk7820 CAS Join Date: May 2017 Location: Greater Philadelphia Studying for FM, MFE College: Saint Joseph's Favorite beer: Einstok Posts: 10
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.

__________________
P FM MLE C
#2
08-02-2017, 12:37 PM
 MountainHawk Member CAS AAA Join Date: Dec 2001 Location: Salem, MA Studying for Nothing!!!! College: Lehigh University Alum Favorite beer: Yuengling Posts: 64,850

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
#3
08-02-2017, 12:37 PM
 Sleeping Dragon Member Join Date: Jun 2003 Posts: 128

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
08-02-2017, 12:44 PM
 BruteForce Member SOA AAA Join Date: Apr 2013 Studying for More Money Favorite beer: Wurzel Bier Posts: 10,802

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 I would really hate to bring Pokémon to a gun fight.
#5
08-02-2017, 01:43 PM
 hawk7820 CAS Join Date: May 2017 Location: Greater Philadelphia Studying for FM, MFE College: Saint Joseph's Favorite beer: Einstok Posts: 10

Quote:
 Originally Posted by MountainHawk 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
#6
08-02-2017, 01:50 PM
 ALivelySedative Member CAS Join Date: Dec 2013 Location: Land of the Pine College: UNC-Chapel Hill Alum Favorite beer: Red Oak Posts: 2,412

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.
#7
08-02-2017, 02:06 PM
 DyalDragon Member SOA Join Date: Apr 2009 Location: Here Studying for the hell of it... College: AASU Favorite beer: This one... Posts: 31,296

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 Maybe a better statement is that I enjoyed having experienced it both ways?
#8
08-02-2017, 02:19 PM
 MountainHawk Member CAS AAA Join Date: Dec 2001 Location: Salem, MA Studying for Nothing!!!! College: Lehigh University Alum Favorite beer: Yuengling Posts: 64,850

Quote:
 Originally Posted by DyalDragon 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
#9
08-02-2017, 02:23 PM
 DyalDragon Member SOA Join Date: Apr 2009 Location: Here Studying for the hell of it... College: AASU Favorite beer: This one... Posts: 31,296

Quote:
 Originally Posted by MountainHawk 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 Maybe a better statement is that I enjoyed having experienced it both ways?
#10
08-02-2017, 02:28 PM
 MountainHawk Member CAS AAA Join Date: Dec 2001 Location: Salem, MA Studying for Nothing!!!! College: Lehigh University Alum Favorite beer: Yuengling Posts: 64,850

Quote:
 Originally Posted by DyalDragon 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.

Array Functions --- not even once.
__________________

Play in the AO Prediction Game now!

1

 Tags data, excel, finance, intern

 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 08:07 PM.

 -- Default Style - Fluid Width ---- Default Style - Fixed Width ---- Old Default Style ---- Easy on the eyes ---- Smooth Darkness ---- Chestnut ---- Apple-ish Style ---- If Apples were blue ---- If Apples were green ---- If Apples were purple ---- Halloween 2007 ---- B&W ---- Halloween ---- AO Christmas Theme ---- Turkey Day Theme ---- AO 2007 beta ---- 4th Of July Contact Us - Actuarial Outpost - Archive - Privacy Statement - Top