What am I doing wrong? I keep getting the error - #value!"

[wackynutty]

Hello-
Can anyone tell me what I am doing wrong?
cell c2 = 62
cell c6 = 0

(cell c2 represents female's height in inches, cell c6 represents a male's height in inches)
(if c2 has a value >0, then c6 will = 0 and vice versa, because only one person's height will be entered)

There are 4 formulas used to determine a persons ideal body weight
Formula #1 - used for women under 5 feet. If x= height in inches, the formula is : 105-(60-x)*5
Formula #2 - used for women 5 feet and over. If x= height in inches, the formula is : 105+(x-60)*5
Formula #3 - used for men under 5 feet. If x= height in inches, the formula is : 106-(60-x)*6
Formula #4 - used for men 5 feet and over. If x= height in inches, the formula is : 106(x-60)*6

The cell I am working on is to calculate ideal body weight. This is what i have, but it doesn't work. I have tried all sorts of variations on the parentheses. Can anyone tell me what the problem is? I have little computer and excel experience. thanks in advance for any help.

=IF(AND(C2<60,C6<=0),(105-(60-C2)*5)), IF(AND(C2>=60,C6<=0),((C2-60)*5+105), IF(AND(C6<60,C2<=0),((106-(60-C6)*6)),IF(AND(C6>=60,C2<=0),((C6-60)*6+106),(("**")))))

[daddylonglegs]

I would use this version where 1,2,3 and 4 represent your 4 different calculations

=IF(C2>=60,1,IF(C2>0,2,IF(C6>=60,3,IF(C6>0,4,"**"))))

so you can insert those like this:

=IF(C2>=60,(C2-60)*5+105,IF(C2>0,105-(60-C2)*5,IF(C6>=60,(C6-60)*6+106,IF(C6>0,106-(60-C6)*6,"**"))))

[wackynutty]

I would use this version where 1,2,3 and 4 represent your 4 different calculations

=IF(C2>=60,1,IF(C2>0,2,IF(C6>=60,3,IF(C6>0,4,"**"))))

so you can insert those like this:

=IF(C2>=60,(C2-60)*5+105,IF(C2>0,105-(60-C2)*5,IF(C6>=60,(C6-60)*6+106,IF(C6>0,106-(60-C6)*6,"**"))))

Thank you, this works.
However, is there a way I can modify the formula so it wont show any values if a number >0 are in both c2 and c6? This would be to safeguard against the potential problem of -

Person enters a weight in female field,
then says "oops, he's a man", and
they re-enter the weight in the male field, but
they are too lazy, or forget to delete number in female field.

In such a case, the wrong result will be displayed in the ideal body weight field.
Thanks!

[daddylonglegs]

Try including that check first, e.g.

=IF(AND(C2>0,C6>0),"Error",IF(C2>=60,(C2-60)*5+105,IF(C2>0,105-(60-C2)*5,IF(C6>=60,(C6-60)*6+106,IF(C6>0,106-(60-C6)*6,"**")))))

[wackynutty]

Thanks! I appreciate your help.