Harlan Grove
My feedback

9 votes4 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
An error occurred while saving the comment 
10 votes6 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
An error occurred while saving the comment Harlan Grove commented=ADDRESS(1,COLUMN(AB1),4) returns "AB1", so SUBSTITUTE(ADDRESS(1,COLUMN(AB1),4),1,"") returns "AB". I realize 3 function calls are more than 1, but are 3 function calls for something as rarely needed as this an excessive burden?

21 votes14 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
An error occurred while saving the comment Harlan Grove commented@Ohil,
Excel would be far less pleasant for hundreds of millions to use if it had thousands of builtin functions to address the needs of hundreds of users each.
I realize COUNTIF(ref_to_string,"*"&substring&"*") may not be obvious, but it does address the need without all that much mysterious syntactic baggage.
I hate to put it this way, but with only 18 votes in favor, don't get your hope up on seeing this in Excel before Excel gets regular expression functions.
An error occurred while saving the comment Harlan Grove commented@Roy, 'routine, and direct solutions to the general problems faced fairly often'
What if there are many (dozens if not hundreds) of variations on certain general problems? Text matching and manipulation are standard computing problems, but not financial arithmetic problems, so spreadsheets seldom address them.
Why not the most general solutions possible?
Anyway, blame Lotus 123 for this as well as MSFT's decision to copy 123's @function semantics so completely. Had SEARCH or FIND returned a positive integer for substring matches or 0 for no match rather than #VALUE!, this particular issue would never have arisen. However, backwards compatibility requires FIND and SEARCH continue to return #VALUE! for no match.
If Excel's eventual REGEXMATCH works the same as Google Sheets's function of the same name, it'll take care of this since if substring contained no metacharacters, REGEXMATCH(string,substring) would return TRUE when substring appeared within string and FALSE otherwise.
An error occurred while saving the comment Harlan Grove commented@Roy, wildcad searching usually stops once a match has been established, so COUNTIF(something,"*abc*") would usually note the presence of the initial *, so find the 1st 'a' in something, then check whether the next 2 characters match 'bc'. If so, end and return 1, otherwise find the next 'a' in something.
Excel's * and ? wildcards are the most basic form of regular expressions, and regular expressions implemented correctly are quire efficient. Thus, you can't really complain COUNTIF would be inefficient.

1 vote2 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
An error occurred while saving the comment Harlan Grove commentedThe Excel team has never done a good job with boundary cases for special numeric functions. For a long time, GAMMALN(1) and GAMMALN(2) didn't return 0. I figure the Excel team figures those who really need precision for such things would be using MATLAB or other math packages rather than Excel. Excel really isn't a general mathematical tool.

66 votes7 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
An error occurred while saving the comment Harlan Grove commentedThere's a new UNIQUE function in the offing. It's in some Office Insider versions. Once it becomes standard, =COUNTA(UINIQUE(range)) would give the count if distinct items in range.

153 votes23 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
An error occurred while saving the comment Harlan Grove commentedThing is dynamic arrays can also spill into a potentially variable number of columns as well as rows. There's no obvious or perhaps even sensible way to handling column indexing which doesn't require numeric column indexing. Why should rows get special treatment?
To some extent, what's really needed is a sensible SEQUENCE function like the one in Google Sheets. It'd also be handy to have backtofront indexing like in Icon and SNOBOL languages so that index 1 referred to the LAST item in an array, 2 to the 2nd to last item, etc.

219 votes34 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
An error occurred while saving the comment Harlan Grove commentedWith new dynamic arrays, does it really make sense to have total rows and columns below or to the right, respectively, any longer? There's always been good reason to put such totals above or to the left of the range over which they'd evaluate, and in the coming era of dynamic arrays and spilled formulas, above/to the left seem to have become the only sensible way to do this.
That said, do we really need B3:Z3: =SUMCOLS(B5:Z999) rather than B3: =SUM(B5:B999) filled right into C3:Z3?
If spilled formulas could truly be unpredictable, so that the largest extent in terms of rows and columns really isn't known, would it ever make sense to put ANYTHING to the right or below any formula returning a variablesize array result?
I had to learn some PL/I way, way back, and I'd prefer Excel didn't copy the approach of adding anything anyone could think of to it. Minimalism may suggest a 2step approach, e.g., one new function which returns the full array extent of the results given by the topleft cell in its single reference argument or the largest single area range containing the same R1C1 formula as the topleft cell in its single reference argument. For this sort of thing, GETRANGE(B5) which would return a reference to B5:Z999, and that could be chopped up using INDEX(GETRANGE(B5),0,COLUMNS($B$3:D3)) for the sum of column D within the result range.

122 votes17 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
An error occurred while saving the comment Harlan Grove commentedAlternatively, make ALL informational XLM functions proper worksheet functions since GET.WORKBOOK(1) returns a horizontal array of all worksheets in the active workbook and GET.DOCUMENT(1,GET.CELL(66,A1)) returns a horizontal array of all worksheets in the workbook containing this formula.
@Tony Valko: Especially annoying since Lotus 123 Release 3's (1989, so 27 years ago) @CELL could return the worksheet name, its @INFO could return the number of worksheets in a workbook, and a combination of @CELL, @COORD and @@ could be used to return all worksheet names in a workbook. I believe Quattro Pro also had this functionality in the early 1990s. OTOH, Excel requires VBA or XLM functions.

55 votes10 comments · Excel for Windows (Desktop Application) » Tables, Sorting and Filtering · Flag idea as inappropriate… · Admin →Harlan Grove supported this idea ·

64 votes13 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
An error occurred while saving the comment Harlan Grove commentedKenneth,
I didn't mention stack access other than the top. I don't want to recreate FORTH.
Being able to create names on the fly would be good. About time most of the XLM functions became available in nonmacro worksheets. Unfortunately, SET.NAME() raises the ticklish question whether expressions in cell formulas could change ALREADY DEFINED names, and whether ad hoc names created while evaluating formulas should be deleted when formula evaluation completes.
An error occurred while saving the comment Harlan Grove commentedPoor example, as =MIN(5,[Formula]) would suffice.
I get the point, but PUSH and POP functions would work as long as PUSH returned its argument's value. That is, something like
=IF(PUSH(very_complex_expression)>=0,SQRT(PUSH(POP())),IF(PUSH(POP())<0,PUSH(POP())&" is negative",IF(NOT(ISERROR(PUSH(POP()))),PUSH(POP())&" not numeric",POP())))
OK, PUSH(POP()) would be very common, so maybe a PPOP() or GET().

37 votes10 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →Harlan Grove supported this idea ·

63 votes29 comments · Excel for Windows (Desktop Application) » Data Import · Flag idea as inappropriate… · Admin →
Thanks for the feedback!
Can you comment on whether you expect this to basically import all the lines as text to column A, or if you have some other expectation?
Also, I’m assuming people refer here to the file/open path, and not to the Data/FromText or NewQuery/FromText path.
An error occurred while saving the comment Harlan Grove commentedFor example, genetic data often looks like dates, and opening CSVs converts such data into dates. Since opening CSVs doesn't launch Text to Columns, there's no way to prevent Excel from making this conversion other than by changing the CSV file's extention to .TXT and opening is as a text file rather than as a CSV file.
IOW, ONLY separate CSV records into columns, but otherwise leave as text with no conversion of numbers into numeric values, no conversion of anything looking like dates into dates, etc.
Harlan Grove supported this idea · 
45 votes11 comments · Excel for Windows (Desktop Application) » Other · Flag idea as inappropriate… · Admin →Harlan Grove supported this idea ·
If your formulas in cells X99 and Y100 were =A5 and =B6, respectively, and you CUT cell B6 and pasted it into cell A5, you'd want both the X99 and Y100 formulas to be =A5? If Y100 were returning the correct value of the former B6 but now A5 but cell X99 returns #REF!. How would you be able to diagnose the problem in cell X99? If you made X99 the active cell, pressed [F2] to enter Edit mode, then pressed [Enter] to reenter it without changing anything, would you expect it still to return #REF! or the new value in A5?
Likewise for deleting row 5 or column A.
In any of these actions, you're DESTROYING a range to which your formulas refer. I'd argue it's better to have Excel replace the A5 reference with #REF!. It'd be better still if Excel displayed a confirmation dialog stating that you're about to destroy a range to which other cells refer and prompting you whether you really want to do that. However, if you did perform the action, Excel should alter the formulas. Maybe another approach would be Excel adding the original formula as a cell comment. However, the formula itself should be changed to reflect the fact that you've destroyed a formerly valid reference.