Student loan formula

[Scoober]

Hi all,

I am trying to get this formula to complete a student loan formula.

=IF(AND(H31=H33,H31="no"),"",IF(AND(H31=H33,H31="yes"),(H31-18148)*0.1+(E33-18148)*0.1)

h31 = income
h33 =income

e31 = yes/no
e33 = yes/no

So if yes is selected in e31 and or e33 i need the formula
(h31-$18,148)*10%. There is also a need to show no result if either
applicants income is less than $18,142. Of course only one applicant may have
a student loan and the other may not. So i need the formula to populate the
object cell with nothing if neither applicants have a student loan, the
answer if 1 applicant has a student loan but the other does not or add the
answer together if both have a student loan.

Can you see where i have gone wrong in my attempt?

 

[Tom-S]

Try this:

=IF(OR(H31<18142,H33<18142,AND(E31="no",E33="no")),"",(SUMPRODUCT((E31:E33="yes")*(H31:H33))-COUNTIF(E31:E33,"=yes")*18148)*0.1)

Please note, this assumes that cells E32 and H32 are both empty.

 

[Scoober]

Hi,

Can anyone help me out with the formula above as i have a conference this
weekend and this formula is part of a spreadsheet in need to use?

I would appreciate any help.

Scoober

 

[Bob Phillips]

You can simplify, in my view, that formula

=IF(SUMPRODUCT(--(H31:H33<>""),--(H31:H33<18142),--(E31:E33<>"yes")),"",
SUMPRODUCT(--(E31:E33="yes"),(H31:H33-18148))*0.1)

 

[Tom-S]

Try this:

=IF(OR(H31<18142,H33<18142,AND(E31="no",E33="no")),"",(SUMPRODUCT((E31:E33="yes")*(H31:H33))-COUNTIF(E31:E33,"=yes")*18148)*0.1)

Please note this formula assumes cells E32 and H32 are both empty.

Regards,

Tom

 

[Scoober]

I'm Bad!!!

Sorry i had the cells around the wrong way

it should read

e31 = income
e33 = income


h31 = yes/no
h33= yes/no

I have changed your formula to represent the change

=IF(OR(E31<18142,E33<18142,AND(H31="no",H33="no")),"",(SUMPRODUCT((H31:H33="yes")*(E31:E33))-COUNTIF(H31:H33,"=yes")*18148)*0.1)

However i get #VALUE! in the target cell l51?

Can you see where i have gone wrong?

 

[Scoober]

Hi Tom,

Sorry your formula has worked (i Has something written in e32)

Can you tell me how to make this answer in a monthly amount as it is showing
in a yearly figure (something i did not think about in my original question)

 

[Bob Phillips]

Divide by 12

--

HTH

Bob

Hi Tom,

Sorry your formula has worked (i Has something written in e32)

Can you tell me how to make this answer in a monthly amount as it is
showing
in a yearly figure (something i did not think about in my original
question)

 

[Tom-S]

If the monthly amounts are simply the yearly amount in 12 equal payments,
then you could use:

=IF(OR(H31<18142,H33<18142,AND(E31="no",E33="no")),"",((SUMPRODUCT((E31:E33="yes")*(H31:H33))-COUNTIF(E31:E33,"=yes")*18148)*0.1)/12)

However, if you don't want the monthly amounts to be equal, or if they
depend on things like interest rates, you'd have to provide a bit more
financial information.

Regards,

Tom