calc - #value when computing vector percentiles?

[DiesNuts]

The range below basically say "cut off everything before $B$8 and everything after $B$9$ and for the remaining, sum columns I,J where the values are distributed over multiple sheets (3 in this case, “mw_rep0”, “mw_rep1”, “mw_rep2”).

OFFSET($mw_rep0.I:I,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep0.J:J,$B$8,0,$B$9-$B$8,1),OFFSET($mw_rep1.I:I,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep1.J:J,$B$8,0,$B$9-$B$8,1),OFFSET($mw_rep2.I:I,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep2.J:J,$B$8,0,$B$9-$B$8,1)

I will refer to this as range1 (though I have to copy-paste it to use it because apparently, you cannot create a name for it).
So =AVERAGE(range1) entered with CTRL+SHIFT+ENTER will calculate me the average sum.

Now I want percentiles. To that end, I modify range1 such that the terms for each sheet are separeted by ~, rather than by ,:

e.g: =PERCENTILE(OFFSET($mw_rep0.I:I,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep0.J:J,$B$8,0,$B$9-$B$8,1)~OFFSET($mw_rep1.I:I,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep1.J:J,$B$8,0,$B$9-$B$8,1)~OFFSET($mw_rep2.I:I,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep2.J:J,$B$8,0,$B$9-$B$8,1),0.9)

when I enter this with CTRL+SHIFT+ENTER, I get #VALUE!.

Which I find strange because I have an even uglier range summing up columns B,C,D,E, for which this works:

OFFSET($mw_rep0.B:B,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep0.C:C,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep0.D:D,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep0.E:E,$B$8,0,$B$9-$B$8,1),OFFSET($mw_rep1.B:B,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep1.C:C,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep1.D:D,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep1.E:E,$B$8,0,$B$9-$B$8,1),OFFSET($mw_rep2.B:B,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep2.C:C,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep2.D:D,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep2.E:E,$B$8,0,$B$9-$B$8,1)

I will refer to this as range0.
Here, both, =AVERAGE(range0) and percentiles (for which we again modify range0 such that the individual sheets’ terms are separated by tilde, rather than comma) work.

e.g. =PERCENTILE(OFFSET($mw_rep0.B:B,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep0.C:C,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep0.D:D,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep0.E:E,$B$8,0,$B$9-$B$8,1)~OFFSET($mw_rep1.B:B,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep1.C:C,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep1.D:D,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep1.E:E,$B$8,0,$B$9-$B$8,1)~OFFSET($mw_rep2.B:B,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep2.C:C,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep2.D:D,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep2.E:E,$B$8,0,$B$9-$B$8,1),0.9)

So how do I get the percentiles working in the “easier” case?

[Lupp]

I am afraid you will need to give simplified examples if you want to get an answer. The only alternative might be to attach “the real thing”. You cannot expect someone to fillet your monsters for you.
By the way: You seem to have split equally structured data to different sheets. This is considered bad design by experienced users, and is known to cause problems again and again.
Did you study the OpenFormula specifications concerning ‘Reference List’ and the ‘Infix Operator’ ~ ?

[DiesNuts]

@Lupp Well, since I cannot edit my post, let me put a link to such a file here: https://drive.google.com/open?id=0B_isCgk8unrBam4tWU1xb3dEaXc

The problem is on the “middleware” sheet (Cells F12, G12). As for the “splitting” part, I don’t have much choice. The data comes from individual files.