Skip to main content

Common Math Operations

Perform mathematical calculations with SUM, AVERAGE, MIN, MAX, and other statistical functions.

Relevant Formulas​

SUM​

Adds its arguments together.

View SUM Formula Reference →

AVERAGE​

Returns the average (arithmetic mean) of its arguments.

View AVERAGE Formula Reference →

MIN​

Returns the minimum value in a list of arguments.

View MIN Formula Reference →

MAX​

Returns the maximum value in a list of arguments.

View MAX Formula Reference →

Additional Math Formulas​

Basic Usage​

SUM​

=SUM([price], [tax], [shipping])
Result: Total of all values

=[base_price] + [tax] + [shipping]  // Alternative

AVERAGE​

=AVERAGE([q1_sales], [q2_sales], [q3_sales], [q4_sales])
Result: Annual average

MIN/MAX​

=MIN([price1], [price2], [price3])  // Lowest price
=MAX([score1], [score2], [score3])  // Highest score

Common Use Cases​

Totals and Subtotals​

// Order total
=SUM([item_price], [tax], [shipping], [handling])

// Line item total
=[quantity] * [unit_price]

Averages and Rates​

// Average rating
=AVERAGE([rating1], [rating2], [rating3])

// Average daily sales
=[total_sales] / [days_in_period]

Pricing Calculations​

// Discount price
=[original_price] * (1 - [discount_rate])

// Tax calculation
=[subtotal] * [tax_rate]

// Profit margin
=([selling_price] - [cost]) / [selling_price]

Rounding​

// Round to 2 decimal places
=ROUND([price], 2)

// Always round up
=ROUNDUP([units_needed], 0)

// Round to nearest dollar
=ROUND([amount], 0)

Advanced Techniques​

Conditional Math​

// Calculate with discount if applicable
=IF([is_member] = TRUE,
    [price] * 0.9,
    [price])

// Minimum order amount
=MAX([calculated_total], [minimum_order])

Multiple Operations​

// Complex calculation
=ROUND(([base_price] * [quantity]) * (1 + [tax_rate]), 2)

// Profit percentage
=ROUND((([revenue] - [cost]) / [revenue]) * 100, 1)

Safe Division​

// Avoid division by zero
=IF([denominator] = 0, 0, [numerator] / [denominator])

// Using COALESCE
=[numerator] / COALESCE([denominator], 1)

Common Patterns​

Percentage Calculations​

// Percentage of total
=([part] / [total]) * 100

// Percentage change
=(([new_value] - [old_value]) / [old_value]) * 100

// Convert percentage to decimal
=[percentage] / 100

Financial Calculations​

// Compound interest (simplified)
=[principal] * POWER((1 + [rate]), [periods])

// Monthly payment estimate
=[loan_amount] * [monthly_rate]

Statistical Operations​

// Range
=MAX([values]) - MIN([values])

// Midpoint
=(MIN([value]) + MAX([value])) / 2

Best Practices​

  1. Use ROUND for Money - Always round financial calculations
  2. Handle Division by Zero - Check denominator before dividing
  3. Consider NULL Values - Use COALESCE for safe math
  4. Order of Operations - Use parentheses for clarity
  5. Test Edge Cases - Zero, negative, NULL, very large numbers

Performance Considerations​

  • Avoid Complex Nesting - Break into multiple columns if very complex
  • Pre-calculate Constants - Don't recalculate same values
  • Consider Aggregations - Use GROUP BY for summary statistics

Additional Formula References​

Additional examples will be added soon.