Simplifying a formula that prevents negative numbers
 

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 a
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?




--
MZ

Simplifying a formula that prevents negative numbers
 

Well, to start with you can throw away the unnecessary use of the SUM
function. [Look up the SUM function in Exzcel help to see what it does.]

Instead of =IF(C11-C9=C8,C8,IF(C11-C9=0,C11-C9,0))
you could use =MAX(0,MIN(C11-C9,C8)) or =MEDIAN(C11-C9,C8,0)
--
David Biddulph

"MZ" wrote in message
...
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 a
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?




--
MZ

Simplifying a formula that prevents negative numbers
 

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!

--------------------
(Ms-Exl-Learner)
--------------------


"MZ" wrote:

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 a
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?




--
MZ

Simplifying a formula that prevents negative numbers
 

"MZ" wrote:
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))

For starters, stop using SUM unnecessarily.

But I believe the following does what you intended:

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


----- original message -----

"MZ" wrote in message
...
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 a
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?




--
MZ

Simplifying a formula that prevents negative numbers
 

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))


"MZ" wrote:

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 a
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?




--
MZ