You don’t need the CELL() part of your formulas: =INDIRECT(ADDRESS(B1,B2)) or =OFFSET($A$1, B1-1,B2-1) will both work. Note that both INDIRECT and OFFSET are volatile functions. Volatile functions can slow down calculation because they are calculated at every single recalculation.
The good news is that you can throw away the SUMPRODUCT function and replace it with a pair of COUNTIFS functions. The COUNTIFS can use full column references without detriment and is vastly more efficient than the SUMPRODUCT even with the SUMPRODUCT cell ranges limited to the extents of the data. In N2 as a … Read more
Try using this: =ISNUMBER(SEARCH(“Some Text”, A3)) This will return TRUE if cell A3 contains Some Text.
The construct of INDEX() >>:<<INDEX() will work when applied to ranges. Not to arrays AFAIK. It will lead to these errors. Maybe try something like: =LET(range,A1:D2,MAKEARRAY(ROWS(range),COLUMNS(range),LAMBDA(r,c,SUM(INDEX(range,r,SEQUENCE(c)))))) This would resemble the construct of your inital formula. However, in the linked question you have mentioned that you’d like to use SCAN() in combination with BYROW(). You have … Read more
If the only problem is sharing it with your Polish co-worker, then you can simply use the international placeholder “e” to replace “YYYY”: =TEXT(A1,”e-MM-DD”) Now, while this would work fine for your co-worker in Poland, there are countries where “m” and “d” would also need replacement. If in general we need to return the locale … Read more
As stated previously, there is no native function which can do what you want in a single cell. If you absolutely cannot use VBA, then you could use a helper column (can hide the column if preferred) and then have the cell where you want the result simply show the last cell of the helper … Read more
You could also use CEILING which rounds up to an integer or desired multiple of significance ie =CEILING(A1,10) rounds up to a multiple of 10 12340.0001 will become 12350
Here is a function that will do it for you. It’s a little different from Vlookup in that you will only give it the search column, not the whole range, then as the third parameter you will tell it how many columns to go left (negative numbers) or right (positive) in order to get your … Read more
I just hit this problem too… but its not really a problem. You can only have 255 chars in a cell, but unlimited in a formula. Your formula will result in an error, so excel is reading it as text rather than a formula. Find your error and your formula will work.
Edit 7/4/22: ms365 now has introduced a function called VSTACK() and TOCOL() which allows for the the functionality that we were missing from GS’s FLATTEN() (and works even smoother) In your case the formula could become: =TOCOL(A1:D6,1) And that small formula (where the 2nd parameter tells the function to ignore empty cells) would replace everything … Read more