Question

In cell F29, use an IF function to display the correct Shipping Charge, based on the amount of the Discounted Total. If the Discounted Total is greater than or equal to the Free Shipping Minimum found in cell B28, the Shipping Charge is 0 (zero); otherwise, the Shipping Charge is 5% of the Discounted Total. Hint: You will need to use a formula for the Value if False to calculate what 5% of the Discounted Total will be.

Answer 1

= IF(B29 >= B28,0,(0.05 * B29))

Step-by-step explanation:

Given

Free Shipping Minimum = B28

Shipping Charge = F29

Required

Write a formula in F29 to calculate the total shipping charge based on the condition in the question.

To solve this, the following assumption needs to be made.

It'll be assumed that cell B29 contains the value for Discounted Total.

So,

Discounted Total = B29

The condition in the question says

1. If Discounted Total (B29) is greater than or equal to the Free Shipping Minimum (B29), then Shipping Charge (F29) is 0

This can be represented as

If(B29 >= B28)

F29 = 0

2. Else (i.e. if (1) above is false), Shipping Charge (F29) equals 5% of Discounted Total (B29)

This can also be represented as

F29 = 0.05 * B29

Writing the formula in (1) and (2) together in Excel format;

= IF(B29 >= B28, 0, (0.05 * B29))

The above formula will give the desired result based on the condition in the question.

Take for instance;

Free Shipping Minimum = B28 = 28

Discounted Total = B29 = 30

Since 30 >= 28, the value of F29 will be 0 because it satisfies condition 1.

But if

Free Shipping Minimum = B28 = 30

Discounted Total = B29 = 28

Since 28 < 30, the value of F29 will be 5% of 28 = 1.4 because it satisfies condition 2