Excel Functions Reference¶

CalcBridge supports over 50 Excel-compatible functions. This reference provides detailed syntax, parameters, and examples for each function.

Logical Functions¶

Functions for conditional logic and boolean operations.

IF¶

Returns one value if a condition is TRUE, another if FALSE.

Syntax: =IF(condition, value_if_true, value_if_false)

Examples:

=IF(A1>100, "High", "Low")           # Returns "High" if A1>100
=IF(B1="Active", 1, 0)               # Returns 1 if B1 is "Active"
=IF(AND(A1>0, B1<100), "Yes", "No")  # Nested condition

IFS¶

Checks multiple conditions and returns a value for the first TRUE condition.

Syntax: =IFS(condition1, value1, [condition2, value2], ...)

Example:

=IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", A1>=60, "D", TRUE, "F")

IFERROR¶

Returns a specified value if a formula results in an error.

Syntax: =IFERROR(value, value_if_error)

Example:

=IFERROR(A1/B1, 0)                    # Returns 0 if division error
=IFERROR(VLOOKUP(A1, B:C, 2, FALSE), "Not Found")

AND¶

Returns TRUE if all arguments are TRUE.

Syntax: =AND(logical1, [logical2], ...)

Example:

=AND(A1>0, A1<100, B1="Active")       # TRUE if all conditions met

OR¶

Returns TRUE if any argument is TRUE.

Syntax: =OR(logical1, [logical2], ...)

Example:

=OR(A1="Red", A1="Blue", A1="Green")  # TRUE if A1 is any color

NOT¶

Reverses the logical value of its argument.

Syntax: =NOT(logical)

Example:

=NOT(A1>100)                          # TRUE if A1 is not greater than 100

SWITCH¶

Evaluates an expression against a list of values and returns the corresponding result.

Syntax: =SWITCH(expression, value1, result1, [value2, result2], ..., [default])

Example:

=SWITCH(A1, "A", 4, "B", 3, "C", 2, "D", 1, 0)  # Grade to GPA

CHOOSE¶

Returns a value from a list based on an index number.

Syntax: =CHOOSE(index_num, value1, [value2], ...)

Example:

=CHOOSE(MONTH(A1), "Q1", "Q1", "Q1", "Q2", "Q2", "Q2", "Q3", "Q3", "Q3", "Q4", "Q4", "Q4")

Lookup Functions¶

Functions for finding and retrieving data.

VLOOKUP¶

Searches for a value in the first column of a range and returns a value from another column.

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Example:

=VLOOKUP(A1, $B$1:$D$100, 3, FALSE)   # Exact match from column 3

HLOOKUP¶

Searches for a value in the first row and returns a value from another row.

Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Example:

=HLOOKUP("Price", A1:F5, 3, FALSE)    # Find "Price" column, return row 3

XLOOKUP¶

Modern lookup function with more flexibility than VLOOKUP.

Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Example:

=XLOOKUP(A1, B:B, C:C, "Not Found", 0, 1)

INDEX¶

Returns a value from a specified position in a range.

Syntax: =INDEX(array, row_num, [col_num])

Example:

=INDEX(A1:C10, 5, 2)                  # Value at row 5, column 2

MATCH¶

Returns the position of a value in a range.

Syntax: =MATCH(lookup_value, lookup_array, [match_type])

Example:

=INDEX(B:B, MATCH(A1, C:C, 0))        # INDEX/MATCH combination

XMATCH¶

Modern version of MATCH with additional features.

Syntax: =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

Example:

=XMATCH("CCC", Rating:Rating, 0, 1)   # Find position of "CCC" rating

Math Functions¶

Functions for mathematical calculations.

SUM¶

Adds all numbers in a range.

Syntax: =SUM(number1, [number2], ...)

Example:

=SUM(A1:A100)                         # Sum of range
=SUM(A1, B1, C1, 100)                 # Sum of specific values

SUMIF¶

Sums cells that meet a single criterion.

Syntax: =SUMIF(range, criteria, [sum_range])

Example:

=SUMIF(A:A, ">100", B:B)              # Sum B where A > 100
=SUMIF(Status:Status, "Active", Amount:Amount)

SUMIFS¶

Sums cells that meet multiple criteria.

Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Example:

=SUMIFS(Amount:Amount, Rating:Rating, "CCC", Status:Status, "Active")
=SUMIFS(D:D, A:A, ">=2025-01-01", B:B, "<100")

SUMPRODUCT¶

Returns the sum of the products of corresponding arrays.

Syntax: =SUMPRODUCT(array1, [array2], ...)

Example:

=SUMPRODUCT(Price:Price, Quantity:Quantity)  # Sum of (Price * Quantity)
=SUMPRODUCT((A:A>100)*B:B)            # Conditional sum using boolean

ABS¶

Returns the absolute value of a number.

Syntax: =ABS(number)

Example:

=ABS(-50)                             # Returns 50
=ABS(A1-B1)                           # Absolute difference

ROUND¶

Rounds a number to a specified number of digits.

Syntax: =ROUND(number, num_digits)

Example:

=ROUND(3.14159, 2)                    # Returns 3.14
=ROUND(A1, 0)                         # Round to whole number

ROUNDUP / ROUNDDOWN¶

Rounds a number up or down.

Syntax: =ROUNDUP(number, num_digits) / =ROUNDDOWN(number, num_digits)

Example:

=ROUNDUP(3.14, 1)                     # Returns 3.2
=ROUNDDOWN(3.19, 1)                   # Returns 3.1

MOD¶

Returns the remainder after division.

Syntax: =MOD(number, divisor)

Example:

=MOD(10, 3)                           # Returns 1

POWER¶

Returns the result of a number raised to a power.

Syntax: =POWER(number, power)

Example:

=POWER(2, 10)                         # Returns 1024
=POWER(A1, 0.5)                       # Square root

SQRT¶

Returns the square root of a number.

Syntax: =SQRT(number)

Example:

=SQRT(144)                            # Returns 12

Statistical Functions¶

Functions for statistical analysis.

COUNT¶

Counts cells containing numbers.

Syntax: =COUNT(value1, [value2], ...)

Example:

=COUNT(A1:A100)                       # Count of numeric cells

COUNTA¶

Counts non-empty cells.

Syntax: =COUNTA(value1, [value2], ...)

Example:

=COUNTA(A:A)-1                        # Count rows (minus header)

COUNTBLANK¶

Counts empty cells in a range.

Syntax: =COUNTBLANK(range)

Example:

=COUNTBLANK(A1:A100)                  # Count empty cells

COUNTIF¶

Counts cells meeting a single criterion.

Syntax: =COUNTIF(range, criteria)

Example:

=COUNTIF(Rating:Rating, "CCC")        # Count CCC ratings
=COUNTIF(Amount:Amount, ">1000000")   # Count over 1M

COUNTIFS¶

Counts cells meeting multiple criteria.

Syntax: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Example:

=COUNTIFS(Rating:Rating, "CCC", Status:Status, "Active")

AVERAGE¶

Returns the arithmetic mean.

Syntax: =AVERAGE(number1, [number2], ...)

Example:

=AVERAGE(A1:A100)                     # Average of range

AVERAGEIF / AVERAGEIFS¶

Conditional average functions.

Syntax: =AVERAGEIF(range, criteria, [average_range])

Example:

=AVERAGEIF(Rating:Rating, "BBB", Spread:Spread)
=AVERAGEIFS(Spread:Spread, Rating:Rating, "BBB", Status:Status, "Active")

MIN / MAX¶

Returns minimum or maximum value.

Syntax: =MIN(number1, [number2], ...) / =MAX(number1, [number2], ...)

Example:

=MIN(A1:A100)                         # Minimum value
=MAX(Spread:Spread)                   # Maximum spread

MINIFS / MAXIFS¶

Conditional min/max functions.

Syntax: =MINIFS(min_range, criteria_range1, criteria1, ...)

Example:

=MINIFS(Price:Price, Rating:Rating, ">=BBB")

MEDIAN¶

Returns the median (middle value).

Syntax: =MEDIAN(number1, [number2], ...)

Example:

=MEDIAN(A1:A100)                      # Median of range

Text Functions¶

Functions for text manipulation.

CONCATENATE / CONCAT¶

Joins text strings.

Syntax: =CONCATENATE(text1, [text2], ...)

Example:

=CONCATENATE(A1, " ", B1)             # Join with space
=CONCAT(A1, "-", B1)                  # Modern syntax

TEXTJOIN¶

Joins text with a delimiter.

Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

Example:

=TEXTJOIN(", ", TRUE, A1:A10)         # Comma-separated list

TEXT¶

Formats a number as text with a specified format.

Syntax: =TEXT(value, format_text)

Example:

=TEXT(A1, "0.00%")                    # Format as percentage
=TEXT(B1, "YYYY-MM-DD")               # Format date

LEFT / RIGHT / MID¶

Extract characters from text.

Syntax: - =LEFT(text, [num_chars]) - =RIGHT(text, [num_chars]) - =MID(text, start_num, num_chars)

Example:

=LEFT(A1, 3)                          # First 3 characters
=RIGHT(A1, 4)                         # Last 4 characters
=MID(A1, 2, 5)                        # 5 chars starting at position 2

LEN¶

Returns the length of a text string.

Syntax: =LEN(text)

Example:

=LEN(A1)                              # Character count

TRIM¶

Removes extra spaces from text.

Syntax: =TRIM(text)

Example:

=TRIM(A1)                             # Clean up whitespace

UPPER / LOWER / PROPER¶

Change text case.

Syntax: =UPPER(text) / =LOWER(text) / =PROPER(text)

Example:

=UPPER(A1)                            # UPPERCASE
=LOWER(A1)                            # lowercase
=PROPER(A1)                           # Title Case

Date Functions¶

Functions for date operations.

DATE¶

Creates a date from year, month, day components.

Syntax: =DATE(year, month, day)

Example:

=DATE(2025, 1, 15)                    # January 15, 2025

TODAY / NOW¶

Returns current date or date/time.

Syntax: =TODAY() / =NOW()

Example:

=TODAY()                              # Current date
=NOW()                                # Current date and time

YEAR / MONTH / DAY¶

Extract components from a date.

Syntax: =YEAR(date) / =MONTH(date) / =DAY(date)

Example:

=YEAR(A1)                             # Extract year
=MONTH(A1)                            # Extract month (1-12)

DATEDIF¶

Calculates the difference between two dates.

Syntax: =DATEDIF(start_date, end_date, unit)

Example:

=DATEDIF(A1, TODAY(), "M")            # Months since date in A1

EDATE¶

Returns a date a specified number of months away.

Syntax: =EDATE(start_date, months)

Example:

=EDATE(A1, 6)                         # Date 6 months from A1

EOMONTH¶

Returns the last day of the month.

Syntax: =EOMONTH(start_date, months)

Example:

=EOMONTH(A1, 0)                       # End of current month
=EOMONTH(A1, 3)                       # End of month 3 months later

Information Functions¶

Functions for checking cell content.

ISBLANK¶

Returns TRUE if cell is empty.

Syntax: =ISBLANK(value)

Example:

=IF(ISBLANK(A1), "Empty", A1)

ISERROR / ISERR / ISNA¶

Check for error values.

Syntax: =ISERROR(value) / =ISERR(value) / =ISNA(value)

Example:

=IF(ISERROR(A1/B1), 0, A1/B1)

ISTEXT / ISNUMBER / ISLOGICAL¶

Check data types.

Syntax: =ISTEXT(value) / =ISNUMBER(value) / =ISLOGICAL(value)

Example:

=IF(ISNUMBER(A1), A1*2, 0)

Function Compatibility Notes¶