Postavi Pitanje

zf's profile - activity

2020-12-28 07:19:07 +0100 primio/la značku  Čuveno pitanje (source)
2020-10-28 13:17:25 +0100 primio/la značku  Klasifikator
2020-08-09 20:00:43 +0100 primio/la značku  Čuveno pitanje (source)
2020-08-09 20:00:43 +0100 primio/la značku  Zamjećeno pitanje (source)
2020-08-07 20:26:56 +0100 primio/la značku  Čuveno pitanje (source)
2020-08-07 13:28:35 +0100 primio/la značku  Čuveno pitanje (source)
2020-08-02 18:41:27 +0100 primio/la značku  Zamjećeno pitanje (source)
2020-07-28 10:11:38 +0100 primio/la značku  Popularno pitanje (source)
2020-07-27 22:52:02 +0100 postavio/la pitanje How to extract full number from a string?

How to extract full number from a string? Best illustrated with an example: | | desired output 12/10/12 |

2020-05-30 19:31:06 +0100 primio/la značku  Zamjećeno pitanje (source)
2020-05-26 09:27:38 +0100 primio/la značku  Popularno pitanje (source)
2020-05-25 08:36:05 +0100 primio/la značku  Zamjećeno pitanje (source)
2020-05-22 23:03:37 +0100 komentirano pitanje Range for conditional formatting keeps changing?

I am on version 6.4.4.

2020-05-22 16:32:23 +0100 primio/la značku  Čuveno pitanje (source)
2020-05-21 17:44:06 +0100 primio/la značku  Popularno pitanje (source)
2020-05-20 23:59:19 +0100 postavio/la pitanje Range for conditional formatting keeps changing?

Range for conditional formatting keeps changing? The range for conditional formatting keeps changing and I'm not sure wh

2020-05-20 22:29:11 +0100 postavio/la pitanje Basic string parsing

Basic string parsing I realize the solution I'm looking for can easily be accomplished by adding additional columns for

2020-05-06 18:18:33 +0100 primio/la značku  Čuveno pitanje (source)
2020-04-16 13:33:57 +0100 primio/la značku  Popularno pitanje (source)
2020-04-14 00:42:46 +0100 označen je najbolji odgovor Criteria to check if cell value is a date

I am using a function such as COUNTIFS which requires a criteria. It seems you can't use e.g. ISNUMBER(A1:A6) as the criteria in order to check if a value is a date as opposed to text. Is the best approach to enable regular expressions for Calc and to use a criteria such as "^[:digit:]{2}/[:digit:]{2}/[:digit:]{2}" for date in the format of "MM/DD/YY", despite caveats such as the need to escape certain characters for all formulas and the fact that this seems to be a program-specific setting, potentially messing with documents from other users that use * as wildcards (the default setting)?

If it's the case that most experienced users would probably want regular expressions enabled, then it's not as big of a deal to make the switch to enable regular expressions and learn to deal with it.

I can use SUMPRODUCT as an alternative to COUNTIFS where I can specify the "criteria" using e.g. ISNUMBER(A1:A6), but for some reason it causes a performance issue for my spreadsheet.

2020-04-12 22:19:23 +0100 primio/la značku  Zamjećeno pitanje (source)
2020-04-12 13:39:29 +0100 primio/la značku  Popularno pitanje (source)
2020-04-12 11:03:39 +0100 primio/la značku  Zamjećeno pitanje (source)
2020-04-11 09:18:42 +0100 primio/la značku  Popularno pitanje (source)
2020-04-11 08:08:19 +0100 komentirano pitanje Check if date is within 30 days before/after anniversary

@m.a.riosv I end up with the formula =IF(EDATE(P11,(DATEDIF(P11,TODAY(),"y")+1)*12)-DATE(YEAR(EDATE(P11,(DATEDIF(P11,TOD

2020-04-11 08:07:43 +0100 komentirano pitanje Check if date is within 30 days before/after anniversary

@m.a.riosv I end up with the formula =IF(EDATE(P11,(DATEDIF(P11,TODAY(),"y")+1)*12)-DATE(YEAR(EDATE(P11,(DATEDIF(P11,TOD

2020-04-10 20:03:30 +0100 uređeno pitanje Check if date is within 30 days before/after anniversary

Check if date is within 30 days before/after anniversary Given a cell with a date value, how would one format a cell wit

2020-04-10 18:17:22 +0100 primio/la značku  Čuveno pitanje (source)
2020-04-10 06:53:22 +0100 primio/la značku  Čuveno pitanje (source)
2020-04-09 23:45:34 +0100 uređeno pitanje [ELI5] How does AND condition with SUM via matrix multiplication work?

[ELI5] How does AND condition with SUM via matrix multiplication work? I came across a few SUM formulas like =SUM((A1:A4

2020-04-09 23:44:58 +0100 uređeno pitanje [ELI5] How does AND condition with SUM via matrix multiplication work?

[ELI5] How does AND condition with SUM via matrix multiplication work? I came across a few SUM formulas like =SUM((A1:A4

2020-04-09 23:43:54 +0100 komentiran odgovor [ELI5] How does AND condition with SUM via matrix multiplication work?

Thanks, that makes it much more clear now. I'm not sure if this is related to the original question, but I don't underst

2020-04-09 08:31:39 +0100 komentiran odgovor Last five oldest dates within past 2 years

Why is it B$2:B$99-TODAY() and not simply B$2:B$99 (which yields a wrong result that I don't underrstand) as the third A

2020-04-08 11:32:20 +0100 primio/la značku  Zamjećeno pitanje (source)
2020-04-08 08:33:01 +0100 primio/la značku  Pristalica (source)
2020-04-08 08:33:00 +0100 označen je najbolji odgovor [ELI5] How does AND condition with SUM via matrix multiplication work?

I came across a few SUM formulas like =SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40) from the only documentation I could find regarding using matrix multiplication to allow the use of AND condition in the SUM formula. How are the comparisons used to generate the matrices involved and how is the matrix multiplication done in order to generate the output? An example that illustrates the quote from the documentation is much appreciated:

The formula is based on the fact that the result of a comparison is 1 if the criterion is met and 0 if it is not met. The individual comparison results will be treated as an array and used in matrix multiplication, and at the end the individual values will be totaled to give the result matrix.

P.S. How do you know when it is possible to use this trick for other formulas?

-------- updated ------

C:\fakepath\dates-and-condition.ods

2020-04-07 14:18:56 +0100 primio/la značku  Popularno pitanje (source)
2020-04-07 04:16:59 +0100 komentiran odgovor Last five oldest dates within past 2 years

@m.a.riosv Sorry, one more question: how does matrix multiplication work and could you break it down in this example for

2020-04-06 07:46:38 +0100 komentiran odgovor Last five oldest dates within past 2 years

@m.a.riosv Could you break down how the functions/formula for the SUMPRODUCT works? I have used it in similar setting as

2020-04-06 04:55:08 +0100 komentiran odgovor Last five oldest dates within past 2 years

@m.a.riosv Could you break down how the functions/formula for the SUMPRODUCT works? I have used it in similar setting as

2020-04-06 04:52:48 +0100 primio/la značku  Komentator
2020-04-03 19:44:19 +0100 primio/la značku  Zamjećeno pitanje (source)
2020-04-03 19:41:14 +0100 primio/la značku  Zamjećeno pitanje (source)
2020-04-03 19:35:22 +0100 označen je najbolji odgovor Refer to rest of cells in a column after a cell

Is there a shorthand to refer to "all cells in a column after A3"? E.g. A:A represents all cells in a column--is there a shorthand for A3:A1048576 such that you don't have to type out the A1048576 part? It seems like A3:A doesn't work--I suppose I just have to memorize 1048576.

2 more vaguely related questions:

Would restricting conditional formatting to an expected number of cells you're dealing with as opposed to extending to the rest of the spreadsheet result in any noticeable performance improvement? I just prefer extending it for the sake of consistency and to be sure I will not encounter something unexpected if I ever do require more cells then I had originally formatted for.

Is it good practice to default to using relative reference and use absolute reference as necessary or the other way around for most typical workflows/data? Is it good practice to create simple test conditions like whether a date is entered (what's the best way to test this?) to ensure data entered to certain cells make sense or to only test for it in very specific conditions/context?