Excel Finance Functions: Text, Dates, Lookups, Arrays & Aggregation

Combine an employee's first and last names into one field.

Text and date functions

Determine the month-end date associated with each transaction date.

Text and date functions

Determine whether an employee qualifies for a bonus based on both sales performance and customer-retention targets.

Logic and error handling

Display the transaction file from largest to smallest transaction amount without changing the original data.

Dynamic arrays

Extract the final four digits from an account code such as US-ATL-6100.

Text and date functions

Extract the year from each invoice date.

Text and date functions

How many customer invoices are more than 60 days overdue?

conditional aggregation

How many invoice amounts are recorded in the accounts payable file?

summary functions

If a lookup cannot find a matching vendor number, display "Vendor Not Found" instead of an Excel error.

Logic and error handling

Produce a list containing each vendor only once.

Dynamic arrays

Should a customer be classified as High Risk, Medium Risk, or Low Risk based on its credit score?

Logic and error handling

Should an invoice be flagged for review if it is both over $25,000 and more than 30 days overdue?

Logic and error handling

Show all invoices that are currently more than 60 days overdue.

Dynamic arrays

Show only employees assigned to the Audit department.

Dynamic arrays

What account description corresponds to general ledger account 610200?

lookups and retrieval

What department is Employee E238 assigned to?

lookups and retrieval

What was the average invoice amount during the quarter?

summary functions

What was the average purchase amount for Vendor V1047?

conditional aggregation

What payment terms are associated with Vendor V1047?

look ups and retrieval

What was the largest individual expense reimbursement submitted this month?

summary functions

What was the largest sale made in the Northeast region?

conditional aggregation

What standard cost corresponds to Product P510?

lookups and retrieval

What was the total dollar amount of accounts receivable at month-end?

summary functions

What was total travel expense for the Consulting department?

conditional aggregation