Formula Field Practice Problems

Problem Statement

Write a Formula field named Commission__c on the Opportunity object that calculates the sales representative's commission:

• If StageName equals 'Closed Won' → commission = 5% of Amount
• Otherwise → commission = 0

Field Details

• Object: Opportunity
• Return type: Currency
• Formula uses the IF() function

Syntax

IF(logical_test, value_if_true, value_if_false)

Problem Statement

Write a cross-object formula field named Account_Industry__c on the Contact object that:

• Returns the Industry field from the parent Account
• If the Account's Industry is blank, returns the text 'No Industry'
• Uses the cross-object formula syntax: Account.Industry

Cross-Object Formula Syntax

To access a field on a related object, use dot notation through the relationship name:

RelationshipName.FieldName

For standard lookup relationships: Account.Industry, Owner.Name, etc.

Key Functions

• ISBLANK(value) — returns true if the value is null or empty string

Problem Statement

Write a formula field Discount_Pct__c on Opportunity that returns the discount percentage based on the Amount:

Return Type

Percent field — return the number (e.g., 15 for 15%)

Use nested IF() or CASE()

Both approaches are valid. Nested IF() is often clearer for range-based logic.

Problem Statement

Write two formula fields on the Opportunity object:

1. Days_Open__c (Number)

Calculate how many days the opportunity has been open:

• If the opportunity is closed (IsClosed = true): CloseDate - CreatedDate
• If still open: TODAY() - DATEVALUE(CreatedDate)
• Use ROUND(..., 0) to get a whole number

2. Age_Category__c (Text)

Categorize the opportunity age using Days_Open__c:

• ≥ 90 days → 'Stale'
• ≥ 30 days → 'Aging'
• ≥ 7 days → 'Active'
• < 7 days → 'New'

Date Formula Functions

• TODAY() — returns today's date
• DATEVALUE(datetime) — converts DateTime to Date
• ROUND(number, decimal_places) — rounds to whole number

Problem Statement

A Contact object has a text field Nickname__c (can be blank) and a number field Years_Known__c (can be null).

Create two formula fields:

• Display_Name__c — returns Nickname__c if not blank, otherwise returns FirstName
• Known_Years__c — returns Years_Known__c if not null, otherwise returns 0

Functions to Use

• BLANKVALUE(field, substitute) — returns field unless blank, then returns substitute
• ISNULL(field) — returns TRUE if field is null
• IF(condition, true_val, false_val)

Problem Statement

A Lead object has a text field Raw_Code__c (format: AB-12345-XY).

Create three formula fields:

• Code_Upper__c — returns Raw_Code__c in ALL CAPS
• Code_Prefix__c — returns the first 2 characters of Raw_Code__c
• Code_Suffix__c — returns the last 2 characters of Raw_Code__c

Functions to Use

• UPPER(text) / LOWER(text)
• LEFT(text, num_chars) / RIGHT(text, num_chars)
• MID(text, start_num, num_chars)
• LEN(text)

Problem Statement

An Opportunity has fields Raw_Score__c (decimal, can be negative) and Bonus__c (number).

Create three formula fields:

• Floor_Score__c — the largest integer ≤ Raw_Score__c
• Abs_Score__c — the absolute value of Raw_Score__c
• Remainder__c — remainder when Raw_Score__c is divided by 7

Functions

• FLOOR(number) — round down to nearest integer
• CEILING(number) — round up to nearest integer
• ABS(number) — absolute value
• MOD(number, divisor) — remainder after division
• MAX(n1, n2, ...) / MIN(n1, n2, ...)

Problem Statement

Write a Validation Rule on the Opportunity object that prevents users from reducing the Amount field once it has been set.

The rule should fire (return TRUE to block save) when:

• The record is being edited (not newly created)
• The new Amount is less than the previous value
• The previous Amount was not null

Key Function

• PRIORVALUE(field) — returns the field's value before the current edit
• ISNEW() — returns TRUE if the record is being created for the first time

Problem Statement

Write a Validation Rule on the Contact object that ensures the Phone field (if not blank) matches the format (XXX) XXX-XXXX where X is a digit.

Valid examples: (415) 555-1234, (800) 867-5309

The rule should fire (return TRUE) when Phone is not blank but does not match the expected pattern.

Key Function

• REGEX(text, pattern) — returns TRUE if text matches the regex pattern
• NOT(REGEX(...)) — returns TRUE when it does NOT match (use in validation)

Problem Statement

A Lead object has these fields:

• AnnualRevenue (currency)
• NumberOfEmployees (integer)
• Industry (text)
• IsConverted (boolean)

Create a checkbox formula field Is_Qualified__c that returns TRUE when the lead is not converted AND meets at least one of these criteria:

• AnnualRevenue ≥ 1,000,000
• NumberOfEmployees ≥ 200
• Industry = "Technology"

Problem Statement

An Opportunity has a picklist field StageName.

Create a formula field Stage_Group__c (Text) that maps stages to groups:

• "Prospecting" or "Qualification" → "Early"
• "Needs Analysis" or "Value Proposition" or "Id. Decision Makers" → "Mid"
• "Perception Analysis" or "Proposal/Price Quote" or "Negotiation/Review" → "Late"
• "Closed Won" → "Won"
• "Closed Lost" → "Lost"

Key Function

• ISPICKVAL(field, value) — returns TRUE if picklist field equals the given value

Problem Statement

A Contract object has a StartDate (Date) field.

Create two formula fields:

• Contract_Year__c (Number) — the year portion of StartDate
• Anniversary_Date__c (Date) — the date exactly one year after StartDate using DATE(YEAR(StartDate)+1, MONTH(StartDate), DAY(StartDate))

Date Functions

• YEAR(date) / MONTH(date) / DAY(date)
• DATE(year, month, day) — construct a date from parts
• DATEVALUE(text_or_datetime) — convert to Date
• TODAY() — today's date

Problem Statement

You have a custom object Region_Config__c with fields:

• Region_Code__c (Text, external ID) — e.g., "NA", "EMEA", "APAC"
• Tax_Rate__c (Percent)
• Currency_Symbol__c (Text)

On the Opportunity object there is a text field Region__c.

Create two formula fields on Opportunity:

• Lookup_Tax_Rate__c — the Tax_Rate__c from Region_Config__c where Region_Code__c = Region__c
• Lookup_Currency__c — the Currency_Symbol__c from the same lookup

Key Function

• VLOOKUP(field_to_return, lookup_field, lookup_value)

Problem Statement

A Case object has these fields:

• Priority (picklist: "High", "Medium", "Low")
• Account.Industry (text via relationship)
• CreatedDate (DateTime)

Create a formula field SLA_Tier__c (Text) using these rules:

• If Priority = "High" AND Account.Industry = "Healthcare" → "Platinum"
• If Priority = "High" → "Gold"
• If Priority = "Medium" AND Account.Industry = "Technology" → "Silver"
• If Priority = "Medium" → "Bronze"
• Otherwise → "Standard"

Also create SLA_Hours__c (Number) using CASE() to map Priority to response hours: High→4, Medium→8, Low→24.