Excel formulas cheat sheet
posted in dotnet on • by Wouter Van SchandevijlCompanion to the EPPlus series, specifically to Part 2: Formulas.
Covering the EPPlus syntax and implemented functions.
Basics
Add a formula with:
var package = new ExcelPackage();
var sheet = package.Workbook.Worksheets.Add("Formulas")
sheet.Cells["A1"].Formula = "A$5";
sheet.Cells["D5"].FormulaR1C1 = "RC[-2]*RC[-1]";
A few EPPlus specific gotchas:
- Do not start .Formula with =
- Use English function names
- Use , as function parameter separator
String manipulation
All string indexes start from 1.
| Function | C# | Remarks |
|---|---|---|
| LEN(str) | str.Length | |
| TRIM(str) | str.Trim | |
| LOWER(str) | str.ToLower | |
| UPPER(str) | str.ToUpper | |
| PROPER(str) | To Title Case - Like This | |
| LEFT(str, cnt) | str.Substring(0, cnt) | Also: RIGHT |
| MID(str, start, length) | str.Substring(start - 1, length) | |
| REPLACE(str, startIndex, length, newStr) | Use SUBSTITUTE instead | |
| SUBSTITUTE(str, “the”, “a”) | str.Replace(“the”, “a”) | |
| REPT(str, number) | string.Concat(Enumerable.Repeat(str, number)) | |
| CONCATENATE(str1, str2, …) | str1 + str2 | |
| str1 & str2 | str1 + str2 | |
| FIND(str, needle) | str.IndexOf(needle) + 1 | Case sensitive |
| SEARCH(str, needle) | Case insensitive FIND |
Numbers & Math
| Function | Meaning |
|---|---|
| VALUE(str) | decimal.Parse(str) |
| FLOOR(number, significance) | Or INT, ROUNDDOWN, TRUNC |
| CEILING(number, significance) | Or ROUNDUP |
| ROUND(number, significance) | |
| ISNUMBER, ISEVEN, ISODD | |
| MAX, MIN | |
| COUNT(range) | Counts all numeric cell values |
| COUNTA(range) | Counts all non empty cell values |
| COUNTBLANK(range) | |
| COUNTIF(range, criteria) | |
| COUNTIFS(range, criteria, range2, crit2) | |
| SUM, SUMIF, SUMIFS | |
| AVERAGE, AVERAGEIF, AVERAGEIFS |
CountIf, SumIf, AverageIf
Criteria possibilities for these xxxIF functions:
- A literal (ex: 15 or “value”)
- Another cell or function result
">=10","=0", …"<>"&A1: not equal to A1"gr?y": single letter wildcard"cat*": 0..x wildcard
Real mathy
| Function | Meaning |
|---|---|
| ABS(number) | Absolute value |
| SIGN(number) | Returns 1 or -1 |
| PRODUCT(range…) | Returns arg1 * arg2 * … |
| POWER(base, exponent) | Or base^exp. Also: SQRT |
| MOD(dividend, divisor) | Modulo. Also: QUOTIENT |
| RAND() | Between 0 and 1 |
| RANDBETWEEN(low, high) | Both params inclusive |
| LARGE(range, xth) | Returns xth largest number |
| SMALL(range, xth) | Returns xth smallest number |
And then a whole bunch like:
- PI, SIN, COS, ASIN, ASINH, TAN, ATAN, …
- EXP, LOG, LOG10, LN
- MEDIAN, STDEV, RANK, VAR
SUBTOTAL
SUBTOTAL(9,…), as apposed to using SUM directly, will not include other subtotals in the calculation.
SUBTOTAL(FUNC_NUM,ref1,[ref2],...)
Add 100 to FUNC_NUM to ignore manually hidden rows. Filtered-out rows are always excluded.
| FUNC_NUM | Function name | Desc |
|---|---|---|
| 1 | AVERAGE | |
| 2 | COUNT | Counts how many numbers |
| 3 | COUNTA | Counts how many values |
| 4 | MAX | |
| 5 | MIN | |
| 6 | PRODUCT | |
| 7 | STDEV | Estimates standard deviation based on a sample |
| 8 | STDEVP | Calculates standard deviation based on the entire population |
| 9 | SUM | |
| 10 | VAR | Estimates variance based on a sample |
Date & Time
| Function | C# |
|---|---|
| DATE(year, month, day) | new DateTime(year, month, day) |
| TODAY | DateTime.Now.Date |
| NOW | DateTime.Now |
| DATE(date) | date.Date |
| Also: MONTH, YEAR, TIME, HOUR, MINUTE, SECOND | |
| WEEKNUM | Week of year. Also: ISOWEEKNUM |
| WEEKDAY | Day of week index (sunday=0) |
| Calculations: | |
| TODAY() - 2 | DateTime.Now.Date.Subtract(TimeSpan.FromDays(2)) |
| NOW() + “2:00” | DateTime.Now.Add(TimeSpan.FromHours(2)) |
| DAYS360(date1, date2) | Difference in days |
| YEARFRAC(date1, date2) | Difference in years (including fractional part) |
| EDATE(date, nrOfMonths) | Add nrOfMonths to date |
| EOMONTHS(date, 0) | Returns the last day of the date month |
| EOMONTHS(date, -2) | Returns the last day of the month of (date - 2 months) |
| WORKDAY(date, workDaysToAdd, holidaysRange) | Add working days to date. holidaysRange is optional |
Boolean logic
| Function | Meaning |
|---|---|
| ISBLANK | |
| ISTEXT, ISNONTEXT | |
| T(A1) | typeof A1 === “string” ? A1 : “” |
| EXACT(strA, strB) | strA == strB. Case sensitive. |
| ISNUMBER, ISEVEN, ISODD | |
| ISLOGICAL | Is a boolean? |
| TRUE(), FALSE() | True/False values |
Or, And, Not
' Check if cell is blank
IF(OR(ISBLANK(A1), TRIM(A1)=""), 1, 0)
' Check if cell is either value
IF(OR(A1="value", A1="value2"), "value1-2", "other")
Stuff that came into being during the making of this post
Other interesting reads
Category:
dotnet
Tags:
excel
cheat-sheet
