質問する

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_<take from A column>'.$B$5
='myStuff_<take from A column>'.$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 ... (more)

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)