Peter Bartholomew
WellKnown Member
A bit more work yet but essentially there!
Attachments

16.7 KB Views: 7
I will be doing a FF post on it in due course.Hi Hui,
Perhaps an idea for formula forensics on the blog?
=SUM(IFERROR(EXP(LN(IF(F$6MMULT(N(ROW(_r1)>=TRANSPOSE(ROW(_r1))),N(+_r1))<_r,F$6MMULT(N(ROW(_r1)>=TRANSPOSE(ROW(_r1))),N(+_r1)),_r))),)*$D10:$D31)/F$6
Hi John,=IFERROR((SUM(IFERROR(EXP(LN(IF(SUM($E6:F6)MMULT(N(ROW(_r1)>=TRANSPOSE(ROW(_r1))),N(+_r1))<_r,SUM($E6:F6)MMULT(N(ROW(_r1)>=TRANSPOSE(ROW(_r1))),N(+_r1)),_r))),)*$D10:$D31)SUM(N(+$E6:E6)*N(+$E38:E38)))/F$6,)
I will be NarayanHi ,
You should write a Formula Forensics post on how you develop such formulae !
Narayan
Peter@Hui
It took me a long time to realise that named formulas were just that, formulas and not variables. Then Charles Williams's observations that named formulas are not evaluated unless they are used within a cell suddenly made sense.
Today's effort would have gone much faster if I hadn't changed strategies midstream. I was tempted to use matrix accumulation and differencing operators but then decided that SUMIFS would be more efficient despite doubling the calculation to work with start and end of period cases.
Peter
Hi ,I will be Narayan
Just a bit busy at work at present
This will also take a bit more thinking than the usual FF!
Someday we might need a debate as to what constitutes 'simplicity'. For me 'simplicity' lies in capturing the structure that underpins the problem domain and carries meaning step by step towards a solution....there was a simpler approach...