質問する

# DiesNuts's profile - activity

 2019-03-21 01:18:58 +0200 バッジを受け取った ● 有名な質問 (source) 2019-03-21 01:18:58 +0200 バッジを受け取った ● 卓越した質問 (source) 2018-11-15 20:22:56 +0200 バッジを受け取った ● 卓越した質問 (source) 2018-11-15 20:22:56 +0200 バッジを受け取った ● 有名な質問 (source) 2018-11-15 20:22:56 +0200 バッジを受け取った ● 人気の質問 (source) 2018-10-02 05:29:04 +0200 バッジを受け取った ● 人気の質問 (source) 2018-06-10 15:19:13 +0200 バッジを受け取った ● 有名な質問 (source) 2018-04-17 21:08:54 +0200 バッジを受け取った ● 有名な質問 (source) 2018-02-14 17:13:54 +0200 バッジを受け取った ● 有名な質問 (source) 2018-01-24 08:46:30 +0200 バッジを受け取った ● 有名な質問 (source) 2018-01-24 08:46:30 +0200 バッジを受け取った ● 卓越した質問 (source) 2017-12-10 17:01:36 +0200 バッジを受け取った ● 有名な質問 (source) 2017-11-09 21:08:56 +0200 バッジを受け取った ● 人気の質問 (source) 2017-08-06 12:15:07 +0200 バッジを受け取った ● 卓越した質問 (source) 2017-08-06 12:15:07 +0200 バッジを受け取った ● 人気の質問 (source) 2017-08-06 12:15:07 +0200 バッジを受け取った ● 有名な質問 (source) 2017-07-27 15:08:49 +0200 バッジを受け取った ● 有名な質問 (source) 2017-06-28 20:40:28 +0200 バッジを受け取った ● 卓越した質問 (source) 2017-04-22 00:01:12 +0200 バッジを受け取った ● 卓越した質問 (source) 2017-04-21 08:00:08 +0200 バッジを受け取った ● 有名な質問 (source) 2017-04-21 08:00:08 +0200 バッジを受け取った ● 卓越した質問 (source) 2017-04-10 18:27:28 +0200 バッジを受け取った ● 人気の質問 (source) 2017-04-07 01:18:58 +0200 バッジを受け取った ● 卓越した質問 (source) 2017-04-05 13:24:39 +0200 バッジを受け取った ● 有名な質問 (source) 2017-01-19 16:13:19 +0200 ベストアンサーマーク average of sums Let's say I have some data on columns B through E that I want to average as follows: for each row: sum the four columns then take the average such sum Do I have to get another column for the sum and then take the average of that column or is there a more direct approach? Because pulling =SUM(...) down 500'000 rows does take a while. 2017-01-19 16:13:11 +0200 ベストアンサーマーク calc - calculate with custom timestamps Let's say I have cells with custom timestamps, e.g. 2016-10-18T14:14:09.831Z-GET 2016-10-18T15:05:09.854Z-GET  And I want to calculate the time elapsed in-between them (in this case, roughly 3060seconds). How do I do that? 2017-01-19 16:12:11 +0200 ベストアンサーマーク calc - find value in subtable Let's say I have a sheet and somewhere on it a table like  AAAA 8 16 32 48 64 50 x08050 x16050 x32050 x48050 x64050 150 x08150 x16150 x32150 x48150 x64150 250 x08250 x16250 x32250 x48250 x64250 350 x08350 x16350 x32350 x48350 x64350 450 x08450 x16450 x32450 x48450 x64450 550 x08550 x16550 x32550 x48550 x64550 650 x08650 x16650 x32650 x48650 x64650  where xYYZZZ represents the value in the column for YY and the row for ZZZ How do I perform a lookup the kind of "Find me AAAA(YY,ZZ)"? E.g. let's assume AAAA is at I32 then x64650 would be at N38. and say I want to lookup x48550 I'm looking for a way to say find me the value in J32:N38 where the value on the same row in column I is 550 and the value on the same column in row 32 is 48. 2017-01-19 16:12:05 +0200 ベストアンサーマーク calc - vary column in indirect Assume sheets that have some values on columns B-K. Each sheet has R2=AVERAGE(INDIRECT("b"&$O$2):INDIRECT("b"&$O$3)) R3=STDEV(INDIRECT("b"&$O$2):INDIRECT("b"&$O$3))  for, say, O2=1436 O3=10260  How can I write the indirect s.t. I can drag it right to sumarise the other columns? i.e. S2=AVERAGE(INDIRECT("c"&$O$2):INDIRECT("c"&$O$3)) S3=STDEV(INDIRECT("c"&$O$2):INDIRECT("c"&$O$3)) T2=AVERAGE(INDIRECT("d"&$O$2):INDIRECT("d"&$O$3)) T3=STDEV(INDIRECT("d"&$O$2):INDIRECT("d"&$O$3))  2017-01-19 16:12:03 +0200 ベストアンサーマーク calc - copy row to column Say I have some data in sheet Sheet_A on R2:AA2 I now want to reference that data in Sheet_B in B2:B10 How do I do that? If I write  Sheet_B.B2 = Sheet_A.R$2  And then drag down, it will result in  Sheet_B.B3 = Sheet_A.R$2  But I want  Sheet_B.B3 = Sheet_A.S$2  2017-01-19 16:12:00 +0200 ベストアンサーマーク calc - filename from cell Let's say I have sheets "myStuff_10", "myStuff_30", ... imported on the A column, I have 10 30 50 70 ...  and in the C column, I want to have ='myStuff_10'.$B$5 ='myStuff_30'.$B$5 ...  How can I take that value from the A column, though? I.e. ='myStuff_'.$B$5 ='myStuff_'.$B$5 ...  something like (doesn't work, ERR:501):  =INDIRECT("myStuff_"&A3).$B$5  such that I can drag it down rather than manually adjust a thousand cells? 2016-12-14 11:26:30 +0200 バッジを受け取った ● 卓越した質問 (source) 2016-12-14 09:37:41 +0200 コメント付き回答 calc - using tuples/vectors @Lupp I would appreciate it if you could share that BASIC version because I cannot upgrade to 5.2 (tried, it got me W: Failed to fetch http://ppa.launchpad.net/libreoffice/libreoffice-5-2/ubuntu/dists/wily/main/binary-amd64/Packages 404 Not Found W: Failed to fetch http://ppa.launchpad.net/libreoffice/libreoffice-5-2/ubuntu/dists/wily/main/binary-i386/Packages 404 Not Found 2016-12-13 16:37:24 +0200 コメント付き回答 calc - using tuples/vectors That looks like what I'm looking for ... but my libre office calc doesn't know any TEXTJOIN function. 2016-12-13 14:17:40 +0200 コメント付き回答 calc - using tuples/vectors it's not a particularly hard macro, I just have no idea how to code it in calc. 2016-12-13 14:16:14 +0200 コメント付き回答 calc - using tuples/vectors @Lupp thank you, I am aware of the OFFSET and the INDIRECT. What I NEED, though, is an agreeable representation so I can export the table and include it into a latex document. So as that's not supported, I'm looking for a macro that will produce that for me. E.g. let's call that macro vec. so =vec(A1) should produce ="("&A1&")", =vec(A1:A3) should produce ="("&A1&","&A2&","&A3&")", =vec(A1,X5:X99,Z3)should result in="("&A1&","&"X5"&","&X6&...&x99&","&Z3&")" 2016-12-13 14:12:35 +0200 バッジを受け取った ● 人気の質問 (source) 2016-12-13 11:59:21 +0200 コメント付き回答 calc - using tuples/vectors So ... what you're saying is I need to write B1="("&A1&","&A2&","&A3&")" and then C1=AVERAGE(A1:A3). You wouldn't happen to know how to write a macro to which I can pass a variable number of arguments (single cells or cell ranges) and that will construct me the obnoxious ="("&X1&","&Y4002&","&Z19&"," & ... & ")" formula? 2016-12-13 11:06:56 +0200 質問をする calc - using tuples/vectors Let's say I have values A1 = 3 A2 = 5 A3 = 17  How do I get calc to display a vector containing these three values in cell B1? I.e: B1= (3,5,17)  And can I then just - for example - C1=AVERAGE(B1)  to get C1 = 8.33333  ? 2016-12-09 09:20:13 +0200 バッジを受け取った ● 卓越した質問 (source) 2016-12-09 08:40:04 +0200 コメント付き回答 calc - copy formulae without adjusting nope, doesn't work. it still adds the file references. 2016-12-08 11:30:42 +0200 コメント付き回答 calc - copy formulae without adjusting This actually works. Did not expect that. Thank you. 2016-12-08 11:28:14 +0200 コメント付き回答 calc - copy formulae without adjusting I am not familiar with the INDEX function. how would you rewrite =AVERAGE(OFFSET($mw_rep0.E:E,$B$8,0,$B$9-$B$8,1)~OFFSET($mw_rep1.E:E,$B$8,0,$B$9-$B$8,1)~OFFSET($mw_rep2.E:E,$B$8,0,$B$9-$B$8,1)) using INDEX? 2016-12-08 11:24:53 +0200 コメント付き質問 calc - #value when computing vector percentiles? @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. 2016-12-07 18:19:31 +0200 バッジを受け取った ● 人気の質問 (source) 2016-12-07 15:05:02 +0200 質問をする calc - copy formulae without adjusting So I have prepared a summary sheet with some really nasty formulae that reference other sheets in the same file. In their simplest form, they are of the form =AVERAGE(OFFSET($mw_rep0.E:E,$B$8,0,$B$9-$B$8,1)~OFFSET($mw_rep1.E:E,$B$8,0,$B$9-$B$8,1)~OFFSET($mw_rep2.E:E,$B$8,0,$B$9-$B$8,1))  In the uglier versions, they come as =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)  and need to be entered with CTRL+SHIFT+ENTER. As I have other files that need the same kind of summary but for THEIR sheets, I have named the sheets in these files accordingly. So I CTRL+A, CTRL+C, switch to the other document and CTRL+V. Works "great", EXCEPT all the sheet references have been prefixed by the file name (i.e. $mw_rep0 becomes 'file:///home/.../myFirstDocument.ods'#$mw_rep0), which in turn results in most cells displaying Err:502 instead of the calculated values from the new sheet. How do I copy the sheet WITHOUT doing that transformation? I can't copy-paste each cell's formula one-by-one into all the files that need them because I kind of need to get done before I die of old age. 2016-12-07 13:56:25 +0200 質問をする calc - #value when computing vector percentiles? 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 ... 2016-12-06 12:06:57 +0200 質問をする calc - CALCulate mathematical formulae Say I have three parameters: m (in cell B2) lambda (in cell B7) mu (in cell B8)  where I can easily calculate rho = lambda/(m*mu) (in cell B10)  I now want to calculate p0 = 1/(1 + (m * rho)^m/(m!*(1-rho)) + sum[n=1 to m-1]( (m*rho)^n / n!) ) How do I enter the sum? (1/(1+(B2 * (B7/(B2*B8)))^B2/(FACT(B2) * (1-(B7/(B2*B8)))) + ???)) note: I'm generating these formulae in bash so I don't need to use named cells and can re-use the generated formulae by copying the column and changing the parameters for your convenience: lambda="B7" mu="B8" m="B2" rho="($lambda/($m*$mu))" echo -e "rho =\n\t$rho" p0="(1/(1+($m * $rho)^$m/(FACT($m) * (1-$rho)) + ???))" echo -e "p0 =\n\t\$p0"  2016-12-02 13:10:38 +0200 バッジを受け取った ● 人気の質問 (source)