Within there are a bunch of =SUMIFS calc’s. However, the formula is slightly different for the company ABC claims than it is for the company XYZ claims.
Right now the formula is …
=IF(Company=“ABC”
,SUMIFS(claimsdata, First_criteria_range, “Potato”, Second_criteria_range, “42”, Third_criteria_range, “Frank said”)
,SUMIFS(claimsdata, First_criteria_range, “Potato”, Second_criteria_range, “42”)
…as you can see, claims for ABC have three criteria, but claims for XYZ only have the first two.
Company is a switch that’s flipped.
Is there a good or better way to write this code? I was thinking of creating a big range where all of the ABC claims are summed up and another range where all of the XYZ claims are summed up and then use an =IF(Company=“ABC”,grab value from ABC range,grab value from XYZ range)…only because it gets these big ass expressions out of the =IF formula, but that can’t be very efficient and will bog down the spreadsheet even more than it already is. It already suffers from being so big as to require a manual calculation.
Follow-up questions: Is there a better tool than Excel to create claims triangles? The only tools I have right now are Excel, SQL, & Access.
I’d typically make more use of a single SUMIFS formula that includes some criteria for which a group’s criteria might simply be any value “<>”. So like in your example you could put the IF statement embedded in your SUMIFS such that the criteria is “<>” if company is XYZ but is “Frank said” if ABC.
In fact if the data is all in one nice table I’d probably go to great lengths of embedding stuff into the single SUMIFS before embedding a SUMIFS into other formulas, unless it’s simple like IFERROR.
I like using * or $ for wildcards, they’re a little more recognizable than <>
I don’t think there’s a “better tool”. Excel is reasonably efficient and very readable, especially to actuaries.
I don’t know if sure which of your options is most efficient. I would think the “if on the outside” is the most efficient take, but again, you’re probably missing something more essential than that. (like volatile functions)
I think it’s either as fast or slightly faster since I think excel will only process the second SUMIFS if the first was negative, mostly speculation though
In Excel can you just use * or $ in place of “<>” then?
I’d make two different tabs if the formulae are that different. Easier to document that way.
Failing that, I’d create a separate column in your data for the third criteria range, the value of which is “Frank said” for all non-Company-ABC rows.
Follow-up Q’s:
A long time ago, in a company far, far away… ♫BAAAAAAAAA♫…
All the claim triangles were made, analyzed, and completed in various ways, using APL.
What about two separate workbooks? Is that possible?
Is the underlying data separate/separable?
I don’t use a whole lot of worksheets that are linked to Access/SQL data sources, so I don’t have experience that says for sure if it helps, but if your data source is a connection, use your connection to slim down the data you bring in.
If you are old school and have the data in a sheet in your workbook, and data is separate, then slimming down the data should help, and you have one formula for each workbook.
I prefer sticking to one worksheet and one workbook when possible. Splitting things out generally results in inefficiencies and nonstandard versions and limits to what you can do with the output.
Admittedly would solve your size problem, but I’m still not convinced that you are not causing it yourself some other way. Could you explain more how the file works generally???
Yep, having one workbook is preferable. However, if size forces you to split, standardization of output, including identifiers that specify source, allows for combination of results.
For example, I had multiple (30-40) projections of 1000 scenarios x 600 time steps x 40ish data elements. Data had to be conveyed to end user in Excel, along with summaries that included simple computational work on data. All that data wasn’t going into 1 workbook. It was almost unreasonable for one workbook for each projection. I built the standardized summary formulas, and had a macro that brought in the data, computed, saved the workbook under projection name, for each projection. Along the way it took the summary results and saved them in a summary file.
The only big data Excel file I am using currently uses pivot tables from a SQL connection. Seems quick when changing formulas, etc, but refreshing SQL connection takes a bit.
Once upon a time when I was EL, I inherited one of our darling models. It had 5 big fat buttons you had to push over the course of 8 hours. The various inputs and outputs were spread out across a 20 workbooks, using around 10 gigs of space in total. The macros would open and close them and copy paste over data, and paste down unreadable formulas, calculate, and save them as values. It crashed all the time.
I said “these formulas are unreadable garbage”.
My boss immediately IMed the Manager who had created the model “Sredni says your formulas are unreadable garbage.”
She responded “He can try to take them apart, but it’s just going to take even longer to run.”
…I spent a few days working on it. Afterwards it was a 10 meg file that took 5 minutes to run.
Lesson: You can be a good actuary but a terrible developer.
Once upon a time I had a chance to watch from afar as a few actuarial processes (macro-laded workbooks and databases) were transitioned over to IT control. The goal was ostensibly to minimize key-person risk – IT had a broader pool of technical talent, so they would be better able to work around things like the process owner transferring or leaving.
The IT people almost uniformly loathed the code they were handed – undocumented, inefficient, completely devoid of anything approaching best practices, etc. The transitions usually took a lot longer than expected, since not only did the IT team need to learn the process, they also had to rebuild it into something that vaguely met their standards.
