New Functions for 2007
To see full samples of these functions, download functionsamples07.xls.
These Functions are new in Excel 2007 (and will return an error if opened in an earlier version).
- AVERAGEIF
- Syntax: AVERAGEIF(range, criteria, range to average)
Average the values in a range that meet specified criteria.
=AVERAGEIF(B16:F55,"RT",D16:D55) will average the values in column D where column B contains RT.
- SUMIFS
- Syntax: SUMIFS(sum range, criteria range1, criteria1, criteria range2,
criteria2, …)
Adds the cells in a range that meet multiple criteria.
=SUMIFS(F16:F55,B16:B55,"RT",C16:C55,"Dept 2") sums the values in column F when column B contains RT and column C contains Dept 2 (both must be true).
- COUNTIFS
- Syntax: COUNTIFS(criteria range1, criteria1, criteria range2, criteria2…)
Applies criteria to cells across multiple ranges and counts the number of times all criteria are met.
=COUNTIFS(B16:B55,"RT",C16:C55,"Dept 2") will count only those rows where column B contains RT and column C contains Dept 2 (both must be true).
- AVERAGEIFS
- Syntax: AVERAGEIFS(average range, criteria range1, criteria1, criteria
range2, criteria2…)
Returns the average of all cells that meet multiple criteria.
=AVERAGEIFS(F16:F55,B16:B55,"RT",C16:C55,"Dept 2") will average those values in column F where column B contains RT and column C contains Dept 2 (both must be true).
- IFERROR
- Syntax: IFERROR(value, value if error)
Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula.
=IFERROR(VLOOKUP(I37,A16:F42,2), "Invalid Part #")
If the vlookup function cannot find the value in cell I37 in the table contained in the range A16:F42, instead of the #N/A error, it will display Invalid Part #.
- Former Analysis ToolPak Add-In functions are now included in Excel 2007:
| ACCRINT | DELTA | IMLN | ODDLPRICE |
| ACCRINTM | DISC | IMLOG10 | ODDLYIELD |
| AMORDEGRC | DOLLARDE | IMLOG2 | PRICE |
| AMORLINC | DOLLARFR | IMPOWER | PRICEDISC |
| BESSELI | DURATION | IMPRODUCT | PRICEMAT |
| BESSELJ | EDATE | IMREAL | QUOTIENT |
| BESSELK | EFFECT | IMSIN | RAND BETWEEN |
| BESSELY | EOMONTH | IMSQRT | RECEIVED |
| BIN2DEC | ERF | IMSUB | SERIESSUM |
| BIN2HEX | ERFC | IMSUM | SQRTPI |
| BIN2OCT | FACTDOUBLE | INTRATE | TBILLEQ |
| COMPLEX | FVSCHEDULE | ISEVEN | TBILLPRICE |
| CONVERT | GCD | ISODD | TBILLYIELD |
| COUPDAYBS | GESTEP | LCM | WEEKNUM |
| COUPDAYS | HEX2BIN | MDURATION | WORKDAY |
| COUPDAYSNC | HEX2DEC | MROUND | XIRR |
| COUPNCD | HEX2OCT | MULTINOMIAL | XNPV |
| COUPNUM | IMABS | NETWORKDAYS | YEARFRAC |
| COUPPCD | IMAGINARY | NOMINAL | YIELD |
| CUMIPMT | IMARGUMENT | OCT2BIN | YIELDDISC |
| CUMPRINC | IMCONJUGATE | OCT2DEC | YIELDMAT |
| DEC2BIN | IMCOS | OCT2HEX | |
| DEC2HEX | IMDIV | ODDFPRICE | |
| DEC2OCT | IMEXP | ODDFYIELD |
