| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 a230, (0.048*a3)+3.653, if a1="male' and a260, (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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
nesting for 2 dependents, and 4 formula options
=IF(AND(A1="male",A230), (0.048*A3)+3.653,
if(AND(A1="male",A260),(0.049*a3)+2.459, IF(...)))) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "dodo news" wrote in message ... I want to choose one of four formulas to use, conditional on the contents of two cells i.e. If a1="male" and a230, (0.048*a3)+3.653, if a1="male' and a260, (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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
nesting for 2 dependents, and 4 formula options
=If (And(a1="male",a230), (0.048*a3)+3.653, if(And(
a1="male",a260),(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(AN D(A1=7,B1=7),7,8))))))) -- Regards, Tom Ogilvy "dodo news" wrote in message ... I want to choose one of four formulas to use, conditional on the contents of two cells i.e. If a1="male" and a230, (0.048*a3)+3.653, if a1="male' and a260, (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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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",B460),(0.049*B6)+2.459)/0.004184, IF(AND(B3="male",B430),(0.048*B6)+3.653)/0.004184, IF(AND(B3="female",B460),(0.038*B6)+2.755/0.004184, IF(AND(B3="female",B430),(0.048*B6)+3.653)/0.004184,"out of range") =If (And(a1="male",a230), (0.048*a3)+3.653, if(And( a1="male",a260),(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(AN D(A1=7,B1=7),7,8))))))) -- Regards, Tom Ogilvy "dodo news" wrote in message ... I want to choose one of four formulas to use, conditional on the contents of two cells i.e. If a1="male" and a230, (0.048*a3)+3.653, if a1="male' and a260, (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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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",B460),(0.049*B6+2.459)/0.004184, IF(AND(B3="male",B430),(0.048*B6+3.653)/0.004184, IF(AND(B3="female",B460),(0.038*B6+2.755)/0.004184, IF(AND(B3="female",B430),(0.048*B6+3.653)/0.004184,"out of range")))) -- Regards, Tom Ogilvy "dodo news" wrote in message ... 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",B460),(0.049*B6)+2.459)/0.004184, IF(AND(B3="male",B430),(0.048*B6)+3.653)/0.004184, IF(AND(B3="female",B460),(0.038*B6)+2.755/0.004184, IF(AND(B3="female",B430),(0.048*B6)+3.653)/0.004184,"out of range") =If (And(a1="male",a230), (0.048*a3)+3.653, if(And( a1="male",a260),(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(AN D(A1=7,B1=7),7,8))))))) -- Regards, Tom Ogilvy "dodo news" wrote in message ... I want to choose one of four formulas to use, conditional on the contents of two cells i.e. If a1="male" and a230, (0.048*a3)+3.653, if a1="male' and a260, (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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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",B460),(0.049*B6+2.459)/0.004184, IF(AND(B3="male",B430),(0.048*B6+3.653)/0.004184, IF(AND(B3="female",B460),(0.038*B6+2.755)/0.004184, IF(AND(B3="female",B430),(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",B460),(0.049*B6+2.459)/0.004184, IF(AND(B3="male",B430),(0.048*B6+3.653)/0.004184, IF(AND(B3="female",B460),(0.038*B6+2.755)/0.004184, IF(AND(B3="female",B430),(0.048*B6+3.653)/0.004184,"out of range")))) -- Regards, Tom Ogilvy "dodo news" wrote in message ... 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",B460),(0.049*B6)+2.459)/0.004184, IF(AND(B3="male",B430),(0.048*B6)+3.653)/0.004184, IF(AND(B3="female",B460),(0.038*B6)+2.755/0.004184, IF(AND(B3="female",B430),(0.048*B6)+3.653)/0.004184,"out of range") =If (And(a1="male",a230), (0.048*a3)+3.653, if(And( a1="male",a260),(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(AN D(A1=7,B1=7),7,8))))))) -- Regards, Tom Ogilvy "dodo news" wrote in message ... I want to choose one of four formulas to use, conditional on the contents of two cells i.e. If a1="male" and a230, (0.048*a3)+3.653, if a1="male' and a260, (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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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(B460,0.049*B6+2.459,0.048*B6+3.65 3), IF(B460,0.038*B6+2.755,0.048*B6+3.653)))/0.004184 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bruce" wrote in message ... 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",B460),(0.049*B6+2.459)/0.004184, IF(AND(B3="male",B430),(0.048*B6+3.653)/0.004184, IF(AND(B3="female",B460),(0.038*B6+2.755)/0.004184, IF(AND(B3="female",B430),(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",B460),(0.049*B6+2.459)/0.004184, IF(AND(B3="male",B430),(0.048*B6+3.653)/0.004184, IF(AND(B3="female",B460),(0.038*B6+2.755)/0.004184, IF(AND(B3="female",B430),(0.048*B6+3.653)/0.004184,"out of range")))) -- Regards, Tom Ogilvy "dodo news" wrote in message ... 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",B460),(0.049*B6)+2.459)/0.004184, IF(AND(B3="male",B430),(0.048*B6)+3.653)/0.004184, IF(AND(B3="female",B460),(0.038*B6)+2.755/0.004184, IF(AND(B3="female",B430),(0.048*B6)+3.653)/0.004184,"out of range") =If (And(a1="male",a230), (0.048*a3)+3.653, if(And( a1="male",a260),(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(AN D(A1=7,B1=7),7,8))))))) -- Regards, Tom Ogilvy "dodo news" wrote in message ... I want to choose one of four formulas to use, conditional on the contents of two cells i.e. If a1="male" and a230, (0.048*a3)+3.653, if a1="male' and a260, (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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 sex, 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(B460,0.049*B6+2.459,0.048*B6+3.65 3), IF(B460,0.038*B6+2.755,0.048*B6+3.653)))/0.004184 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bruce" wrote in message ... 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",B460),(0.049*B6+2.459)/0.004184, IF(AND(B3="male",B430),(0.048*B6+3.653)/0.004184, IF(AND(B3="female",B460),(0.038*B6+2.755)/0.004184, IF(AND(B3="female",B430),(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",B460),(0.049*B6+2.459)/0.004184, IF(AND(B3="male",B430),(0.048*B6+3.653)/0.004184, IF(AND(B3="female",B460),(0.038*B6+2.755)/0.004184, IF(AND(B3="female",B430),(0.048*B6+3.653)/0.004184,"out of range")))) -- Regards, Tom Ogilvy "dodo news" wrote in message ... 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",B460),(0.049*B6)+2.459)/0.004184, IF(AND(B3="male",B430),(0.048*B6)+3.653)/0.004184, IF(AND(B3="female",B460),(0.038*B6)+2.755/0.004184, IF(AND(B3="female",B430),(0.048*B6)+3.653)/0.004184,"out of range") =If (And(a1="male",a230), (0.048*a3)+3.653, if(And( a1="male",a260),(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(AN D(A1=7,B1=7),7,8))))))) -- Regards, Tom Ogilvy "dodo news" wrote in message ... I want to choose one of four formulas to use, conditional on the contents of two cells i.e. If a1="male" and a230, (0.048*a3)+3.653, if a1="male' and a260, (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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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" wrote in message ... 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 sex, 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(B460,0.049*B6+2.459,0.048*B6+3.65 3), IF(B460,0.038*B6+2.755,0.048*B6+3.653)))/0.004184 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bruce" wrote in message ... 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",B460),(0.049*B6+2.459)/0.004184, IF(AND(B3="male",B430),(0.048*B6+3.653)/0.004184, IF(AND(B3="female",B460),(0.038*B6+2.755)/0.004184, IF(AND(B3="female",B430),(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",B460),(0.049*B6+2.459)/0.004184, IF(AND(B3="male",B430),(0.048*B6+3.653)/0.004184, IF(AND(B3="female",B460),(0.038*B6+2.755)/0.004184, IF(AND(B3="female",B430),(0.048*B6+3.653)/0.004184,"out of range")))) -- Regards, Tom Ogilvy "dodo news" wrote in message ... 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",B460),(0.049*B6)+2.459)/0.004184, IF(AND(B3="male",B430),(0.048*B6)+3.653)/0.004184, IF(AND(B3="female",B460),(0.038*B6)+2.755/0.004184, IF(AND(B3="female",B430),(0.048*B6)+3.653)/0.004184,"out of range") =If (And(a1="male",a230), (0.048*a3)+3.653, if(And( a1="male",a260),(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(AN D(A1=7,B1=7),7,8))))))) -- Regards, Tom Ogilvy "dodo news" wrote in message ... I want to choose one of four formulas to use, conditional on the contents of two cells i.e. If a1="male" and a230, (0.048*a3)+3.653, if a1="male' and a260, (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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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" wrote in message ... 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 sex, 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(B460,0.049*B6+2.459,0.048*B6+3.65 3), IF(B460,0.038*B6+2.755,0.048*B6+3.653)))/0.004184 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bruce" wrote in message ... 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",B460),(0.049*B6+2.459)/0.004184, IF(AND(B3="male",B430),(0.048*B6+3.653)/0.004184, IF(AND(B3="female",B460),(0.038*B6+2.755)/0.004184, IF(AND(B3="female",B430),(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",B460),(0.049*B6+2.459)/0.004184, IF(AND(B3="male",B430),(0.048*B6+3.653)/0.004184, IF(AND(B3="female",B460),(0.038*B6+2.755)/0.004184, IF(AND(B3="female",B430),(0.048*B6+3.653)/0.004184,"out of range")))) -- Regards, Tom Ogilvy "dodo news" wrote in message ... 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",B460),(0.049*B6)+2.459)/0.004184, IF(AND(B3="male",B430),(0.048*B6)+3.653)/0.004184, IF(AND(B3="female",B460),(0.038*B6)+2.755/0.004184, IF(AND(B3="female",B430),(0.048*B6)+3.653)/0.004184,"out of range") =If (And(a1="male",a230), (0.048*a3)+3.653, if(And( a1="male",a260),(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(AN D(A1=7,B1=7),7,8))))))) -- Regards, Tom Ogilvy "dodo news" wrote in message ... I want to choose one of four formulas to use, conditional on the contents of two cells i.e. If a1="male" and a230, (0.048*a3)+3.653, if a1="male' and a260, (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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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" wrote in message ... 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 sex, 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(B460,0.049*B6+2.459,0.048*B6+3.65 3), IF(B460,0.038*B6+2.755,0.048*B6+3.653)))/0.004184 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Bruce" wrote in message ... 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",B460),(0.049*B6+2.459)/0.004184, IF(AND(B3="male",B430),(0.048*B6+3.653)/0.004184, IF(AND(B3="female",B460),(0.038*B6+2.755)/0.004184, IF(AND(B3="female",B430),(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",B460),(0.049*B6+2.459)/0.004184, IF(AND(B3="male",B430),(0.048*B6+3.653)/0.004184, IF(AND(B3="female",B460),(0.038*B6+2.755)/0.004184, IF(AND(B3="female",B430),(0.048*B6+3.653)/0.004184,"out of range")))) -- Regards, Tom Ogilvy "dodo news" wrote in message ... 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",B460),(0.049*B6)+2.459)/0.004184, IF(AND(B3="male",B430),(0.048*B6)+3.653)/0.004184, IF(AND(B3="female",B460),(0.038*B6)+2.755/0.004184, IF(AND(B3="female",B430),(0.048*B6)+3.653)/0.004184,"out of range") =If (And(a1="male",a230), (0.048*a3)+3.653, if(And( a1="male",a260),(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(AN D(A1=7,B1=7),7,8))))))) -- Regards, Tom Ogilvy "dodo news" wrote in message ... I want to choose one of four formulas to use, conditional on the contents of two cells i.e. If a1="male" and a230, (0.048*a3)+3.653, if a1="male' and a260, (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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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;3 0,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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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;3 0,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" wrote in message ... 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| nesting if formula | Excel Discussion (Misc queries) | |||
| Nesting of more than 7 in an IF formula | Excel Worksheet Functions | |||
| CORREL formula work on multiple dependents | Excel Worksheet Functions | |||
| Can you help nesting a formula? | Excel Worksheet Functions | |||
| NESTING FORMULA | Excel Worksheet Functions | |||
Linear Mode
