On the "Royalty" worksheet, the existing formula in the "Royalty Due" column calculates the royalties due to each author. On cell F4, modify the existing formula to return the amount of royalties due

Exam Question Explanation: Conditional Royalty Formula with IF --- Overall Goal The goal is to add a minimum threshold condition to an existing royalty calculation. Publishers often withhold payments below a minimum amount (like $25) because the administrative cost of cutting a c

Create advanced formulas and macros

Question

On the "Royalty" worksheet, the existing formula in the "Royalty Due" column calculates the royalties due to each author. On cell F4, modify the existing formula to return the amount of royalties due to the author only if the amount is greater than $25, and otherwise returns 0.

Explanation

Exam Question Explanation: Conditional Royalty Formula with IF

Overall Goal

The goal is to add a minimum threshold condition to an existing royalty calculation. Publishers often withhold payments below a minimum amount (like $25) because the administrative cost of cutting a check exceeds the value - so authors only receive payment when they've earned enough. You accomplish this in Excel by wrapping the existing formula inside an IF function.

The Core Concept: Excel's IF Function

=IF(logical_test, value_if_true, value_if_false)

Argument	Purpose
`logical_test`	The condition to evaluate (e.g., `>25`)
`value_if_true`	What to return when the condition is met
`value_if_false`	What to return when it is not met

Step-by-Step Procedure

Step 1: Click on cell F4

You must be in F4 specifically - not another cell in the column - because the question targets this cell. The formula bar will show the existing formula (something like =D4*E4 or =D4*C4*E4 depending on the workbook).

Step 2: Identify and preserve the existing formula

Before modifying anything, note what the existing formula is. You will reuse it in two places inside the IF function. Do not delete it - you are wrapping it, not replacing it.

Example: suppose the existing formula is =D4*E4

Step 3: Wrap the existing formula in an IF statement

Modify the formula to:

=IF(D4*E4>25, D4*E4, 0)

Breaking this down:

D4*E4>25 - the logical test: "Is the calculated royalty greater than $25?"
D4*E4 - if yes, return the actual royalty amount
0 - if no, return zero

Step 4: Confirm with Enter

Press Enter (not Escape) to commit the formula. The cell will display either the royalty amount or 0 depending on whether the threshold is met.

What Goes Wrong If Steps Are Skipped

Mistake	Consequence
Replacing the formula instead of wrapping it	You lose the royalty calculation entirely
Using `>=25` instead of `>25`	$25.00 exactly would pay out - the question says greater than, not greater than or equal to
Returning the text `"0"` (with quotes) instead of the number `0`	The cell stores text, breaking any SUM formulas that reference it
Editing the wrong cell	Other rows get incorrect formulas or existing correct formulas get overwritten

Helpful Memory Tip

Think of IF as asking a yes/no question:

"Is this royalty greater than $25?"

Yes → pay them the royalty

No → pay them nothing (0)

The structure always follows: IF(question, yes-answer, no-answer).

A common mnemonic: "IF it passes the test, give the real value; otherwise give the fallback."

Topics

#IF Function#Conditional Logic#Formula Modification

Community Discussion

No community discussion yet for this question.

Full MO-201 Practice