+ Reply to Thread
Results 1 to 13 of 13

nesting for 2 dependents, and 4 formula options

  1. #1
    dodo news
    Guest

    nesting for 2 dependents, and 4 formula options

    I want to choose one of four formulas to use, conditional on the contents of
    two cells

    i.e.
    If a1="male" and a2>30, (0.048*a3)+3.653, if a1="male' and a2>60,
    (0.049*a3)+2.459, if for two more formulas

    I was trying to do a combined =IF(AND, but this doesn't seem to be
    nestable....

    Any help appreciated....
    TIA



  2. #2
    Bob Phillips
    Guest

    Re: nesting for 2 dependents, and 4 formula options

    =IF(AND(A1="male",A2>30), (0.048*A3)+3.653,
    if(AND(A1="male",A2>60),(0.049*a3)+2.459, IF(...))))




    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "dodo news" <[email protected]> wrote in message
    news:[email protected]...
    > I want to choose one of four formulas to use, conditional on the contents

    of
    > two cells
    >
    > i.e.
    > If a1="male" and a2>30, (0.048*a3)+3.653, if a1="male' and a2>60,
    > (0.049*a3)+2.459, if for two more formulas
    >
    > I was trying to do a combined =IF(AND, but this doesn't seem to be
    > nestable....
    >
    > Any help appreciated....
    > TIA
    >
    >




  3. #3
    Tom Ogilvy
    Guest

    Re: nesting for 2 dependents, and 4 formula options

    =If (And(a1="male",a2>30), (0.048*a3)+3.653, if(And(
    a1="male",a2>60),(0.049*a3)+2.459,"burp"))

    should work for 5 more, 7 is the limit


    =IF(AND(A1=1,B1=1),1,IF(AND(A1=2,B1=2),2,IF(AND(A1=3,B1=3),3,IF(AND(A1=4,B1=
    4),4,IF(AND(A1=5,B1=5),5,IF(AND(A1=6,B1=6),6,IF(AND(A1=7,B1=7),7,8)))))))

    --
    Regards,
    Tom Ogilvy



    "dodo news" <[email protected]> wrote in message
    news:[email protected]...
    > I want to choose one of four formulas to use, conditional on the contents

    of
    > two cells
    >
    > i.e.
    > If a1="male" and a2>30, (0.048*a3)+3.653, if a1="male' and a2>60,
    > (0.049*a3)+2.459, if for two more formulas
    >
    > I was trying to do a combined =IF(AND, but this doesn't seem to be
    > nestable....
    >
    > Any help appreciated....
    > TIA
    >
    >




  4. #4
    dodo news
    Guest

    Re: nesting for 2 dependents, and 4 formula options

    Thanks for prompt reply Bob and Tom,

    i've messed with your syntax and am still having error reports
    have broken up the formula into lines to make it easier to read.
    Would greatly appreciate your perusal.
    TIA

    =IF(AND(B3="male",B4>60),(0.049*B6)+2.459)/0.004184,
    IF(AND(B3="male",B4>30),(0.048*B6)+3.653)/0.004184,
    IF(AND(B3="female",B4>60),(0.038*B6)+2.755/0.004184,
    IF(AND(B3="female",B4>30),(0.048*B6)+3.653)/0.004184,"out of range")








    > =If (And(a1="male",a2>30), (0.048*a3)+3.653, if(And(
    > a1="male",a2>60),(0.049*a3)+2.459,"burp"))
    >
    > should work for 5 more, 7 is the limit
    >
    >
    > =IF(AND(A1=1,B1=1),1,IF(AND(A1=2,B1=2),2,IF(AND(A1=3,B1=3),3,IF(AND(A1=4,B1=
    > 4),4,IF(AND(A1=5,B1=5),5,IF(AND(A1=6,B1=6),6,IF(AND(A1=7,B1=7),7,8)))))))
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    >
    > "dodo news" <[email protected]> wrote in message
    > news:[email protected]...
    >> I want to choose one of four formulas to use, conditional on the contents

    > of
    >> two cells
    >>
    >> i.e.
    >> If a1="male" and a2>30, (0.048*a3)+3.653, if a1="male' and a2>60,
    >> (0.049*a3)+2.459, if for two more formulas
    >>
    >> I was trying to do a combined =IF(AND, but this doesn't seem to be
    >> nestable....
    >>
    >> Any help appreciated....
    >> TIA
    >>
    >>

    >
    >




  5. #5
    Tom Ogilvy
    Guest

    Re: nesting for 2 dependents, and 4 formula options

    You had several unpaired parentheses. I removed the paren after B6 since it
    isn't needed - multiplication occurs before addition.
    You alos were missing one in the third IF

    =IF(AND(B3="male",B4>60),(0.049*B6+2.459)/0.004184,
    IF(AND(B3="male",B4>30),(0.048*B6+3.653)/0.004184,
    IF(AND(B3="female",B4>60),(0.038*B6+2.755)/0.004184,
    IF(AND(B3="female",B4>30),(0.048*B6+3.653)/0.004184,"out of range"))))

    --
    Regards,
    Tom Ogilvy


    "dodo news" <[email protected]> wrote in message
    news:[email protected]...
    > Thanks for prompt reply Bob and Tom,
    >
    > i've messed with your syntax and am still having error reports
    > have broken up the formula into lines to make it easier to read.
    > Would greatly appreciate your perusal.
    > TIA
    >
    > =IF(AND(B3="male",B4>60),(0.049*B6)+2.459)/0.004184,
    > IF(AND(B3="male",B4>30),(0.048*B6)+3.653)/0.004184,
    > IF(AND(B3="female",B4>60),(0.038*B6)+2.755/0.004184,
    > IF(AND(B3="female",B4>30),(0.048*B6)+3.653)/0.004184,"out of range")
    >
    >
    >
    >
    >
    >
    >
    >
    > > =If (And(a1="male",a2>30), (0.048*a3)+3.653, if(And(
    > > a1="male",a2>60),(0.049*a3)+2.459,"burp"))
    > >
    > > should work for 5 more, 7 is the limit
    > >
    > >
    > >

    =IF(AND(A1=1,B1=1),1,IF(AND(A1=2,B1=2),2,IF(AND(A1=3,B1=3),3,IF(AND(A1=4,B1=
    > >

    4),4,IF(AND(A1=5,B1=5),5,IF(AND(A1=6,B1=6),6,IF(AND(A1=7,B1=7),7,8)))))))
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > >
    > >
    > > "dodo news" <[email protected]> wrote in message
    > > news:[email protected]...
    > >> I want to choose one of four formulas to use, conditional on the

    contents
    > > of
    > >> two cells
    > >>
    > >> i.e.
    > >> If a1="male" and a2>30, (0.048*a3)+3.653, if a1="male' and a2>60,
    > >> (0.049*a3)+2.459, if for two more formulas
    > >>
    > >> I was trying to do a combined =IF(AND, but this doesn't seem to be
    > >> nestable....
    > >>
    > >> Any help appreciated....
    > >> TIA
    > >>
    > >>

    > >
    > >

    >
    >




  6. #6
    Bruce
    Guest

    Re: nesting for 2 dependents, and 4 formula options

    Thanks very much Tom.
    It is hard to see these things when you aren't looking at them regularly,
    are mentally tired and are unsure of the syntax in the first place.

    The formulae will be put to a great use, and many will benefit freely, so
    please know your time has been well spent.

    Cheers
    Bruce Gray
    Grays Health






    =IF(AND(B3="male",B4>60),(0.049*B6+2.459)/0.004184,
    IF(AND(B3="male",B4>30),(0.048*B6+3.653)/0.004184,
    IF(AND(B3="female",B4>60),(0.038*B6+2.755)/0.004184,
    IF(AND(B3="female",B4>30),(0.034*B6+3.538)/0.004184,"out of range"))))


    > You had several unpaired parentheses. I removed the paren after B6 since
    > it
    > isn't needed - multiplication occurs before addition.
    > You alos were missing one in the third IF
    >
    > =IF(AND(B3="male",B4>60),(0.049*B6+2.459)/0.004184,
    > IF(AND(B3="male",B4>30),(0.048*B6+3.653)/0.004184,
    > IF(AND(B3="female",B4>60),(0.038*B6+2.755)/0.004184,
    > IF(AND(B3="female",B4>30),(0.048*B6+3.653)/0.004184,"out of range"))))
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "dodo news" <[email protected]> wrote in message
    > news:[email protected]...
    >> Thanks for prompt reply Bob and Tom,
    >>
    >> i've messed with your syntax and am still having error reports
    >> have broken up the formula into lines to make it easier to read.
    >> Would greatly appreciate your perusal.
    >> TIA
    >>
    >> =IF(AND(B3="male",B4>60),(0.049*B6)+2.459)/0.004184,
    >> IF(AND(B3="male",B4>30),(0.048*B6)+3.653)/0.004184,
    >> IF(AND(B3="female",B4>60),(0.038*B6)+2.755/0.004184,
    >> IF(AND(B3="female",B4>30),(0.048*B6)+3.653)/0.004184,"out of range")
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >> > =If (And(a1="male",a2>30), (0.048*a3)+3.653, if(And(
    >> > a1="male",a2>60),(0.049*a3)+2.459,"burp"))
    >> >
    >> > should work for 5 more, 7 is the limit
    >> >
    >> >
    >> >

    > =IF(AND(A1=1,B1=1),1,IF(AND(A1=2,B1=2),2,IF(AND(A1=3,B1=3),3,IF(AND(A1=4,B1=
    >> >

    > 4),4,IF(AND(A1=5,B1=5),5,IF(AND(A1=6,B1=6),6,IF(AND(A1=7,B1=7),7,8)))))))
    >> >
    >> > --
    >> > Regards,
    >> > Tom Ogilvy
    >> >
    >> >
    >> >
    >> > "dodo news" <[email protected]> wrote in message
    >> > news:[email protected]...
    >> >> I want to choose one of four formulas to use, conditional on the

    > contents
    >> > of
    >> >> two cells
    >> >>
    >> >> i.e.
    >> >> If a1="male" and a2>30, (0.048*a3)+3.653, if a1="male' and a2>60,
    >> >> (0.049*a3)+2.459, if for two more formulas
    >> >>
    >> >> I was trying to do a combined =IF(AND, but this doesn't seem to be
    >> >> nestable....
    >> >>
    >> >> Any help appreciated....
    >> >> TIA
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >




  7. #7
    Bob Phillips
    Guest

    Re: nesting for 2 dependents, and 4 formula options

    You can actually reduce it

    =IF(OR(AND(B3<>"male",B3<>"female"),B4<=30),"out of range",
    IF(B3="male",IF(B4>60,0.049*B6+2.459,0.048*B6+3.653),
    IF(B4>60,0.038*B6+2.755,0.048*B6+3.653)))/0.004184

    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "Bruce" <[email protected]> wrote in message
    news:[email protected]...
    > Thanks very much Tom.
    > It is hard to see these things when you aren't looking at them regularly,
    > are mentally tired and are unsure of the syntax in the first place.
    >
    > The formulae will be put to a great use, and many will benefit freely, so
    > please know your time has been well spent.
    >
    > Cheers
    > Bruce Gray
    > Grays Health
    >
    >
    >
    >
    >
    >
    > =IF(AND(B3="male",B4>60),(0.049*B6+2.459)/0.004184,
    > IF(AND(B3="male",B4>30),(0.048*B6+3.653)/0.004184,
    > IF(AND(B3="female",B4>60),(0.038*B6+2.755)/0.004184,
    > IF(AND(B3="female",B4>30),(0.034*B6+3.538)/0.004184,"out of range"))))
    >
    >
    > > You had several unpaired parentheses. I removed the paren after B6

    since
    > > it
    > > isn't needed - multiplication occurs before addition.
    > > You alos were missing one in the third IF
    > >
    > > =IF(AND(B3="male",B4>60),(0.049*B6+2.459)/0.004184,
    > > IF(AND(B3="male",B4>30),(0.048*B6+3.653)/0.004184,
    > > IF(AND(B3="female",B4>60),(0.038*B6+2.755)/0.004184,
    > > IF(AND(B3="female",B4>30),(0.048*B6+3.653)/0.004184,"out of range"))))
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > >
    > > "dodo news" <[email protected]> wrote in message
    > > news:[email protected]...
    > >> Thanks for prompt reply Bob and Tom,
    > >>
    > >> i've messed with your syntax and am still having error reports
    > >> have broken up the formula into lines to make it easier to read.
    > >> Would greatly appreciate your perusal.
    > >> TIA
    > >>
    > >> =IF(AND(B3="male",B4>60),(0.049*B6)+2.459)/0.004184,
    > >> IF(AND(B3="male",B4>30),(0.048*B6)+3.653)/0.004184,
    > >> IF(AND(B3="female",B4>60),(0.038*B6)+2.755/0.004184,
    > >> IF(AND(B3="female",B4>30),(0.048*B6)+3.653)/0.004184,"out of range")
    > >>
    > >>
    > >>
    > >>
    > >>
    > >>
    > >>
    > >>
    > >> > =If (And(a1="male",a2>30), (0.048*a3)+3.653, if(And(
    > >> > a1="male",a2>60),(0.049*a3)+2.459,"burp"))
    > >> >
    > >> > should work for 5 more, 7 is the limit
    > >> >
    > >> >
    > >> >

    > >

    =IF(AND(A1=1,B1=1),1,IF(AND(A1=2,B1=2),2,IF(AND(A1=3,B1=3),3,IF(AND(A1=4,B1=
    > >> >

    > >

    4),4,IF(AND(A1=5,B1=5),5,IF(AND(A1=6,B1=6),6,IF(AND(A1=7,B1=7),7,8)))))))
    > >> >
    > >> > --
    > >> > Regards,
    > >> > Tom Ogilvy
    > >> >
    > >> >
    > >> >
    > >> > "dodo news" <[email protected]> wrote in message
    > >> > news:[email protected]...
    > >> >> I want to choose one of four formulas to use, conditional on the

    > > contents
    > >> > of
    > >> >> two cells
    > >> >>
    > >> >> i.e.
    > >> >> If a1="male" and a2>30, (0.048*a3)+3.653, if a1="male' and a2>60,
    > >> >> (0.049*a3)+2.459, if for two more formulas
    > >> >>
    > >> >> I was trying to do a combined =IF(AND, but this doesn't seem to be
    > >> >> nestable....
    > >> >>
    > >> >> Any help appreciated....
    > >> >> TIA
    > >> >>
    > >> >>
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >




  8. #8
    Bruce
    Guest

    Re: nesting for 2 dependents, and 4 formula options

    Thanks a lot Bob. The 7 nest limit is going to be a problem for the whole
    set of formulae i need to put into the cell. And I just got the full set of
    equations now in kcals, so there's no need to do conversion from MJ to
    kcals.

    The end goal is to produce a formula that returns BMR based on the variables
    ***, age, and weight, as per the following table of regression calcs.

    If you can guide me how to create that, I'd be very grateful.

    TIA
    Bruce Gray


    Age BMR: kcal/day
    Years

    Males
    < 3 59.512kg - 30.4
    3-10 22.706kg + 504.3
    10-18 17.686kg + 658.2
    18-30 15.057kg + 692.2
    30-60 11.472kg + 873.1
    ??60 11.711kg + 587.7

    Females
    < 3 58.317kg - 31.1
    3-10 20.315kg + 485.9
    10-18 13.384kg + 692.6
    18-30 14.818kg + 486.6
    30-60 8.126kg + 845.6
    ??60 9.082kg + 658.5





    > You can actually reduce it
    >
    > =IF(OR(AND(B3<>"male",B3<>"female"),B4<=30),"out of range",
    > IF(B3="male",IF(B4>60,0.049*B6+2.459,0.048*B6+3.653),
    > IF(B4>60,0.038*B6+2.755,0.048*B6+3.653)))/0.004184
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (remove nothere from email address if mailing direct)
    >
    > "Bruce" <[email protected]> wrote in message
    > news:[email protected]...
    >> Thanks very much Tom.
    >> It is hard to see these things when you aren't looking at them regularly,
    >> are mentally tired and are unsure of the syntax in the first place.
    >>
    >> The formulae will be put to a great use, and many will benefit freely, so
    >> please know your time has been well spent.
    >>
    >> Cheers
    >> Bruce Gray
    >> Grays Health
    >>
    >>
    >>
    >>
    >>
    >>
    >> =IF(AND(B3="male",B4>60),(0.049*B6+2.459)/0.004184,
    >> IF(AND(B3="male",B4>30),(0.048*B6+3.653)/0.004184,
    >> IF(AND(B3="female",B4>60),(0.038*B6+2.755)/0.004184,
    >> IF(AND(B3="female",B4>30),(0.034*B6+3.538)/0.004184,"out of range"))))
    >>
    >>
    >> > You had several unpaired parentheses. I removed the paren after B6

    > since
    >> > it
    >> > isn't needed - multiplication occurs before addition.
    >> > You alos were missing one in the third IF
    >> >
    >> > =IF(AND(B3="male",B4>60),(0.049*B6+2.459)/0.004184,
    >> > IF(AND(B3="male",B4>30),(0.048*B6+3.653)/0.004184,
    >> > IF(AND(B3="female",B4>60),(0.038*B6+2.755)/0.004184,
    >> > IF(AND(B3="female",B4>30),(0.048*B6+3.653)/0.004184,"out of range"))))
    >> >
    >> > --
    >> > Regards,
    >> > Tom Ogilvy
    >> >
    >> >
    >> > "dodo news" <[email protected]> wrote in message
    >> > news:[email protected]...
    >> >> Thanks for prompt reply Bob and Tom,
    >> >>
    >> >> i've messed with your syntax and am still having error reports
    >> >> have broken up the formula into lines to make it easier to read.
    >> >> Would greatly appreciate your perusal.
    >> >> TIA
    >> >>
    >> >> =IF(AND(B3="male",B4>60),(0.049*B6)+2.459)/0.004184,
    >> >> IF(AND(B3="male",B4>30),(0.048*B6)+3.653)/0.004184,
    >> >> IF(AND(B3="female",B4>60),(0.038*B6)+2.755/0.004184,
    >> >> IF(AND(B3="female",B4>30),(0.048*B6)+3.653)/0.004184,"out of range")
    >> >>
    >> >>
    >> >>
    >> >>
    >> >>
    >> >>
    >> >>
    >> >>
    >> >> > =If (And(a1="male",a2>30), (0.048*a3)+3.653, if(And(
    >> >> > a1="male",a2>60),(0.049*a3)+2.459,"burp"))
    >> >> >
    >> >> > should work for 5 more, 7 is the limit
    >> >> >
    >> >> >
    >> >> >
    >> >

    > =IF(AND(A1=1,B1=1),1,IF(AND(A1=2,B1=2),2,IF(AND(A1=3,B1=3),3,IF(AND(A1=4,B1=
    >> >> >
    >> >

    > 4),4,IF(AND(A1=5,B1=5),5,IF(AND(A1=6,B1=6),6,IF(AND(A1=7,B1=7),7,8)))))))
    >> >> >
    >> >> > --
    >> >> > Regards,
    >> >> > Tom Ogilvy
    >> >> >
    >> >> >
    >> >> >
    >> >> > "dodo news" <[email protected]> wrote in message
    >> >> > news:[email protected]...
    >> >> >> I want to choose one of four formulas to use, conditional on the
    >> > contents
    >> >> > of
    >> >> >> two cells
    >> >> >>
    >> >> >> i.e.
    >> >> >> If a1="male" and a2>30, (0.048*a3)+3.653, if a1="male' and a2>60,
    >> >> >> (0.049*a3)+2.459, if for two more formulas
    >> >> >>
    >> >> >> I was trying to do a combined =IF(AND, but this doesn't seem to
    >> >> >> be
    >> >> >> nestable....
    >> >> >>
    >> >> >> Any help appreciated....
    >> >> >> TIA
    >> >> >>
    >> >> >>
    >> >> >
    >> >> >
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >




  9. #9
    Bob Phillips
    Guest

    Re: nesting for 2 dependents, and 4 formula options

    That sound like a lookup table then Bruce. Take a look at VLOOKUP in help,
    and open a new thread if you get stuck, with the new precise details.

    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "Bruce" <[email protected]> wrote in message
    news:[email protected]...
    > Thanks a lot Bob. The 7 nest limit is going to be a problem for the whole
    > set of formulae i need to put into the cell. And I just got the full set

    of
    > equations now in kcals, so there's no need to do conversion from MJ to
    > kcals.
    >
    > The end goal is to produce a formula that returns BMR based on the

    variables
    > ***, age, and weight, as per the following table of regression calcs.
    >
    > If you can guide me how to create that, I'd be very grateful.
    >
    > TIA
    > Bruce Gray
    >
    >
    > Age BMR: kcal/day
    > Years
    >
    > Males
    > < 3 59.512kg - 30.4
    > 3-10 22.706kg + 504.3
    > 10-18 17.686kg + 658.2
    > 18-30 15.057kg + 692.2
    > 30-60 11.472kg + 873.1
    > ??60 11.711kg + 587.7
    >
    > Females
    > < 3 58.317kg - 31.1
    > 3-10 20.315kg + 485.9
    > 10-18 13.384kg + 692.6
    > 18-30 14.818kg + 486.6
    > 30-60 8.126kg + 845.6
    > ??60 9.082kg + 658.5
    >
    >
    >
    >
    >
    > > You can actually reduce it
    > >
    > > =IF(OR(AND(B3<>"male",B3<>"female"),B4<=30),"out of range",
    > > IF(B3="male",IF(B4>60,0.049*B6+2.459,0.048*B6+3.653),
    > > IF(B4>60,0.038*B6+2.755,0.048*B6+3.653)))/0.004184
    > >
    > > --
    > > HTH
    > >
    > > Bob Phillips
    > >
    > > (remove nothere from email address if mailing direct)
    > >
    > > "Bruce" <[email protected]> wrote in message
    > > news:[email protected]...
    > >> Thanks very much Tom.
    > >> It is hard to see these things when you aren't looking at them

    regularly,
    > >> are mentally tired and are unsure of the syntax in the first place.
    > >>
    > >> The formulae will be put to a great use, and many will benefit freely,

    so
    > >> please know your time has been well spent.
    > >>
    > >> Cheers
    > >> Bruce Gray
    > >> Grays Health
    > >>
    > >>
    > >>
    > >>
    > >>
    > >>
    > >> =IF(AND(B3="male",B4>60),(0.049*B6+2.459)/0.004184,
    > >> IF(AND(B3="male",B4>30),(0.048*B6+3.653)/0.004184,
    > >> IF(AND(B3="female",B4>60),(0.038*B6+2.755)/0.004184,
    > >> IF(AND(B3="female",B4>30),(0.034*B6+3.538)/0.004184,"out of range"))))
    > >>
    > >>
    > >> > You had several unpaired parentheses. I removed the paren after B6

    > > since
    > >> > it
    > >> > isn't needed - multiplication occurs before addition.
    > >> > You alos were missing one in the third IF
    > >> >
    > >> > =IF(AND(B3="male",B4>60),(0.049*B6+2.459)/0.004184,
    > >> > IF(AND(B3="male",B4>30),(0.048*B6+3.653)/0.004184,
    > >> > IF(AND(B3="female",B4>60),(0.038*B6+2.755)/0.004184,
    > >> > IF(AND(B3="female",B4>30),(0.048*B6+3.653)/0.004184,"out of

    range"))))
    > >> >
    > >> > --
    > >> > Regards,
    > >> > Tom Ogilvy
    > >> >
    > >> >
    > >> > "dodo news" <[email protected]> wrote in message
    > >> > news:[email protected]...
    > >> >> Thanks for prompt reply Bob and Tom,
    > >> >>
    > >> >> i've messed with your syntax and am still having error reports
    > >> >> have broken up the formula into lines to make it easier to read.
    > >> >> Would greatly appreciate your perusal.
    > >> >> TIA
    > >> >>
    > >> >> =IF(AND(B3="male",B4>60),(0.049*B6)+2.459)/0.004184,
    > >> >> IF(AND(B3="male",B4>30),(0.048*B6)+3.653)/0.004184,
    > >> >> IF(AND(B3="female",B4>60),(0.038*B6)+2.755/0.004184,
    > >> >> IF(AND(B3="female",B4>30),(0.048*B6)+3.653)/0.004184,"out of range")
    > >> >>
    > >> >>
    > >> >>
    > >> >>
    > >> >>
    > >> >>
    > >> >>
    > >> >>
    > >> >> > =If (And(a1="male",a2>30), (0.048*a3)+3.653, if(And(
    > >> >> > a1="male",a2>60),(0.049*a3)+2.459,"burp"))
    > >> >> >
    > >> >> > should work for 5 more, 7 is the limit
    > >> >> >
    > >> >> >
    > >> >> >
    > >> >

    > >

    =IF(AND(A1=1,B1=1),1,IF(AND(A1=2,B1=2),2,IF(AND(A1=3,B1=3),3,IF(AND(A1=4,B1=
    > >> >> >
    > >> >

    > >

    4),4,IF(AND(A1=5,B1=5),5,IF(AND(A1=6,B1=6),6,IF(AND(A1=7,B1=7),7,8)))))))
    > >> >> >
    > >> >> > --
    > >> >> > Regards,
    > >> >> > Tom Ogilvy
    > >> >> >
    > >> >> >
    > >> >> >
    > >> >> > "dodo news" <[email protected]> wrote in message
    > >> >> > news:[email protected]...
    > >> >> >> I want to choose one of four formulas to use, conditional on the
    > >> > contents
    > >> >> > of
    > >> >> >> two cells
    > >> >> >>
    > >> >> >> i.e.
    > >> >> >> If a1="male" and a2>30, (0.048*a3)+3.653, if a1="male' and

    a2>60,
    > >> >> >> (0.049*a3)+2.459, if for two more formulas
    > >> >> >>
    > >> >> >> I was trying to do a combined =IF(AND, but this doesn't seem to
    > >> >> >> be
    > >> >> >> nestable....
    > >> >> >>
    > >> >> >> Any help appreciated....
    > >> >> >> TIA
    > >> >> >>
    > >> >> >>
    > >> >> >
    > >> >> >
    > >> >>
    > >> >>
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >




  10. #10
    Bruce
    Guest

    Re: nesting for 2 dependents, and 4 formula options

    Thanks Bob. Am familiar with VLOOKUP. Shouldn't have a problem with it once
    I revise the help and my previous work with it.

    Thanks again. It is hard to stay on top of this stuff when I only use it
    once every couple of months.

    Cheers again,
    Bruce Gray



    > That sound like a lookup table then Bruce. Take a look at VLOOKUP in help,
    > and open a new thread if you get stuck, with the new precise details.
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (remove nothere from email address if mailing direct)
    >
    > "Bruce" <[email protected]> wrote in message
    > news:[email protected]...
    >> Thanks a lot Bob. The 7 nest limit is going to be a problem for the whole
    >> set of formulae i need to put into the cell. And I just got the full set

    > of
    >> equations now in kcals, so there's no need to do conversion from MJ to
    >> kcals.
    >>
    >> The end goal is to produce a formula that returns BMR based on the

    > variables
    >> ***, age, and weight, as per the following table of regression calcs.
    >>
    >> If you can guide me how to create that, I'd be very grateful.
    >>
    >> TIA
    >> Bruce Gray
    >>
    >>
    >> Age BMR: kcal/day
    >> Years
    >>
    >> Males
    >> < 3 59.512kg - 30.4
    >> 3-10 22.706kg + 504.3
    >> 10-18 17.686kg + 658.2
    >> 18-30 15.057kg + 692.2
    >> 30-60 11.472kg + 873.1
    >> ??60 11.711kg + 587.7
    >>
    >> Females
    >> < 3 58.317kg - 31.1
    >> 3-10 20.315kg + 485.9
    >> 10-18 13.384kg + 692.6
    >> 18-30 14.818kg + 486.6
    >> 30-60 8.126kg + 845.6
    >> ??60 9.082kg + 658.5
    >>
    >>
    >>
    >>
    >>
    >> > You can actually reduce it
    >> >
    >> > =IF(OR(AND(B3<>"male",B3<>"female"),B4<=30),"out of range",
    >> > IF(B3="male",IF(B4>60,0.049*B6+2.459,0.048*B6+3.653),
    >> > IF(B4>60,0.038*B6+2.755,0.048*B6+3.653)))/0.004184
    >> >
    >> > --
    >> > HTH
    >> >
    >> > Bob Phillips
    >> >
    >> > (remove nothere from email address if mailing direct)
    >> >
    >> > "Bruce" <[email protected]> wrote in message
    >> > news:[email protected]...
    >> >> Thanks very much Tom.
    >> >> It is hard to see these things when you aren't looking at them

    > regularly,
    >> >> are mentally tired and are unsure of the syntax in the first place.
    >> >>
    >> >> The formulae will be put to a great use, and many will benefit freely,

    > so
    >> >> please know your time has been well spent.
    >> >>
    >> >> Cheers
    >> >> Bruce Gray
    >> >> Grays Health
    >> >>
    >> >>
    >> >>
    >> >>
    >> >>
    >> >>
    >> >> =IF(AND(B3="male",B4>60),(0.049*B6+2.459)/0.004184,
    >> >> IF(AND(B3="male",B4>30),(0.048*B6+3.653)/0.004184,
    >> >> IF(AND(B3="female",B4>60),(0.038*B6+2.755)/0.004184,
    >> >> IF(AND(B3="female",B4>30),(0.034*B6+3.538)/0.004184,"out of range"))))
    >> >>
    >> >>
    >> >> > You had several unpaired parentheses. I removed the paren after B6
    >> > since
    >> >> > it
    >> >> > isn't needed - multiplication occurs before addition.
    >> >> > You alos were missing one in the third IF
    >> >> >
    >> >> > =IF(AND(B3="male",B4>60),(0.049*B6+2.459)/0.004184,
    >> >> > IF(AND(B3="male",B4>30),(0.048*B6+3.653)/0.004184,
    >> >> > IF(AND(B3="female",B4>60),(0.038*B6+2.755)/0.004184,
    >> >> > IF(AND(B3="female",B4>30),(0.048*B6+3.653)/0.004184,"out of

    > range"))))
    >> >> >
    >> >> > --
    >> >> > Regards,
    >> >> > Tom Ogilvy
    >> >> >
    >> >> >
    >> >> > "dodo news" <[email protected]> wrote in message
    >> >> > news:[email protected]...
    >> >> >> Thanks for prompt reply Bob and Tom,
    >> >> >>
    >> >> >> i've messed with your syntax and am still having error reports
    >> >> >> have broken up the formula into lines to make it easier to read.
    >> >> >> Would greatly appreciate your perusal.
    >> >> >> TIA
    >> >> >>
    >> >> >> =IF(AND(B3="male",B4>60),(0.049*B6)+2.459)/0.004184,
    >> >> >> IF(AND(B3="male",B4>30),(0.048*B6)+3.653)/0.004184,
    >> >> >> IF(AND(B3="female",B4>60),(0.038*B6)+2.755/0.004184,
    >> >> >> IF(AND(B3="female",B4>30),(0.048*B6)+3.653)/0.004184,"out of
    >> >> >> range")
    >> >> >>
    >> >> >>
    >> >> >>
    >> >> >>
    >> >> >>
    >> >> >>
    >> >> >>
    >> >> >>
    >> >> >> > =If (And(a1="male",a2>30), (0.048*a3)+3.653, if(And(
    >> >> >> > a1="male",a2>60),(0.049*a3)+2.459,"burp"))
    >> >> >> >
    >> >> >> > should work for 5 more, 7 is the limit
    >> >> >> >
    >> >> >> >
    >> >> >> >
    >> >> >
    >> >

    > =IF(AND(A1=1,B1=1),1,IF(AND(A1=2,B1=2),2,IF(AND(A1=3,B1=3),3,IF(AND(A1=4,B1=
    >> >> >> >
    >> >> >
    >> >

    > 4),4,IF(AND(A1=5,B1=5),5,IF(AND(A1=6,B1=6),6,IF(AND(A1=7,B1=7),7,8)))))))
    >> >> >> >
    >> >> >> > --
    >> >> >> > Regards,
    >> >> >> > Tom Ogilvy
    >> >> >> >
    >> >> >> >
    >> >> >> >
    >> >> >> > "dodo news" <[email protected]> wrote in message
    >> >> >> > news:[email protected]...
    >> >> >> >> I want to choose one of four formulas to use, conditional on the
    >> >> > contents
    >> >> >> > of
    >> >> >> >> two cells
    >> >> >> >>
    >> >> >> >> i.e.
    >> >> >> >> If a1="male" and a2>30, (0.048*a3)+3.653, if a1="male' and

    > a2>60,
    >> >> >> >> (0.049*a3)+2.459, if for two more formulas
    >> >> >> >>
    >> >> >> >> I was trying to do a combined =IF(AND, but this doesn't seem
    >> >> >> >> to
    >> >> >> >> be
    >> >> >> >> nestable....
    >> >> >> >>
    >> >> >> >> Any help appreciated....
    >> >> >> >> TIA
    >> >> >> >>
    >> >> >> >>
    >> >> >> >
    >> >> >> >
    >> >> >>
    >> >> >>
    >> >> >
    >> >> >
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >




  11. #11
    Bruce
    Guest

    Re: nesting for 2 dependents, and 4 formula options

    Bob, this is about as dumbed down as I can get it, and it works fine.
    Would have been nice to not rely on an external VLOOKUP table.
    If you can see anyway around it, I am all ears....Otherwise, I'll move onto something else.

    Thanks again.
    Bruce



    Raw Data
    Gender B3
    Age B4
    Weight B6


    =IF(B3="male",VLOOKUP(B4,A31:E36,2)*B6+VLOOKUP(B4,A31:E36,3),VLOOKUP(B4,A31:E36,4)*B6+VLOOKUP(B4,A31:E36,5))


    Age Males Females
    0 59.512 -30.4 58.317 -31.1
    3 22.706 504.3 20.315 485.9
    10 17.686 658.2 13.384 692.6
    18 15.057 692.2 14.818 486.6
    30 11.472 873.1 8.126 845.6
    60 11.711 587.7 9.082 658.5




    > Thanks Bob. Am familiar with VLOOKUP. Shouldn't have a problem with it once
    > I revise the help and my previous work with it.
    >
    > Thanks again. It is hard to stay on top of this stuff when I only use it
    > once every couple of months.
    >
    > Cheers again,
    > Bruce Gray
    >
    >
    >
    >> That sound like a lookup table then Bruce. Take a look at VLOOKUP in help,
    >> and open a new thread if you get stuck, with the new precise details.
    >>
    >> --
    >> HTH
    >>
    >> Bob Phillips
    >>
    >> (remove nothere from email address if mailing direct)
    >>
    >> "Bruce" <[email protected]> wrote in message
    >> news:[email protected]...
    >>> Thanks a lot Bob. The 7 nest limit is going to be a problem for the whole
    >>> set of formulae i need to put into the cell. And I just got the full set

    >> of
    >>> equations now in kcals, so there's no need to do conversion from MJ to
    >>> kcals.
    >>>
    >>> The end goal is to produce a formula that returns BMR based on the

    >> variables
    >>> ***, age, and weight, as per the following table of regression calcs.
    >>>
    >>> If you can guide me how to create that, I'd be very grateful.
    >>>
    >>> TIA
    >>> Bruce Gray
    >>>
    >>>
    >>> Age BMR: kcal/day
    >>> Years
    >>>
    >>> Males
    >>> < 3 59.512kg - 30.4
    >>> 3-10 22.706kg + 504.3
    >>> 10-18 17.686kg + 658.2
    >>> 18-30 15.057kg + 692.2
    >>> 30-60 11.472kg + 873.1
    >>> ??60 11.711kg + 587.7
    >>>
    >>> Females
    >>> < 3 58.317kg - 31.1
    >>> 3-10 20.315kg + 485.9
    >>> 10-18 13.384kg + 692.6
    >>> 18-30 14.818kg + 486.6
    >>> 30-60 8.126kg + 845.6
    >>> ??60 9.082kg + 658.5
    >>>
    >>>
    >>>
    >>>
    >>>
    >>> > You can actually reduce it
    >>> >
    >>> > =IF(OR(AND(B3<>"male",B3<>"female"),B4<=30),"out of range",
    >>> > IF(B3="male",IF(B4>60,0.049*B6+2.459,0.048*B6+3.653),
    >>> > IF(B4>60,0.038*B6+2.755,0.048*B6+3.653)))/0.004184
    >>> >
    >>> > --
    >>> > HTH
    >>> >
    >>> > Bob Phillips
    >>> >
    >>> > (remove nothere from email address if mailing direct)
    >>> >
    >>> > "Bruce" <[email protected]> wrote in message
    >>> > news:[email protected]...
    >>> >> Thanks very much Tom.
    >>> >> It is hard to see these things when you aren't looking at them

    >> regularly,
    >>> >> are mentally tired and are unsure of the syntax in the first place.
    >>> >>
    >>> >> The formulae will be put to a great use, and many will benefit freely,

    >> so
    >>> >> please know your time has been well spent.
    >>> >>
    >>> >> Cheers
    >>> >> Bruce Gray
    >>> >> Grays Health
    >>> >>
    >>> >>
    >>> >>
    >>> >>
    >>> >>
    >>> >>
    >>> >> =IF(AND(B3="male",B4>60),(0.049*B6+2.459)/0.004184,
    >>> >> IF(AND(B3="male",B4>30),(0.048*B6+3.653)/0.004184,
    >>> >> IF(AND(B3="female",B4>60),(0.038*B6+2.755)/0.004184,
    >>> >> IF(AND(B3="female",B4>30),(0.034*B6+3.538)/0.004184,"out of range"))))
    >>> >>
    >>> >>
    >>> >> > You had several unpaired parentheses. I removed the paren after B6
    >>> > since
    >>> >> > it
    >>> >> > isn't needed - multiplication occurs before addition.
    >>> >> > You alos were missing one in the third IF
    >>> >> >
    >>> >> > =IF(AND(B3="male",B4>60),(0.049*B6+2.459)/0.004184,
    >>> >> > IF(AND(B3="male",B4>30),(0.048*B6+3.653)/0.004184,
    >>> >> > IF(AND(B3="female",B4>60),(0.038*B6+2.755)/0.004184,
    >>> >> > IF(AND(B3="female",B4>30),(0.048*B6+3.653)/0.004184,"out of

    >> range"))))
    >>> >> >
    >>> >> > --
    >>> >> > Regards,
    >>> >> > Tom Ogilvy
    >>> >> >
    >>> >> >
    >>> >> > "dodo news" <[email protected]> wrote in message
    >>> >> > news:[email protected]...
    >>> >> >> Thanks for prompt reply Bob and Tom,
    >>> >> >>
    >>> >> >> i've messed with your syntax and am still having error reports
    >>> >> >> have broken up the formula into lines to make it easier to read.
    >>> >> >> Would greatly appreciate your perusal.
    >>> >> >> TIA
    >>> >> >>
    >>> >> >> =IF(AND(B3="male",B4>60),(0.049*B6)+2.459)/0.004184,
    >>> >> >> IF(AND(B3="male",B4>30),(0.048*B6)+3.653)/0.004184,
    >>> >> >> IF(AND(B3="female",B4>60),(0.038*B6)+2.755/0.004184,
    >>> >> >> IF(AND(B3="female",B4>30),(0.048*B6)+3.653)/0.004184,"out of
    >>> >> >> range")
    >>> >> >>
    >>> >> >>
    >>> >> >>
    >>> >> >>
    >>> >> >>
    >>> >> >>
    >>> >> >>
    >>> >> >>
    >>> >> >> > =If (And(a1="male",a2>30), (0.048*a3)+3.653, if(And(
    >>> >> >> > a1="male",a2>60),(0.049*a3)+2.459,"burp"))
    >>> >> >> >
    >>> >> >> > should work for 5 more, 7 is the limit
    >>> >> >> >
    >>> >> >> >
    >>> >> >> >
    >>> >> >
    >>> >

    >> =IF(AND(A1=1,B1=1),1,IF(AND(A1=2,B1=2),2,IF(AND(A1=3,B1=3),3,IF(AND(A1=4,B1=
    >>> >> >> >
    >>> >> >
    >>> >

    >> 4),4,IF(AND(A1=5,B1=5),5,IF(AND(A1=6,B1=6),6,IF(AND(A1=7,B1=7),7,8)))))))
    >>> >> >> >
    >>> >> >> > --
    >>> >> >> > Regards,
    >>> >> >> > Tom Ogilvy
    >>> >> >> >
    >>> >> >> >
    >>> >> >> >
    >>> >> >> > "dodo news" <[email protected]> wrote in message
    >>> >> >> > news:[email protected]...
    >>> >> >> >> I want to choose one of four formulas to use, conditional on the
    >>> >> > contents
    >>> >> >> > of
    >>> >> >> >> two cells
    >>> >> >> >>
    >>> >> >> >> i.e.
    >>> >> >> >> If a1="male" and a2>30, (0.048*a3)+3.653, if a1="male' and

    >> a2>60,
    >>> >> >> >> (0.049*a3)+2.459, if for two more formulas
    >>> >> >> >>
    >>> >> >> >> I was trying to do a combined =IF(AND, but this doesn't seem
    >>> >> >> >> to
    >>> >> >> >> be
    >>> >> >> >> nestable....
    >>> >> >> >>
    >>> >> >> >> Any help appreciated....
    >>> >> >> >> TIA
    >>> >> >> >>
    >>> >> >> >>
    >>> >> >> >
    >>> >> >> >
    >>> >> >>
    >>> >> >>
    >>> >> >
    >>> >> >
    >>> >>
    >>> >>
    >>> >
    >>> >
    >>>
    >>>

    >>
    >>

    >
    >


  12. #12
    Bob Phillips
    Guest

    Re: nesting for 2 dependents, and 4 formula options

    You could use

    =VLOOKUP(B4,A31:E36,2+(B3="female")*2)*B6+VLOOKUP(B4,A31:E36,3+(B3="female")
    *2)

    or if you must dispense with the external table, use

    =VLOOKUP(B4,{0,59.512,58.317;3,22.706,20.315;10,17.686,13.384;18,15.057,14.8
    18;30,11.472,8.126;60,11.711,9.082},2+(B3="female"))*B6
    +VLOOKUP(B4,{0,-30.4,-31.1;3,504.3,485.9;10,658.2,692.6;18,692.2,486.6;30,87
    3.1,845.6;60,587.7,658.5},2+(B3="female"))

    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "Bruce" <[email protected]> wrote in message
    news:[email protected]...
    Bob, this is about as dumbed down as I can get it, and it works fine.
    Would have been nice to not rely on an external VLOOKUP table.
    If you can see anyway around it, I am all ears....Otherwise, I'll move onto
    something else.

    Thanks again.
    Bruce



    Raw Data
    Gender B3
    Age B4
    Weight B6


    =IF(B3="male",VLOOKUP(B4,A31:E36,2)*B6+VLOOKUP(B4,A31:E36,3),VLOOKUP(B4,A31:
    E36,4)*B6+VLOOKUP(B4,A31:E36,5))


    Age Males Females
    0 59.512 -30.4 58.317 -31.1
    3 22.706 504.3 20.315 485.9
    10 17.686 658.2 13.384 692.6
    18 15.057 692.2 14.818 486.6
    30 11.472 873.1 8.126 845.6
    60 11.711 587.7 9.082 658.5







  13. #13
    Bruce
    Guest

    Re: nesting for 2 dependents, and 4 formula options

    Bob,

    Outstanding advice.
    I never knew VLOOKUP could integrate table data into the formula.

    Kudos++

    Cheers
    Bruce Gray



    > You could use
    >
    > =VLOOKUP(B4,A31:E36,2+(B3="female")*2)*B6+VLOOKUP(B4,A31:E36,3+(B3="female")
    > *2)
    >
    > or if you must dispense with the external table, use
    >
    > =VLOOKUP(B4,{0,59.512,58.317;3,22.706,20.315;10,17.686,13.384;18,15.057,14.8
    > 18;30,11.472,8.126;60,11.711,9.082},2+(B3="female"))*B6
    > +VLOOKUP(B4,{0,-30.4,-31.1;3,504.3,485.9;10,658.2,692.6;18,692.2,486.6;30,87
    > 3.1,845.6;60,587.7,658.5},2+(B3="female"))
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (remove nothere from email address if mailing direct)
    >
    > "Bruce" <[email protected]> wrote in message
    > news:[email protected]...
    > Bob, this is about as dumbed down as I can get it, and it works fine.
    > Would have been nice to not rely on an external VLOOKUP table.
    > If you can see anyway around it, I am all ears....Otherwise, I'll move
    > onto
    > something else.
    >
    > Thanks again.
    > Bruce
    >
    >
    >
    > Raw Data
    > Gender B3
    > Age B4
    > Weight B6
    >
    >
    > =IF(B3="male",VLOOKUP(B4,A31:E36,2)*B6+VLOOKUP(B4,A31:E36,3),VLOOKUP(B4,A31:
    > E36,4)*B6+VLOOKUP(B4,A31:E36,5))
    >
    >
    > Age Males Females
    > 0 59.512 -30.4 58.317 -31.1
    > 3 22.706 504.3 20.315 485.9
    > 10 17.686 658.2 13.384 692.6
    > 18 15.057 692.2 14.818 486.6
    > 30 11.472 873.1 8.126 845.6
    > 60 11.711 587.7 9.082 658.5
    >
    >
    >
    >
    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1