The new functions in Excel 2010
09.08.2009
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.
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".
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:
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:
And now let's figure out the new functions RANK.AVG and RANK.EQ in Excel 2010 an:
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.
We are curious whether or not more functions will be added to the final version of Excel 2010.
Blog
News
