Excel公式求助：先扣减B9至0再扣减C9的实现方案

Got it, let's work through this Excel formula problem to get the deduction behavior you need—all while keeping your original base calculations for B9 and C9 intact.

Core Logic Recap #

We need to:

• Calculate the original "base values" for B9 and C9 (before any F9 deduction)
• Apply the F9 deduction first to B9 until it hits 0
• Apply any remaining deduction to C9 (without letting it go negative)

Modified Formula for B9 #

Replace your current =IF(H6,C6,0)-F9 with either of these options:

Option 1 (Readable IF Statement)

=IF(F9 >= IF(H6,C6,0), 0, IF(H6,C6,0)-F9)

Option 2 (Concise MAX/MIN Version)

=MAX(IF(H6,C6,0) - MIN(F9, IF(H6,C6,0)), 0)

How it works:

• The base value for B9 stays IF(H6,C6,0) (your original calculation)
• If F9’s value is larger than or equal to this base value, B9 gets set to 0 (we’ve fully deducted from B9)
• If F9 is smaller, we subtract F9 directly from B9’s base value to get the remaining amount

Modified Formula for C9 #

Replace your current =$A$2-E6 with this:

=MAX($A$2 - E6 - MAX(F9 - IF(H6,C6,0), 0), 0)

How it works:

• We start with C9’s original base value: $A$2-E6
• We calculate the leftover deduction after depleting B9: MAX(F9 - IF(H6,C6,0), 0) (this is 0 if F9 doesn’t exceed B9’s base value)
• Subtract that leftover from C9’s base value, and use MAX(...,0) to ensure C9 never drops below 0

Example Walkthrough (Matching Your Scenario) #

Let’s plug in your sample numbers:

• B9 base value: IF(H6,C6,0) = 150
• C9 base value: $A$2-E6 = 200
• F9 = 175

1. B9 Calculation: Since 175 ≥ 150, B9 becomes 0
2. Remaining Deduction: 175 - 150 = 25
3. C9 Calculation: 200 - 25 = 175 (exactly what you expected!)

If F9 was 100 instead:

• B9 = 150 - 100 = 50
• No leftover deduction, so C9 stays at 200

内容的提问来源于stack exchange，提问作者Confused