Function Consistency Improvements in Excel 2010

Any chance of an improvement in VLookup - what I was hoping to see was a merge between 'Match' and 'VLookup' where the Column_Index in the current VLookup could be defined as a match instead - so select the Column Heading by name, not index number...

Tim,

Why use VLOOKUP when You can use

=Index(ColumnToPick,Match(WhatToMatch,ColumnToMatch,0))

Tim: We did not make any changes to VLOOKUP this time.

Do the standard normal distribution functions really begin with NORM.S._ rather than NORMS._ ? Wouldn't NORM.S._ be inconsistent?

While inverses of exponential and weibull distributions are almost trivial, why didn't you include them? After all, the right tail, _.RT, functions are truly trivial since

f.DIST.RT(x, ..) = 1 - f.DIST(x, ..)

and

f.INV.RT(y, ..) = f.INV(1 - y, ..)

All the function improvements posted so far seem to be in Exotic Function criteria

Why not give some improvements for the "Common Man" functions

Egs.

a) Match would do well with an Instance Number argument to return the position when Duplicates instance

=Match(WhatToMatch, WhereToMatch, TypeOfMatch, MatchInstance)

b)Give us a built in version of Harlan's Pull Function

c)Give us a LastRow Function rather than having to use

= Match("*",A:A,1) for Text Columns and

= Match(BigNumber, A:A,-1) for Number coluns

So LastRow(A1) would give the last row which has data in that Column

e) Modify Indirect to work with Dynamic Names (defined using Index/Counta or Offset/Counta

f) Please extended 3D functionailty to all Aggregate Functions - Sumproduct+SumIF/S+CoutIF/S and Search and Pick functions - INDEX/MATCH, Find, Search etc

g)Take a look at the Functions in MoreFunc.xll

Especially Array Manipulation Functions

@sam - The function improvements in Excel 14 are meant to improve Excel's standing with academia, which has voluminously cataloged Excel's manifold flaws for may years. IOW, a lot of bad press about Excel has been coming out of academia for years, and Microsoft has no other way of controlling it than fixing the flaws. Pure CYA.

You're talking about extending Excel, but no one is going to publish articles about such improvements unless Microsft screws them up, so where's the upside from Microsoft's perspective?

But if you want to talk pipe dreams, don't add optional instance number just to MATCH. {V|H}LOOKUP, FIND and SEARCH could all benefit from such an optional argument. Also, let them be positive or negative: positive means search in the locale text direction (left to right for European languages, right to left in Semitic languages), negative means search in the opposite direction, e.g., FIND(ss,s,,-2) would find the next to last instance of ss in s.

It'd also be nice if INDEX, LEFT, RIGHT, MID and SUBSTITUTE also took negative arguments, e.g., LEFT(s,-3) returned the same as LEFT(s,LEN(s)-3), INDEX(a,-3,-5) returned the same as INDEX(a,ROWS(a)+1-3,COLUMNS(s)+1-5).

On a different tangent, build the WSH regular expression class into all Office applications, and allow regular expressions in Find and Replace dialogs as well as MATCH, {V|H}LOOKUP, SEARCH *and* SUBSTITUTE functions. Make it an option just like regular expressions already are in OpenOffice.

It's also long past time to add some 3D functionality, e.g., if SheetA, SheetB, SheetC and SheetD were adjacent, =INDEX(SheetA:SheetD!G7:P100,12,3,,2) would return a reference to SheetB!I18. A function returning the name of the worksheet containing its range argument, e.g., =SHEETNAME(foo!Z2) returning "foo", and another function returning its index in the worksheets collection would be nice to have even in unsaved workbooks.

That begs the question why =CELL("Filename",Sheet1!A1) returns "" rather than [Book1]Sheet1 when entered in Sheet2!A1 in a new and unsaved workbook provisionally named Book1 when =CELL("Address",Sheet1!A1) entered in the same cell returns "[Book1]Sheet2!$A$1".

Finally, provide a means to TURN OFF overly 'helpful' functionality such as automatically converting text constants that look like date strings into date numbers. Genetics researchers would be grateful.

Harlan, those are mouth-watering improvements. Since we're in Fantasyland, there are a few more I'd like to see in Excel 15:

1) IFBLANK, IFZERO & IFEMPTY. Like IFERROR, but extended for these very common scenarios.

2) SIGMOID

3) SQL.REQUEST

Harlan: regarding your comment on NORM.S._ vs. NORMS._, the naming scheme we are following is ..DIST/INV.RT/2TSo in this case, the base distribution in the normal distribution and "S" is the modifier to indicate that it's the standard normal distribution.

Also we didn't add the inverses for the exponential and Weibull distributions because our intent for Excel 2010 was to always provide the left tail inverse in cases where an inverse existed already.

Why provide right-tail inverses? They're trivial for users who KNOW what they are.

Thanks for the further explanation of NORM.S, but it doesn't hold. Look at your table above. It shows NORM.S.DIST but NORMS.INV. Either the first should be NORMS.DIST if the second is correct, or the second should be NORM.S.INV if the first is correct. That or 'consistency' is little more than marketing blather.

Besides, the Normal distribution would be the only distribution with a modifier unless there are long term plans to add inverse (e.g., for Y = 1/X, if X ~ Gamma, Y ~ Inverse Gamma), transformed and inverse transformed variants of various continuous distributions. How likely is that?

Besides, it was never necessary since the 2nd and 3rd parameters to NORMDIST and NORMINV COULD have been optional, defaulting to 0 and 1, respectively. Perhaps you still could do so for NORM.DIST and NORM.INV.

This naturally leads to the question how the new functions will be handled when saving files in .XLS format. Will LOGNORM.DIST be converted into LOGNORMDIST? Will F.INV.RT(y,..) be converted into FINV(1-y,..)?

As a "power user" (not a guru, but plenty of VBA programming and advanced formula use):

@Harlan's comment re: academia. If this is true, then perhaps the remainder of my comment is irrelevant. :(

While I appreciate the development team's efforts to improve the statistical functions in Excel, I use Excel in the 'real world' (business instead of academia), and I rarely need 'advanced' statistics. I wonder what percent of Excel users actually rely on these functions on a daily basis (other than students taking statistics classes). My guess is that the business users who need advanced statistics are probably more likely to be using a program designed specifically for more robust statistics, either as an Excel add-in, or a separate package. Of the /casual/ users who do advanced statistics in Excel, my guess is at least half of them don't know what they are doing anyway and use the wrong function, the wrong parameters, or provide the wrong interpretation of the results. I'm not sure that is behavior I'd want to further enable.

Sam, Harlan, and Colin's function suggestions are phenominal, and if they were available I'd use more than half of those ideas regularly. Some of the more esoteric examples like the 3D referencing I think would have a smaller audience, but could be useful nonetheless.

If you can't add their great suggestions in the actual 2010 release, consider making a MS add-in like Analysis Toolpak that holds this extra functionality. Maybe roll it out in SP1 or something, to ensure that it is broadly available so that when people like us use them, they will continue to calculate when our (internal or external) customers open up the workbooks we create for them.

Caveat: If, per Harlan's comment, there are fundamental flaws in the way /any/ of the existing functions calculate, then of course you should fix them. My comment is more focused on where to expend efforts to enhance the Excel functions, if energy is going into enhancements.

------------------

Separate question:

------------------

Will the new nomenclature affect non-statistical functions, and if so, can you provide 3-5 examples of more commonly used functions and how they might be named under this new system?

Best regards,

Keith

Just a side note, since I don't see a link to report problems with this website;

In the time it takes me to write a comment, the number/code required to process my comment has changed. I think I can tell when it changes, because I hear an unfamiliar audio click after the webpage has been open a few minutes (sometimes I bounce to other applications mid-comment, and come back to work on it several times).

However, the image containing the numbers doesn't change (I'm using IE6, my company's approved version on a locked-down machine). So for each of the comments I've made, I've had to submit, wait for the submission error, then go back to the bottom of the page and type the new code in for my comment to be successfullly processed.

I understand changing the code at intervals to prevent automated attempts to post, but it would be helpful if the image was also updated. After trying to submit a comment, some commenters may not scroll all the way to the bottom to see that their comment was not successfully submitted. In those cases, you may be losing valuable feedback.

Best,

Keith

Keith -

I've had problems submitting comments too. I've tried many times over the past two weeks, and it was only this morning I tried using IE instead of Firefox. Using IE8, the comment went right in. Go figure.