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

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.

Problem Statement

Write a Formula field Full_Mailing_Address__c on Contact that concatenates MailingStreet, MailingCity, MailingState, and MailingPostalCode into one readable line — without leaving stray double-commas or trailing separators when a component is blank.

Field Details

• Object: Contact
• Return type: Text Area (Long)
• Example output: 123 Main St, Springfield, IL 62704

Functions to Use

• IF(ISBLANK(field), "", field & separator) — appends a field plus its trailing separator only when the field actually has a value.
• BLANKVALUE(field, "") — simpler form for the last component, which needs no trailing separator.

Problem Statement

Write a Formula field Expiration_Urgency__c on the standard Contract object that classifies how close the Contract's EndDate is to expiring:

• EndDate already passed → "Expired"
• Expires within the next 7 days → "Urgent"
• Expires within the next 30 days → "Soon"
• Otherwise → "OK"

Field Details

• Object: Contract
• Return type: Text

Functions to Use

• Nested IF() — check the most urgent case first, then fall through.
• TODAY() and Date arithmetic (TODAY() + 7) to build the comparison windows.

Problem Statement

An Account has a custom text field External_Id__c storing its Id in a legacy external system. Write a Formula field Legacy_System_Link__c that renders a clickable link to that record — but shows nothing (not a broken link) when External_Id__c is blank.

Field Details

• Object: Account
• Return type: Text (formula renders as a link)
• Link target: https://legacy.example.com/accounts/<External_Id__c>, opened in a new tab, labeled "View in Legacy System".

Functions to Use

• HYPERLINK(url, label, target) — target of "_blank" opens the link in a new tab/window.
• IF(ISBLANK(field), "", ...) to suppress the link entirely when there's no external Id to link to.

Problem Statement

Write a Formula field Display_Full_Name__c on Contact that combines FirstName and LastName with a single space between them — but returns just LastName (no leading space) when FirstName is blank.

Field Details

• Object: Contact
• Return type: Text

Problem Statement

Write a Formula field Weighted_Amount__c on Opportunity that multiplies Amount by Probability to produce a probability-weighted forecast value — treating a blank Amount as 0 instead of erroring.

Field Details

• Object: Opportunity
• Return type: Currency
• Probability is a Percent field stored as a whole number (e.g. 60 for 60%), so it must be divided by 100 before multiplying.

Problem Statement

Write a Formula field Account_Tier__c on Account that combines AnnualRevenue and NumberOfEmployees into a tier label:

• Both AnnualRevenue >= 10,000,000 and NumberOfEmployees >= 500 → "Platinum"
• Either threshold alone → "Gold"
• Neither → "Standard"

Field Details

• Object: Account
• Return type: Text

Problem Statement

Write a Formula field Days_Since_Last_Activity__c on Lead that returns the whole number of days since LastActivityDate. If the Lead has never had any activity (LastActivityDate is blank), return -1 as a sentinel instead of erroring.

Field Details

• Object: Lead
• Return type: Number, 0 decimal places

Problem Statement

A Lead has a custom Number field Lead_Score__c (0–100). Write a Formula field Score_Tier__c that buckets it into a letter tier using CASE():

• 80–100 → "A"
• 50–79 → "B"
• 20–49 → "C"
• 0–19 → "D"

Key Technique

CASE() only matches exact values, not ranges — so first derive an integer "bucket" with FLOOR(Lead_Score__c / 20), then CASE() over that whole-number bucket instead of the raw score.

Problem Statement

Write a Formula field Account_Owner_Email__c on Contact that displays the Email of the User who owns this Contact's parent Account — traversing two relationship levels. Show "No Account Owner" when there's nothing to display (e.g. the Contact has no parent Account).

Field Details

• Object: Contact
• Return type: Text

Key Technique

Formula fields can traverse relationships with dot notation just like SOQL: Account.Owner.Email reaches two levels up in a single expression.

Problem Statement

Write a Formula field Stage_Summary__c on Opportunity that concatenates the Opportunity Name and its StageName picklist into one summary line, e.g. "Acme Renewal - Negotiation/Review".

Key Technique

Picklist fields like StageName cannot be joined with the & concatenation operator directly — attempting Name & StageName is a compile error in a real formula field. Wrap the picklist in TEXT(StageName) first to convert it to a plain string.

Problem Statement

Write a Formula field Rounded_Forecast__c on Opportunity that computes Amount * (Probability / 100), rounded to 2 decimal places — treating either a blank Amount or blank Probability as 0.

Field Details

• Object: Opportunity
• Return type: Currency, 2 decimal places

Problem Statement

Write a Formula field Territory_Code__c on Account that builds a short territory code like "US-CA" by combining BillingCountryCode and BillingStateCode with a hyphen. Show "UNASSIGNED" when either piece is missing.

Field Details

• Object: Account
• Return type: Text

Problem Statement

Write a Formula field At_Risk__c (Checkbox) on Opportunity that flags a deal as at-risk when all of the following hold:

• The Opportunity is still open.
• It's been open more than 90 days (based on CreatedDate).
• Amount is at least 50,000.

Field Details

• Object: Opportunity
• Return type: Checkbox

Problem Statement

An Opportunity has a custom currency field Sales_Target__c representing the owning rep's assigned target for that deal's period. Write a Formula field Target_Achievement_Percent__c that shows what percentage of Sales_Target__c the Opportunity's Amount represents.

Field Details

• Object: Opportunity
• Return type: Percent
• If Sales_Target__c is blank or zero, return 0 instead of dividing by zero.

Functions to Use

• IF(OR(ISBLANK(...), ... = 0), 0, ...) — guards the division.
• Division (/) and multiplication by 100 to express the ratio as a percentage.

Problem Statement

An OpportunityLineItem has the standard fields ListPrice (from its Pricebook Entry) and UnitPrice (the actual, possibly discounted, price on the line item). Write a Formula field Discount_Percent_Display__c that shows how much the line has been discounted below list price, as a percentage.

Field Details

• Object: OpportunityLineItem
• Return type: Percent
• If ListPrice is blank or zero, return 0 rather than dividing by zero.

Functions to Use

• IF(OR(ISBLANK(...), ... = 0), 0, ...) to guard the division.
• (ListPrice - UnitPrice) / ListPrice, scaled by 100, to get the discount percentage.

Problem Statement

An Opportunity has a custom currency field Total_Cost__c tracking the total cost of goods/services for the deal. Write a Formula field Gross_Profit__c that computes Amount minus Total_Cost__c.

Field Details

• Object: Opportunity
• Return type: Currency
• If Total_Cost__c is blank, treat it as 0 so Gross_Profit__c still equals the full Amount.

Functions to Use

• BLANKVALUE(field, substitute) — returns substitute when field is blank, otherwise the field's own value.

Problem Statement

Write a Formula field Close_Quarter_Label__c on Opportunity that turns the standard CloseDate field into a readable label like "Q3 2026", based on standard calendar quarters (Q1 = Jan–Mar, Q2 = Apr–Jun, Q3 = Jul–Sep, Q4 = Oct–Dec).

Field Details

• Object: Opportunity
• Return type: Text
• Example: a CloseDate of August 15, 2026 should display as "Q3 2026".

Functions to Use

• MONTH(CloseDate) and CEILING(month / 3) to derive the quarter number (1–4) from the month.
• CASE() to map the quarter number to its display digit.
• YEAR(CloseDate) plus TEXT() to turn the year number into a string for concatenation, and & to build the final label.

Problem Statement

A Contract has a custom currency field Monthly_Value__c and a custom checkbox At_Risk__c, plus the standard ContractTerm (months) and EndDate fields. Write a Formula field Renewal_Priority_Score__c that scores how urgently this Contract should be worked for renewal, from 0-100, combining three factors:

• Value (up to 50 points): total contract value (ContractTerm * Monthly_Value__c) scaled against a $100,000 ceiling — a Contract worth $100,000+ total earns the full 50 points, proportionally less below that.
• Urgency (up to 40 points): already expired = 40, expiring within 30 days = 35, within 90 days = 20, otherwise = 5.
• Risk bonus (10 points): a flat 10 points added when At_Risk__c is checked.

Field Details

• Object: Contract
• Return type: Number, 0 decimal places

Functions to Use

• MIN() to cap the value component at 50 points.
• CASE(TRUE, condition1, result1, condition2, result2, ..., default) — the "CASE on TRUE" pattern for scoring tiered ranges.
• IF() for the flat risk bonus, and ROUND() to produce a clean whole-number score.

Problem Statement

An OpportunityLineItem has the standard fields TotalPrice (net revenue for the line, i.e. UnitPrice * Quantity) and Quantity, plus a custom currency field Unit_Cost__c (cost per unit). Write a Formula field Margin_Percent__c that computes the true margin percentage: (TotalPrice - TotalCost) / TotalPrice, where TotalCost = Unit_Cost__c * Quantity.

Field Details

• Object: OpportunityLineItem
• Return type: Percent, 2 decimal places
• If TotalPrice is blank or zero, return 0 instead of dividing by zero.
• If Unit_Cost__c is blank, treat cost as 0 (100% margin on that line) rather than erroring.

Functions to Use

• BLANKVALUE(Unit_Cost__c, 0) to safely default a missing cost.
• IF(OR(ISBLANK(...), ... = 0), 0, ...) to guard the division by TotalPrice.
• ROUND(..., 2) to keep the percentage to two decimal places.

Problem Statement

An Account has custom fields Avg_Annual_Revenue__c (Currency), Expected_Lifetime_Years__c (Number), and Customer_Tier__c (picklist: Platinum, Gold, Silver, Bronze). Write a Formula field CLV_Estimate__c that estimates Customer Lifetime Value as:

CLV = Avg_Annual_Revenue__c * Expected_Lifetime_Years__c * Retention_Multiplier

where the Retention Multiplier depends on Customer_Tier__c:

• "Platinum" → 1.5
• "Gold" → 1.2
• "Silver" → 1.0
• anything else (e.g. Bronze, or blank) → 0.8

Field Details

• Object: Account
• Return type: Currency, 2 decimal places
• A blank Avg_Annual_Revenue__c or Expected_Lifetime_Years__c should contribute 0 to the product rather than causing an error.

Functions to Use

• BLANKVALUE(field, 0) to safely default the two numeric inputs.
• CASE(Customer_Tier__c, "Platinum", 1.5, "Gold", 1.2, "Silver", 1.0, 0.8) — CASE() works directly on a picklist's text value without needing ISPICKVAL(), since it is comparing against literal string labels.
• ROUND(..., 2) for a clean currency amount.