Financial
Calculate a fictional two-rate marginal charge with MIN and MAX
Split an amount at a threshold, apply one rate to each slice, and compare rounded charges with the underlying blended rate.
Replace nested IFs with the branchless MIN + MAX kinked-schedule pattern!
This is a configurable arithmetic example, not a tax schedule, utility contract or payroll-law calculator. Rates 0.15 and 0.25 and threshold 50000 are fictional. Real rules may include exemptions, credits, fixed fees, daily thresholds or other adjustments.
For this exercise, negative amounts are floored to zero by an explicit chosen policy; that does not establish how losses or refunds should be treated. The threshold and both rates must be nonnegative numeric values. Rates represent currency per input unit and need not be percentages in other applications.
The lower rate applies only up to the threshold; the upper rate applies only to the excess. At 75000 the two slices yield 7500 and 6250, totaling 13750. This differs from applying a whole-volume tier to the entire amount.
| Input amount | First slice at0.15 | Excess at0.25 | Charge and blended rate |
|---|---|---|---|
| $75,000 | $50,000 * 15% = $7,500 | $25,000 * 25% = $6,250 | $13,750 (18.33% effective) |
| $40,000 (Below kink) | $40,000 * 15% = $6,000 | $0 * 25% = $0 | $6,000 (15.00% effective) |
| -5000 | 0 by chosen example policy | 0 | 0; blended rate displayed as0 by convention |
Calculate the marginal charge, rounded to cents
Validation runs before slicing. This illustrative policy rounds once after summing both unrounded slice charges; rounding each slice separately can differ.
=IF(COUNT(B2:B5)<>4,"Invalid inputs",IF(OR(B3<0,B4<0,B5<0),"Invalid inputs",ROUND(MAX(0, MIN(B2, B3)) * B4 + MAX(0, B2 - B3) * B5, 2)))Underlying blended rate before cent rounding
The numerator here is the unrounded charge, so the result may differ from rounded-charge divided by amount for tiny inputs. Nonpositive amounts display zero by convention, not a mathematically defined effective rate.
=IF(COUNT(B2:B5)<>4,"Invalid inputs",IF(OR(B3<0,B4<0,B5<0),"Invalid inputs",IF(B2 <= 0, 0, ROUND((MAX(0, MIN(B2, B3)) * B4 + MAX(0, B2 - B3) * B5) / B2, 4))))Try the example
Use the original values or change them to test your understanding.