質問する

migueldealmeida's profile - activity

2019-04-30 10:06:40 +0200 コメント付き質問 How to Reference the value of the filter in autofilter?

Filter the table+printing is sufficient, yes. However, I would need the first row to display the "data belonging to...",

2019-04-30 10:06:40 +0200 バッジを受け取った  解説者
2019-04-29 21:09:06 +0200 バッジを受け取った  人気の質問 (source)
2019-04-29 18:21:01 +0200 編集された質問 How to Reference the value of the filter in autofilter?

How to Reference the filter in autofilter Consider the attached example Reference data in autofilter.ods Is there a wa

2019-04-29 17:16:12 +0200 質問をする How to Reference the value of the filter in autofilter?

How to Reference the filter in autofilter Consider the attached example Reference data in autofilter.ods Is there a wa

2019-04-28 14:06:21 +0200 質問をする How to hide sheet(s) when printing?

How to hide sheet(s) when printing? Say you have 3 sheets, and you only want 1 to be printed. Is there a simple way to d

2019-04-28 14:04:33 +0200 バッジを受け取った  熱心な人
2019-04-27 17:32:54 +0200 質問をする How to display nothing instead of "(empty)" in Pivot table?

How to display nothing instead of "(empty)" in Pivot table? I have a pivot table that takes a sheet with a lot of expens

2019-04-09 18:58:17 +0200 バッジを受け取った  人気の質問 (source)
2019-04-04 10:13:13 +0200 編集された質問 How to create a Sub-report with only some columns and some rows in Calc

How to create a Sub-report with only some columns and some rows in Calc Consider you have a list of sales, with Date,Cat

2019-04-04 10:11:19 +0200 質問をする How to create a Sub-report with only some columns and some rows in Calc

How to create a Sub-report with only some columns and some rows in Calc Consider you have a list of sales, with Date,Cat

2019-03-29 21:01:55 +0200 バッジを受け取った  有名な質問 (source)
2019-01-28 10:39:24 +0200 ベストアンサーマーク Is there a way to refer to a Pivot Table's total cell?

When a pivot table is created, there is a "total" at the bottom of the table. However, since the pivot table might be dynamic (i.e. number of rows can change if you change the underlying data), if you refer to that cell by it's absolute position (e.g. "'Sheet1.B33"), things might break if the pivot table changes the number of rows.

Is it possible to reference this cell in a way that does not break if the pivot table changes?

2019-01-27 03:18:14 +0200 バッジを受け取った  人気の質問 (source)
2019-01-26 20:19:09 +0200 質問をする Is there a way to refer to a Pivot Table's total cell?

Is there a way to refer to a Pivot Table's total cell? When a pivot table is created, there is a "total" at the bottom o

2019-01-26 20:19:07 +0200 質問をする Is there a way to refer to a Pivot Table's total cell?

Is there a way to refer to a Pivot Table's total cell? When a pivot table is created, there is a "total" at the bottom o

2018-02-18 15:47:20 +0200 コメント付き回答 How to find where in the interval a number is

This works really well, thanks!

2018-02-18 15:39:30 +0200 コメント付き質問 How to find where in the interval a number is

I agree. The real world example actually is organised in steps of one hundredth (it is a cost and they don't define less

2018-02-18 15:37:48 +0200 ベストアンサーマーク How to find where in the interval a number is

Imagine we have 3 columns, the first two define a range (e.g. min and max weight) and the third the value (e.g. price that item costs if it has that weight). Example

What formula should be used to achieve this?

From this site I was able to build something that returns the number of the column, but I would now need to get column C. To get the column number I use =IF(SUMPRODUCT(--(A1:A39<=F2)(B1:B39>=F2))=1,SUMPRODUCT(--(A1:A39<=F2)(B1:B39>=F2),ROW(A1:A39))-1,"Not Found")

here is an example file: https://drive.google.com/file/d/1yJGc...

Questions:

  1. How would I retrieve the corresponding value in C?
  2. Is there an easier way to do this?
2018-02-17 11:58:09 +0200 バッジを受け取った  人気の質問 (source)
2018-02-16 20:05:13 +0200 バッジを受け取った  編集者 (source)
2018-02-16 20:05:13 +0200 編集された質問 How to find where in the interval a number is

How to find where in the interval a number is Imagine we have 3 columns, the first two define a range (e.g. min and max

2018-02-16 20:02:14 +0200 編集された質問 How to find where in the interval a number is

How to find where in the interval a number is Imagine we have 3 columns, the first two define a range (e.g. min and max

2018-02-16 19:58:39 +0200 質問をする How to find where in the interval a number is

How to find where in the interval a number is Imagine we have 3 columns, the first two define a range (e.g. min and max

2017-12-06 09:03:36 +0200 バッジを受け取った  卓越した質問 (source)
2017-07-27 14:16:22 +0200 バッジを受け取った  有名な質問 (source)
2017-03-03 13:01:30 +0200 バッジを受け取った  人気の質問 (source)
2017-03-01 15:42:16 +0200 コメント付き回答 How to use SUMIF when condition has a function?

Actually, not sure how this would work (given we're using the function EXACT here). I was trying =SUMPRODUCT(EXACT(A1:A100,B8),D1:D100) without luck. Any idea how to correct it? EDIT: This is the correct formula, I had an issue where the formula was being applied to a cell in D, yielding an error.

2017-03-01 15:37:31 +0200 コメント付き回答 How to use SUMIF when condition has a function?

Thanks! I failed to see that second answer, I'd accepted "the next best thing"!

2017-03-01 15:36:40 +0200 コメント付き回答 How to SUMIF that tests if length of cell is 1

Aha! I agree, this was what I was looking for! Thanks :)

2017-03-01 14:31:45 +0200 バッジを受け取った  卓越した質問 (source)
2017-03-01 13:58:37 +0200 質問をする How to use SUMIF when condition has a function?

Consider the follow example: image description

I want the SUMIF of Cell C8 to be "sum the price, if the category is equal to B8". To individually test equality of each cell in column A I would say, for example for cell A1: EXACT(A1,B8) But whenever I want to apply this sort of thinking within a SUMIF I don't know how to represent "Ax" in the conditional inside the SUMIF.

The only way I see to do this is to create yet another auxiliary column "is exact" to perform the Exact function, and then use that column within the SUMIF conditional.

Is there a simpler alternative that doesn't require an auxiliary column?

Thanks!

2017-02-28 14:45:32 +0200 バッジを受け取った  人気の質問 (source)
2017-02-28 12:36:48 +0200 コメント付き回答 How to SUMIF that tests if length of cell is 1

I have wildcards on. Not sure how to write it. Do you mean =SUMIF(A1:A4,LEN(=?)=1,C1:C4) ? This formula doesn't work.

2017-02-28 12:01:57 +0200 質問をする How to SUMIF that tests if length of cell is 1

Consider the following:

image description

What is the formula that "sums column C if the length of the corresponding cell in column A is =1" ?

2016-12-06 21:40:19 +0200 バッジを受け取った  有名な質問 (source)
2016-10-13 08:56:50 +0200 バッジを受け取った  卓越した質問 (source)
2016-10-12 11:47:02 +0200 バッジを受け取った  人気の質問 (source)
2016-10-12 10:13:13 +0200 コメント付き質問 How can you create a summary sheet of another sheet in Calc

Thanks @mark_t! However I believe I'm unable to edit the question to upload the example directly. (and you're right, I needed to use an external service since I didn't have permissions to upload a file)