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