Excel formulas cheat sheet

Excel formulas cheat sheet

posted in dotnet on  • 

Companion 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
Tags: excel cheat-sheet