Excel Advanced - Calculation Challenges

Mixed-reference calculation grid

Create a small price-and-quantity matrix to demonstrate references that lock only one dimension.

Completed

Enter quantities 1–5 in R3:R7. Enter unit prices 100, 250, 500 and 1,000 in S2:V2. In S3, calculate Quantity × Unit Price and fill across and down.

Show hint

Lock column R for the quantity, but allow its row to change. Lock row 2 for the price, but allow its column to change.

Show worked solution

=$R3*S$2

The intersection of quantity 5 and price RM1,000 should equal RM5,000.

Round invoice values correctly

Create a rounded invoice column so displayed currency and aggregated currency reconcile.

Completed

In O1 enter Rounded Final. In O2 round Final Amount in M2 to two decimal places, then fill down.

Show hint

ROUND requires the number and the number of decimal places.

Show worked solution

=ROUND(M2,2)

=SUM(O2:O21) should return RM46,948.34. This is one sen higher than summing the unrounded values and then displaying two decimals.

Classify invoice value with nested IF

Assign High, Medium or Standard to every invoice.

Completed

In Q2 return High when Rounded Final is at least RM3,000, Medium when it is at least RM1,500, and Standard otherwise.

Show hint

Test the highest boundary first. If you test RM1,500 first, values above RM3,000 will be classified too early.

Show worked solution

=IF(O2>=3000,"High",IF(O2>=1500,"Medium","Standard"))

ORD-1001 should be Medium; ORD-1009 should be High.

Combine tests with AND

Identify pending invoices that require urgent collection.

Completed

In R2 return Priority only when Payment Status is Pending and Rounded Final is at least RM2,000. Otherwise return a blank cell.

Show hint

AND must receive two logical tests inside IF.

Show worked solution

=IF(AND(H2="Pending",O2>=2000),"Priority","")

Exactly 2 orders should be flagged: ORD-1009 and ORD-1017.

Create a broader review flag with OR

Flag an invoice if either collection or discount risk exists.

Completed

Return Review when Payment Status is Pending or Discount Rate is at least 12%; otherwise return Clear.

Show hint

Use OR inside IF. Each condition can independently make the OR result TRUE.

Show worked solution

=IF(OR(H2="Pending",G2>=12%),"Review","Clear")

ORD-1005 is flagged because its discount is 12%, even though its status is Paid.

Protect division with IFERROR

Calculate invoice value per unit without exposing a division error.

Completed

Calculate Rounded Final ÷ Quantity. If Quantity is zero or another error occurs, display Check quantity.

Show hint

Place the division expression as the first argument of IFERROR and the friendly message as its second.

Show worked solution

=IFERROR(O2/E2,"Check quantity")

After confirming the formula, temporarily enter 0 in one Quantity cell to test the protection, then undo the change.

Summarise outstanding invoices

Use one conditional function to calculate the total value still pending.

Completed

Sum Rounded Final for rows whose Payment Status is Pending.

Show hint

Payment Status is the criteria range; Rounded Final is the sum range.

Show worked solution

=SUMIF(H2:H21,"Pending",O2:O21)

Expected rounded outstanding total: RM9,870.54.

Calculate an average with one condition

Find the average value of paid invoices.

Completed

Average the unrounded Final Amount in M2:M21 only when Payment Status is Paid.

Show hint

AVERAGEIF uses criteria range, criteria, then average range.

Show worked solution

=AVERAGEIF(H2:H21,"Paid",M2:M21)

Expected result: RM2,471.85 when displayed to two decimal places.

Model a tax-rate change

Measure the effect of increasing the tax assumption without overwriting the original calculation.

Completed

Enter 8.5% in T2 and name it ProposedTaxRate. Calculate proposed tax for every row, then total the difference from the current tax.

Show hint

Proposed row tax is Net Sales × ProposedTaxRate. The total impact is proposed total tax minus current total tax.

Show worked solution

=K2*ProposedTaxRate

=SUM(ProposedTaxRange)-SUM(L2:L21)

The increase should be RM217.35 when displayed to two decimals.

Repair broken copied formulas

Diagnose reference errors rather than merely replacing the displayed result.

Completed

Broken formula	Symptom	Your corrected formula
`=E8*F7` in I8	Uses the previous row's unit price
`=K8*$P$3` in L8	Tax is zero or incorrect
`=SUM(K8:M8)` in M8	Circular reference
`=IF(M8=SUM(K8:M8),"OK","Check")`	Check includes the result cell itself

Show worked repairs

=E8*F8

=K8*$P$2

=SUM(K8:L8)

=IF(M8=SUM(K8:L8),"OK","Check")

Find the largest discount without sorting

Use functions to identify the largest Discount Amount and its order.

Completed

First return the maximum value from J2:J21. Then find the matching Order ID using INDEX and MATCH. Use an exact match.

Show hint

MATCH finds the position of the maximum discount. INDEX returns the Order ID at the same position.

Show worked solution

=MAX(J2:J21)

=INDEX(A2:A21,MATCH(MAX(J2:J21),J2:J21,0))

Expected result: ORD-1009, with a discount of RM599.40.

Perform a complete reconciliation

Prove that the model balances at both row and workbook level.

Completed

Create three tests: all row checks are OK; Gross Sales minus Discounts equals Net Sales; and Net Sales plus Tax equals Final Amount. Use ROUND where required to avoid false failures caused by floating-point precision.

Show hint

COUNTIF can test the row flags. Compare rounded totals rather than relying on exact binary decimal equality.

Show worked solution

=IF(COUNTIF(N2:N21,"Check")=0,"PASS","FAIL")

=IF(ROUND(SUM(I2:I21)-SUM(J2:J21),2)=ROUND(SUM(K2:K21),2),"PASS","FAIL")

=IF(ROUND(SUM(K2:K21)+SUM(L2:L21),2)=ROUND(SUM(M2:M21),2),"PASS","FAIL")

All three tests should return PASS. Final unrounded total displayed to two decimals: RM46,948.33.

InfoTech