|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 27th, 2010, 10:53 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Student loan formula
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="y es"),(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 
|
|
#2
May 27th, 2010, 11:06 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Student loan formula
Try this:
=IF(OR(H3118142,H3318142,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="y es"),(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
|
|
#3
May 27th, 2010, 11:31 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Student loan formula
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="y es"),(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
|
|
#4
May 27th, 2010, 11:33 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Student loan formula
You can simplify, in my view, that formula
=IF(SUMPRODUCT(--(H31:H33""),--(H31:H3318142),--(E31:E33"yes")),"", SUMPRODUCT(--(E31:E33="yes"),(H31:H33-18148))*0.1) -- HTH Bob "Tom-S" wrote in message ... Try this: =IF(OR(H3118142,H3318142,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="y es"),(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
|
|
#5
May 27th, 2010, 11:50 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Student loan formula
Try this:
=IF(OR(H3118142,H3318142,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="y es"),(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
|
|
#6
May 28th, 2010, 01:29 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Student loan formula
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(E3118142,E3318142,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(H3118142,H3318142,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="y es"),(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
|
|
#7
May 28th, 2010, 01:35 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Student loan formula
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(H3118142,H3318142,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="y es"),(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
|
|
#8
May 28th, 2010, 11:36 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Student loan formula
Divide by 12
-- HTH Bob "Scoober" wrote in message ... 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(H3118142,H3318142,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="y es"),(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
|
|
#9
May 28th, 2010, 11:49 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Student loan formula
If the monthly amounts are simply the yearly amount in 12 equal payments,
then you could use: =IF(OR(H3118142,H3318142,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(H3118142,H3318142,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="y es"),(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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
