zf's profile - activity

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

I am on version 6.4.4.

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

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

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.

@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

@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

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

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

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

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

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

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

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

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

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

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?