• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Aggregate excess allocation


New Member
Hi there

I need help to allocate claims to either the insured or the insurer as per the attached file.
I have explained what I am trying to do in the attached file.


Peter Bartholomew

Well-Known Member
Very mysterious. I do not claim to have fully grasped the logic here. :confused:
Despite that, I have generated the numbers with alternative formulae that may be of some interest.
Firstly I converted the data to a table to bring it up to an Excel 2007 level and used that to create formulae that are more readable.

For anyone using the latest versions of Excel 365, I have addressed the OP question using the SCAN Lambda function helper. Accumulation formulae of the type arising here were notoriously difficult using dynamic arrays so the problem has allowed me to check out potential solutions. For anyone not using 365, neither the problem nor its solution are of relevance.