Student loan formula - microsoft.public.excel.worksheet.functions

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? -- Thanks in advance. Scoober

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" wrote: > 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? > -- > Thanks in advance. > > 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 "Scoober" wrote: > 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? > -- > Thanks in advance. > > Scoober

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) -- HTH Bob "Tom-S" <TomS@discussions.microsoft.com> wrote in message news:3C2CB340-56FE-4799-85DA-C74236653844@microsoft.com... > 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" wrote: > >> 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? >> -- >> Thanks in advance. >> >> Scoober

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" wrote: > 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? > -- > Thanks in advance. > > 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? -- Thanks in advance. Scoober "Tom-S" wrote: > 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" wrote: > > > 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? > > -- > > Thanks in advance. > > > > 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) -- Thanks in advance. Scoober "Tom-S" wrote: > 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" wrote: > > > 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? > > -- > > Thanks in advance. > > > > Scoober

Divide by 12 -- HTH Bob "Scoober" <scott.miller@clear.net.nz> wrote in message news:A5328EF4-E649-4E25-BF36-9E31A5B45336@microsoft.com... > 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) > -- > Thanks in advance. > > Scoober > > > "Tom-S" wrote: > >> 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" wrote: >> >> > 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? >> > -- >> > Thanks in advance. >> > >> > Scoober

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 "Scoober" wrote: > 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) > -- > Thanks in advance. > > Scoober > > > "Tom-S" wrote: > > > 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" wrote: > > > > > 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? > > > -- > > > Thanks in advance. > > > > > > Scoober

Similiar Articles:

Cumulative calculation - microsoft.public.access.queries ...
Cumulative cost - microsoft.public.access.forms How to Use Excel Formulas To Calculate Term Loan ... will compute the cumulative distribution function (CDF) for Student's t ...

Cumulative cost - microsoft.public.access.forms
I need my query to calculate the year to date by student and the program ... Cumulative cost - microsoft.public.access.forms How to Use Excel Formulas To Calculate Term Loan ...

Monthly Payment Formula for Student Loan Repayment Program
New Monthly Payment Formula for Direct Loans. You may qualify for a lower monthly payment formula in the new student loan repayment program. The income-based student ...

Which Federal Student Loan Payment Formula?
Your loan payment formula for government student loan repayment can depend on how much student loan relief you need to figure loan payments within your budget.

Student Aid on the Web
You can calculate the monthly interest on your loan by using the Simple Daily Interest Formula. ... The Civil Legal Assistance Attorney Student Loan Repayment Program ...

How to Calculate a Loan Repayment Formula | eHow.com
How to Calculate a Loan Repayment Formula. When you take out a loan, your lender will ... to these increased costs, more and more students are relying on student loans to...

Amortization calculator - Wikipedia, the free encyclopedia
One can rearrange the formula to solve for any one term, except for i, for which ... can also be used to analyze other debt, including short-term loans, student loans and ...

7/18/2012 12:03:45 PM