Simplifying a formula that prevents negative numbers

  • Thread starter Thread starter MZ
  • Start date Start date
M

MZ

Is there a shorter way of writing the following formula for calculating an
alimony deduction?

=IF(SUM(C11-C9)>=C8,C8,IF(SUM(C11-C9)>=0,SUM(C11-C9),0))

The formla calculates the alimony deductible (if any) on the 1040 return for
a given amount of total payments for alimony and child support per year.

The rules are:
1) Payments are first allocated to child support (which is not deductible)
2) Any remaining payment is allocated to alimony, subject to a maximum
amount that is required in a decree of separation or divorce; that is, any
overpayments of alimony are also not deductible.

C8 is the required alimony payment per year
C9 is the required child support payment per year
C11 is the amount of actual total payments for a given year

To keep the formula from recording negative amounts, I had to insert two
additional "IF" operators. Is there a way to shorten the formula?
 
In your below formula you have used only two IF functions, you can add
another five more on it.

Remember to Click Yes, if this post helps!
 
MZ said:
Is there a shorter way of writing the following formula for calculating
an alimony deduction?
=IF(SUM(C11-C9)>=C8,C8,IF(SUM(C11-C9)>=0,SUM(C11-C9),0))
Click to expand...

For starters, stop using SUM unnecessarily.

But I believe the following does what you intended:

=MAX(0, MIN(C8, C11-C9))


----- original message -----
 
Hi
I don't see a simpler way to make it work without the 'nested IF', but if
brevity is your ultimate goal, you can eliminate the 'SUM()' operator and get
the same results.
Like so:
=IF(C11-C9>=C8,C8,IF(C11-C9>=0,C11-C9,0))
 
Back
Top