• 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

    Hui...

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

Triangle Calculation

Q

Qurat

New Member
Hello,
I am in trouble in excel formula making, can any one create the formula for making the loss triangle.
I want to set these total number on making the triangle like this with formula dragging, can anyone create the formula for making the triangle from total values.
I want to create the formula instead of one by one change cell value
 

Attachments

  • Book1.xlsx
    20.2 KB · Views: 17
A couple of options using array formulas. The entire array is given by a single formula; no dragging and relative references involved.
To post the workbook in a working state I have had to select the entire array and commit the formula with CSE; a process disliked by most Excel users!

For me, though, it was different. I am using a pre-release version of Excel with modern Dynamic Arrays. The formula
= IF(1+p>=u, total * LOOKUP(1+p, p+u, escalate), 0 )
is in cell E5; nowhere else. The output array simply fills because p (premium development index) and u (underwriting year index) are arrays and any formula that references any array will normally be an array itself. E28 also contains a formula closer to that offered by John Jairo.

What this does at a stroke is remove the direct cell referencing that is a concept fit only for the trash can and has plagued spreadsheet credibility ever since Dan Bricklin came up with the little wheeze as a means of ensuring that the developer/user did not have to suffer the tedium of defining their notation; at the same time, it removes the need for relative referencing since the objects referenced are no longer just single terms taken from a list/array.
 

Attachments

  • Book1 (PB).xlsx
    29 KB · Views: 9
John Jairo V said:
Hi, Qurat!

One option could be:
[E5] : =$D5*INDEX($D$20:E$20,1+MAX(,COLUMNS($D5:E5)-ROWS(E$5:E5)))

And drag it down and right. Blessings!
Click to expand...
Hi John,
If i want to apply lookup different values in this formula, what should i do for lookup criteria ?
Thanks in advance.
File is attached for reference.
 

Attachments

  • Book1.xlsx
    21.7 KB · Views: 8
T
Peter Bartholomew said:
A couple of options using array formulas. The entire array is given by a single formula; no dragging and relative references involved.
To post the workbook in a working state I have had to select the entire array and commit the formula with CSE; a process disliked by most Excel users!

For me, though, it was different. I am using a pre-release version of Excel with modern Dynamic Arrays. The formula
= IF(1+p>=u, total * LOOKUP(1+p, p+u, escalate), 0 )
is in cell E5; nowhere else. The output array simply fills because p (premium development index) and u (underwriting year index) are arrays and any formula that references any array will normally be an array itself. E28 also contains a formula closer to that offered by John Jairo.

What this does at a stroke is remove the direct cell referencing that is a concept fit only for the trash can and has plagued spreadsheet credibility ever since Dan Bricklin came up with the little wheeze as a means of ensuring that the developer/user did not have to suffer the tedium of defining their notation; at the same time, it removes the need for relative referencing since the objects referenced are no longer just single terms taken from a list/array.
Click to expand...
thank you peter for response, highly appreciated for help :)
 
You must log in or register to reply here.
Back
Top