If you're familiar with Excel formulas, you'll find many functions in Analyse data work the same way. This guide maps the Excel functions you know to their equivalents and highlights the key differences that make formulas work on large datasets.
Excel to Analyse data function mapping
Most common Excel functions have direct equivalents. The table below shows how to translate your existing knowledge.
Excel function | Analyse data equivalent | Notes |
SUM | Sum | Same behaviour |
AVERAGE | Avg | Same behaviour |
COUNT | Count | Same behaviour |
COUNTIF | CountIf | Condition only, no range needed |
SUMIF | SumIf | Field first, then condition |
IF | If | Extended syntax available for multiple conditions |
VLOOKUP | Lookup | Simpler syntax, works across tables |
CONCATENATE | Concat or | Both work |
IFERROR | Coalesce or Zn | Similar purpose |
TODAY | Today | Same behaviour |
NOW | Now | Same behaviour |
LEFT | Left | Same behaviour |
RIGHT | Right | Same behaviour |
TRIM | Trim | Same behaviour |
ROUND | Round | Same behaviour |
MIN | Min | Same behaviour |
MAX | Max | Same behaviour |
Key differences from Excel
Analyse data formulas work on entire columns rather than individual cells. This makes them more powerful for large datasets, but requires a slightly different approach.
Column references instead of cell references. In Excel, you write
=A1+B1to add two cells. In Analyse data, you reference column names in square brackets:[Column A] + [Column B]. The calculation applies automatically to every row.No cell ranges. There's no equivalent to Excel's
A1:B10range notation. Instead, summary functions like Sum and Avg automatically operate on all values in the column.Column names are case-sensitive. When referencing columns, the name must match exactly, including capitalisation.
[Emissions]and[emissions]are different references.
Reference type | Syntax | Example |
Column in the same table |
|
|
Column in a different table |
|
|
Control value |
|
|
The extended If function
Excel requires nested IF statements when you have multiple conditions, which quickly becomes difficult to read. In Analyse data, you can chain conditions in a single function.
Excel approach (nested):
=IF(A1<3,"Small",IF(A1<6,"Medium","Large"))
Analyse data approach (chained):
If([Value] < 3, "Small", [Value] < 6, "Medium", "Large")
The pattern is: If(condition1, result1, condition2, result2, ..., else_result).
The function evaluates conditions in order and returns the result for the first condition that's true. If no conditions match, it returns the final value.
Example: Categorising emissions intensity
If([Emissions Per Unit] < 0.5, "Low", [Emissions Per Unit] < 1.5, "Medium", "High")
This assigns "Low" to values below 0.5, "Medium" to values between 0.5 and 1.5, and "High" to everything else.
Common formula recipes
The following recipes solve common analysis tasks. Each example uses sustainability data scenarios to illustrate how the formula works.
Calculate percentage of total
To find what proportion each row contributes to the total, divide the value by the sum of all values.
[Emissions] / Sum([Emissions])
This returns a decimal. To display as a percentage, apply percentage formatting to the column.
Create conditional categories
Assign labels based on values meeting different criteria.
If([Energy Consumption] >= 10000, "High consumption", [Energy Consumption] >= 5000, "Medium consumption", "Low consumption")
Calculate running total
Track cumulative values over time using the CumulativeSum function.
CumulativeSum([Monthly Emissions])
The result depends on how the table is sorted. Sort by date ascending to see emissions accumulate chronologically.
Find difference between two dates
Calculate the number of days, months, or years between dates.
DateDiff("day", [Start Date], [End Date])The first argument specifies the unit: "year", "month", "week", "day", "hour", "minute", or "second".
Example: Days since last audit
DateDiff("day", [Last Audit Date], Today())
Combine text from multiple columns
Join text values together using the ampersand operator or the Concat function.
Using the ampersand operator:
[Facility Name] & " - " & [Location]
Using the Concat function:
Concat([Facility Name], " - ", [Location])
The Concat function doesn't add spaces automatically, so include them in your formula where needed.
Calculate year-over-year change
Combine date functions to compare values across time periods.
If(Year([Date]) = Year(Today()), [Emissions], 0)
This returns the emissions value only for the current year, which you can then compare against a similar calculation for the previous year.
Count rows meeting criteria
Count how many rows match specific conditions.
CountIf([Status] = "Complete")
This counts rows where the status is "Complete".
Sum values meeting criteria
Add up values only for rows that match your criteria.
SumIf([Emissions], [Category] = "Transport")
This totals emissions only for rows where the category is "Transport".
Handle empty values in calculations
When a column contains empty values (called Null), any arithmetic operation with that value returns Null. Use the Zn function to treat empty values as zero.
Zn([Scope 1]) + Zn([Scope 2]) + Zn([Scope 3])
Without Zn, if any scope column is empty, the entire result would be empty.
Return the first available value
Use Coalesce to return the first non-empty value from a list of columns. This is useful when you have fallback fields.
Coalesce([Primary Supplier], [Secondary Supplier], "Unknown")
This returns the primary supplier if available, falls back to the secondary supplier, and shows "Unknown" if both are empty.
Tips for writing formulas
Start simple and build up. Test each part of a complex formula separately before combining them. This makes it easier to identify where problems occur.
Use the autocomplete suggestions. As you type in the formula bar, suggestions appear for functions and column names. Select a suggestion by pressing Enter or clicking it.
Check for type mismatches. Functions expect specific data types. Adding text to a number, for example, will produce an error. Use the Text function to convert numbers to text when needed:
Text([Year]).Watch for empty values. Many calculations return empty results when any input is empty. Use Zn for numbers or Coalesce for text to provide fallback values.
What's next
See Formula basics for detailed guidance on creating calculated columns
Browse the Function reference by category to discover all available functions