MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Student loan formula

• Follow

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

Scoober
```
 0

```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?
> --
>
> Scoober
```
 0

```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?
> --
>
> Scoober
```
 0

```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?
>> --
>>
>> Scoober

```
 0

```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?
> --
>
> Scoober
```
 0

```I'm Bad!!!

Sorry i had the cells around the wrong way

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

"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?
> > --
> >
> > Scoober
```
 0

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

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?
> > --
> >
> > Scoober
```
 0

```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)
> --
>
> 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?
>> > --
>> >
>> > Scoober

```
 0

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

8 Replies
136 Views

Similiar Articles:

7/18/2012 12:03:45 PM