If you cannot come up with a formula, you can use a table for such a limited number of outcomes: final int[][] result = new int[][] { { 0, 0, 1, 2 }, { 0, 0, 2, 1 }, { 2, 1, 3, 3 }, { 1, 2, 3, 3 } }; return result[one][two];
I’m wondering if anyone has had a similar problem, and why the formula generated hyperlinks are not working for me. Alas, this seems to be painful truth: Excel does not add to Hyperlinks collection formula-generated links – below is the screen from the debugger which is pointed to =HYPERLINK(“http://www.google.com/”;”Google”): I’m not sure whether this is … Read more
Instead of formulas, Why not use a PivotTable to crunch the numbers? You potentially face a longer one-time hit to load the data into the PivotCache, but after that, you should find a PivotTable recalculates much faster in response to filter changes than these computationally expensive formulas. Is there any reason you’re not using one? … Read more
Use¹ the following as an array formula. =TEXTJOIN(“–>”, TRUE, IF(LEN(C3:I3), C$2:I$2, “”)) Pre-Excel 2016 versions While you could just string together a series of IF statements, a cleaner alternate might be to write a user defined function (aka UDF). In a standard VBA module code sheet: Function udf_Stitch_Together(r As Range, _ h As Range, _ … Read more
If I have a formula in cell A1 (e.g. = B1 * C1) and I want to run some VBA code each time A1 changes due to updates to either cell B1 or C1 then I can use the following: Private Sub Worksheet_Calculate() Dim target As Range Set target = Range(“A1”) If Not Intersect(target, Range(“A1”)) … Read more
I isolates or insulates the contents of I( … ) from the gaze of R’s formula parsing code. It allows the standard R operators to work as they would if you used them outside of a formula, rather than being treated as special formula operators. For example: y ~ x + x^2 would, to R, … Read more
[Dis]claimer: I’m the author/maintainer of xlrd. The documentation references to formula text are about “name” formulas; read the section “Named references, constants, formulas, and macros” near the start of the docs. These formulas are associated sheet-wide or book-wide to a name; they are not associated with individual cells. Examples: PI maps to =22/7, SALES maps … Read more
I see a couple issues going on here. First, and I don’t think this is causing any trouble, but let’s make your data frame in one step so you don’t have v1 through v4 floating around both in the global environment as well as in the data frame. Second, let’s just make v2 a factor … Read more
Each character is used to represent 6 bits (log2(64) = 6). Therefore 4 chars are used to represent 4 * 6 = 24 bits = 3 bytes. So you need 4*(n/3) chars to represent n bytes, and this needs to be rounded up to a multiple of 4. The number of unused padding chars resulting … Read more