Thread for posting confusing functions

The Sandbox Software/Technology
1

Excel, SQL, or just code in general

Examples from the AO thread (started January 2011)

SpaceActuary:
{=SUM(($B5 = 'Loss Table'!$A$9:$A$8765) * ($Q5 = 'Loss Table'!$B$9:$B$8765) * (IF(TRIM($K5)="","0",TEXT($K5,"0")) = 'Loss Table'!$C$9:$C$8765) * (IF(TRIM($L5)="","0",TEXT($L5,"0")) = 'Loss Table'!$D$9:$D$8765) * (IF(TRIM($M5)="","0",TEXT($M5,"0")) = 'Loss Table'!$E$9:$E$8765) * (IF(TRIM($N5)="","0",TEXT($N5,"0")) = 'Loss Table'!$F$9:$F$8765) * (IF(TRIM($O5)="","0",TEXT($O5,"0")) = 'Loss Table'!$G$9:$G$8765) * (IF(TRIM($P5)="","0",TEXT($P5,"0")) = 'Loss Table'!$H$9:$H$8765) * ('Loss Table'!$I$9:$I$8765))}

Rake:
=1-(1-(SUMPRODUCT(PRODUCT(1-OFFSET(CSO_80_ANB,G2-1,IF(F2="M",0,2)+IF(I2="S",1,0),T2,1)*(H2*0.25+1)/1000)*1)-1)*(SUMPRODUCT(PRODUCT(1-OFFSET(CSO_80_ANB,L2-1,IF(K2="M",0,2)+IF(N2="S",1,0),T2,1)*(M2*0.25+1)/1000)*1)-1))/(1-(SUMPRODUCT(PRODUCT(1-OFFSET(CSO_80_ANB,G2-1,IF(F2="M",0,2)+IF(I2="S",1,0),T2-1,1)*(H2*0.25+1)/1000)*1)-1)*(SUMPRODUCT(PRODUCT(1-OFFSET(CSO_80_ANB,L2-1,IF(K2="M",0,2)+IF(N2="S",1,0),T2-1,1)*(M2*0.25+1)/1000)*1)-1))

Gedankenexperiment
SELECT IIf([LOB]="STD",[Amt Paid To Dt],[Amt Paid To Dt]*((1-0.995942407^(IIf(Int((IIf(IsNull([Close Date])=True,[Report Date],[Close Date])-DateAdd("d",[Elim Period],[Incurred Date]))*12/365.25)<1,1,Int((IIf(IsNull([Close Date])=True,[Report Date],[Close Date])-DateAdd("d",[Elim Period],[Incurred Date]))*12/365.25))))/0.004074124)*0.952380952^([Elim Period]/365.25)/(IIf(Int((IIf(IsNull([Close Date])=True,[Report Date],[Close Date])-DateAdd("d",[Elim Period],[Incurred Date]))*12/365.25)<1,1,Int((IIf(IsNull([Close Date])=True,[Report Date],[Close Date])-DateAdd("d",[Elim Period],[Incurred Date]))*12/365.25))))+[Reserve]*(0.95238095^(([Report Date]-[Incurred Date])/365)) AS PVPayRes

SirVLCIV
=IF($A$2<>3,0,IF(INDEX(CashBalanceSchedule,1,2+8*( $A$1-1))=1,MIN(AB25,AC25)*INDEX(CashBalanceBase,1,2+8*( $A$1-1))+MAX(AB25-AC25,0)*INDEX(CashBalanceExcess,1,2+8*($A$1-1)),0)+IF(INDEX(CashBalanceSchedule,1,2+8*($A$1-1))>1,MIN(AB25,AC25)*INDEX(CashBalanceSchedule2,MA TCH((IF(INDEX(CashBalanceSchedule,1,2+8*($A$1-1))=2,$B25,0)+IF(INDEX(CashBalanceSchedule,1,2+8*( $A$1-1))=3,$C25,0)+IF(INDEX(CashBalanceSchedule,1,2+8*( $A$1-1))=4,$B25+$C25)),INDEX(CashBalanceSchedule2,0,2+8 *($A$1-1)),-1),4+8*($A$1-1))+MAX(AB25-AC25,0)*INDEX(CashBalanceSchedule2,MATCH((IF(INDEX (CashBalanceSchedule,1,2+8*($A$1-1))=2,$B25,0)+IF(INDEX(CashBalanceSchedule,1,2+8*( $A$1-1))=3,$C25,0)+IF(INDEX(CashBalanceSchedule,1,2+8*( $A$1-1))=4,$B25+$C25)),INDEX(CashBalanceSchedule2,0,2+8 *($A$1-1)),-1),5+8*($A$1-1)),0))

2

I wonder if I can dig up the spreadsheet where I used the 117,640th root of a number in a real calculation.

3

There were nine pages (40 ppp) of the old AO thread. I’m not copying over all the examples…

A before and after from DyalDragon:
Before:
IF(P$2='FT Factors'!$G$11,'FT Factors'!$H$11,1)*IF(P$2='FT Factors'!$G$12,'FT Factors'!$H$12,1)*IF(P$2='FT Factors'!$G$13,'FT Factors'!$H$13,1)*IF(P$2='FT Factors'!$G$14,'FT Factors'!$H$14,1)*IF(P$2='FT Factors'!$G$15,'FT Factors'!$H$15,1)*IF(P$2='FT Factors'!$G$16,'FT Factors'!$H$16,1)*IF(P$2='FT Factors'!$G$17,'FT Factors'!$H$17,1)*IF(P$2='FT Factors'!$G$18,'FT Factors'!$H$18,1)*IF(P$2='FT Factors'!$G$19,'FT Factors'!$H$19,1)*IF(P$2='FT Factors'!$G$20,'FT Factors'!$H$20,1)*IF(P$2='FT Factors'!$G$21,'FT Factors'!$H$21,1)*IF(P$2='FT Factors'!$G$22,'FT Factors'!$H$22,1)*IF(P$2='FT Factors'!$G$23,'FT Factors'!$H$23,1)*IF(P$2='FT Factors'!$G$24,'FT Factors'!$H$24,1)*IF(P$2='FT Factors'!$G$25,'FT Factors'!$H$25,1)*IF(P$2='FT Factors'!$G$26,'FT Factors'!$H$26,1)

After:
{PRODUCT(IF(Factors!$O$33:$O$49=J$3,Factors!$Q$33: $Q$49,1))}

4

Rake:
{=INDEX($A$2:$A$22,SMALL(IF(MATCH($A$2:$A$22,$A$2: $A$22,0)=ROW($A$2:$A$22)-ROW($A$1),ROW($A$2:$A$22)-ROW($A$1),""),ROW(INDIRECT("1:"&SUM(N(MATCH($A$2:$ A$22,$A$2:$A$22,0)=ROW($A$2:$A$22)-ROW($A$1)))))),1)}

JMO:
=IF(ROW()-ROW($H$2)+COLUMN()-COLUMN($H$2)>3,"",SUM(OFFSET ($B$2,ROW()-ROW($H$2),ROW()-ROW($H$2),COLUMN()-COLUMN($H$2)+1,1)))

Gedankenexperiment:
=IF(F2>0,$F$1,IF(G2>0,$G$1,IF(H2>0, $H$1, IF(I2>0, $I$1, IF(J2>0, $J$1, IF(K2>0, $K$1, IF(L2>0, $L$1, IF(M2>0, $M$1, IF(N2>0, $N$1, IF(O2>0, $O$1, IF(P2>0, $P$1, IF(Q2>0, $Q$1, 0))))))))))))

5

You shared this…

http://204.232.242.165/actuarial_discussion_forum/showpost.php?p=5527350&postcount=177

6

I was such an amateur 10 years ago in complex formulas.