(C) 2009 - 2014 by Mourad Louha · All rights reserved

The new functions in Excel 2010

In this article, we will talk about the modified or extended and new functions/formulas in Excel 2010. Most extensions have been made in the statistics category. Please note, that we are testing the Technical Preview version of Excel 2010, so some functions may be added, removed or modified in the final version.

Like in Excel 2007, Excel 2010 uses IntelliSense when entering a function in a cell. For those, who don’t know what IntelliSense ist, I took a screenshot when entering the function.

Excel 2010 Functions

Now, let’s take a look to the functions. I compared all available functions with those in Excel 2007. The dialog with the function list contains the new category “Compatibility”.

Excel 2010 Functions

If you select a function from this list, a info is shown at the bottom of the dialog, explaining the equivalent function of a previous version of Excel. As you can see in the figure above (remark: you can click on the figure to load a greater view), it’s preferable to use BETA.DIST instead of BETADIST. At this point, we must say, that no note is given how using new formulas will affect old workbooks. If you open such a workbook in Excel 2003, you’ll see the error #NAME. Below a list of the new functions. Please note, this article was originally written in German, we kept the German function names, perhaps it may be from interest.

English German Changes Category
BETADIST BETAVERT BETA.DIST Statistical
BETAINV BETAINV BETA.INV Statistical
BINOMDIST BINOMVERT BINOM.DIST Statistical
CRITBINOM KRITBINOM BINOM.INV Statistical
CHIDIST CHIVERT CHISQ.DIST Statistical
CHISQ.DIST.RT Statistical
CHIINV CHIINV CHISQ.INV Statistical
CHISQ.INV.RT Statistical
CHITEST CHITEST CHISQ.TEST Statistical
CONFIDENCE KONFIDENZ CONFIDENCE.NORM Statistical
CONFIDENCE.T Statistical
COVAR KOVAR COVARIANCE.P Statistical
COVARIANCE.S Statistical
EXPONDIST EXPONVERT EXPON.DIST Statistical
FDIST FVERT F.DIST Statistical
F.DIST.RT Statistical
FINV FINV F.INV Statistical
F.INV.RT Statistical
FTEST FTEST F.TEST Statistical
GAMMADIST GAMMAVERT GAMMA.DIST Statistical
GAMMAINV GAMMAINV GAMMA.INV Statistical
HYPGEOMDIST HYPGEOMVERT HYPGEOM.DIST Statistical
LOGNORMDIST LOGNORMVERT LOGNORM.DIST Statistical
LOGINV LOGINV LOGNORM.INV Statistical
MODE MODALWERT MODE.MULT Statistical
MODE.SNGL Statistical
NEGBINOM NEGBINOMVERT NEGBINOM.DIST Statistical
NORMDIST NORMVERT NORM.DIST Statistical
NORMINV NORMINV NORM.INV Statistical
NORMSDIST STANDNORMVERT NORM.S.DIST Statistical
NORMSINV STANDNORMINV NORM.S.INV Statistical
PERCENTILE QUANTIL PERCENTILE.EXC Statistical
PERCENTILE.INC Statistical
PERCENTRANK QUANTILSRANG PERCENTRANK.EXC Statistical
PERCENTRANK.INC Statistical
POISSON POISSON POISSON.DIST Statistical
QUARTILE QUARTILE QUARTILE.EXC Statistical
QUARTILE.INC Statistical
RANK RANG RANK.AVG Statistical
RANK.EQ Statistical
STDEVP STABWN STDEV.P Statistical
STDEV STABW STDEV.S Statistical
TDIST TVERT T.DIST Statistical
T.DIST.2T Statistical
T.DIST.RT Statistical
TINV TINV T.INV Statistical
T.INV.2T Statistical
TTEST TTEST T.TEST Statistical
VARP VARIANZEN VAR.P Statistical
VAR VARIANZ VAR.S Statistical
WEIBULL WEIBULL WEIBULL.DIST Statistical
ZTEST GTEST Z.TEST Statistical
NETWORKDAYS.INTL Date & Time
WORKDAY.INTL Date & Time
AGGREGATE Math. & Trig.
ISO.CEILING ?

Let’s test some functions. As you can see, the function ISO.CEILING is new, we don’t know its German name yet. This functions expects two numbers as arguments, the second one is optional. This function rounds a number up to the nearest integer or to the nearest multiple of significance. The following figure shows some samples:

Excel 2010 Functions

The next function we tested was WORKDAYS.INTL. This function extends the function WORKDAY by the argument “Weekend” and indicates the days of the week that are weekend days. In Europe generally Saturday and Sunday. In Arabic regions this may be Thursday and Friday.

Another function that was extended is NETWORKDAYS. Like WORKDAY.INTL you’ll find the new argument “Weekend” in NETWORKDAYS.INTL.

The last changed function, we tested is the RANK function, which has been splitted in two new functions. In Excel 2007, the RANK function can be used as follows:

Excel 2010 Functions

And now let’s figure out the new functions RANK.AVG and RANK.EQ in Excel 2010 an:

Excel 2010 Functions

Excel 2010 Functions

As we van see, the arguments remained the same like in Excel 2007, however the return value of the functions differs.

RANK.AVG returns the average rank if more than one value in the data source list has the same rank. RANK.EQ return the top rank if more than one value has the same rank; see figure below.

Excel 2010 Functions

We are curious whether or not more functions will be added to the final version of Excel 2010.

This article has also been published in German and in English on the following sites:

 

 
Comments

No comments yet.

Leave a Reply