calc - #value when computing vector percentiles?

asked 2016-12-07 13:56:25 +0100

DiesNuts gravatar image

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 ... (more)

edit retag flag offensive close merge delete

Comments

1

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' ~ ?

Lupp gravatar imageLupp ( 2016-12-07 16:28:54 +0100 )edit

@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_i...

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.

DiesNuts gravatar imageDiesNuts ( 2016-12-08 11:24:53 +0100 )edit