Ask Your Question

migueldealmeida's profile - activity

2020-05-14 18:05:45 +0200 received badge  Notable Question (source)
2020-03-03 19:58:21 +0200 received badge  Famous Question (source)
2020-02-17 23:27:09 +0200 received badge  Notable Question (source)
2019-11-20 06:52:52 +0200 received badge  Popular Question (source)
2019-11-19 21:17:34 +0200 edited question How to find where in the interval a number is - For a particular category

How to find where in the interval a number is - For a particular category In this thread we found a clever way to find w

2019-11-19 20:49:15 +0200 received badge  Famous Question (source)
2019-11-19 20:49:15 +0200 received badge  Notable Question (source)
2019-11-05 17:08:43 +0200 received badge  Notable Question (source)
2019-10-30 21:19:16 +0200 received badge  Popular Question (source)
2019-04-30 10:06:40 +0200 commented question 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 received badge  Commentator
2019-04-29 21:09:06 +0200 received badge  Popular Question (source)
2019-04-29 18:21:01 +0200 edited question 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 asked a question 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 asked a question 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 received badge  Enthusiast
2019-04-27 17:32:54 +0200 asked a question 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 received badge  Popular Question (source)
2019-04-04 10:13:13 +0200 edited question 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 asked a question 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 received badge  Famous Question (source)
2019-01-28 10:39:24 +0200 marked best answer 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 received badge  Popular Question (source)
2019-01-26 20:19:09 +0200 asked a question 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 asked a question 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 commented answer How to find where in the interval a number is

This works really well, thanks!

2018-02-18 15:39:30 +0200 commented question 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 marked best answer 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 received badge  Popular Question (source)
2018-02-16 20:05:13 +0200 edited question 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:05:13 +0200 received badge  Editor (source)
2018-02-16 20:02:14 +0200 edited question 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 asked a question 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 received badge  Notable Question (source)
2017-07-27 14:16:22 +0200 received badge  Famous Question (source)
2017-03-03 13:01:30 +0200 received badge  Popular Question (source)
2017-03-01 15:42:16 +0200 commented answer 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 commented answer 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:45 +0200 marked best answer 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" ?

2017-03-01 15:36:40 +0200 commented answer 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 received badge  Notable Question (source)
2017-03-01 13:58:37 +0200 asked a question 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 received badge  Popular Question (source)