Logic Function Help…

[thomasstyron]

Hello:

I am trying to incorporate an IF Logic function into my worksheet but it doesn’t seem to work.

I am working on a body fat calculator, and one of the components of this calculator is the gender of the individual. I have a drop down menu in a cell, with Male and Female for choices. This is in cell B2.

In cell B9, I have the formulas for Body Density and Body fat incorporated together.

They are as follows:
1. Body Density (BD): 1.109380 - [0.0008267 x (SKF)] + [0.0000016 x (SKF)2] - (0.0002574 x Age)
2. Body Fat (in percent): [(4.95/Db) - 4.5] x 100
(From Jackson & Pollock (1978))

Written for Excel, I have condensed it to:
((4.95/(1.10938-(0.0008267*B8)+(0.0000016*((B8)^2))-(0.0002574*A2)))-4.5)

I omitted the *100 at the end, as the cell that the result is displayed in is formatted for percent.

This formula works great, with cell B8 being the sum of three measurements (in mm) on the body and cell A2 being the age. The results are displayed as a percent.

Unfortunately, this formula is applicable for males only. For females, the constants are changed, but the formula remains essentially the same.

The female formula is:
((5.01/(1.0994921-(0.0009929*B8)+(0.0000023*((B8)^2))-(0.0001392*A2)))-4.57)
(Derived from the formulas of Jackson et al., (1980))
This formula too works well.

Now I am trying to incorporate the two functions into one function using an IF Logic; this is where I hit a snag.

I have come to here but without success:

=IF(B2=Male,"((5.01/(1.0994921-(0.0009929*B8)+(0.0000023*((B8)^2))-(0.0001392*A2)))-4.57))","((4.95/(1.10938-(0.0008267*B8)+(0.0000016*((B8)^2))-(0.0002574*A2)))-4.5)")

This is in cell B9; again cell B8 is the sum of three body measurements and cell A2 is the age.

My expectation was that if Male is selected from the pull down menu in cell B2, then the first formula is used. If not then the latter formula for Females is used. Unfortunately I have been disappointed and have become frustrated. The function (=IF(B2=Male,"((5.01/(1.0994921-(0.0009929*B8)+(0.0000023*((B8)^2))-(0.0001392*A2)))-4.57))","((4.95/(1.10938-(0.0008267*B8)+(0.0000016*((B8)^2))-(0.0002574*A2)))-4.5)")is displayed in the cell B9 rather than the computed value.

I have used “ “, [ ], for the separation of values; and I have tried substituting 1 and 2 for Male and Female (no indicator of superiority) to simplify matters.

If anyone can offer help in this matter I would greatly appreciate it. Thank you for your time.

Best Regards,
Thomas Styron

[TommySzalapski]

First change

=IF(B2=Male,"((5.01/(1.0994921-(0.0009929*B8)+(0.0000023*((B8)^2))-(0.0001392*A2)))-4.57))","((4.95/(1.10938-(0.0008267*B8)+(0.0000016*((B8)^2))-(0.0002574*A2)))-4.5)")

to

=IF(B2="Male",((5.01/(1.0994921-(0.0009929*B8)+(0.0000023*((B8)^2))-(0.0001392*A2)))-4.57)),((4.95/(1.10938-(0.0008267*B8)+(0.0000016*((B8)^2))-(0.0002574*A2)))-4.5))

(removed quote marks and put them around the text string)

next right click on your drop down box and click "properties" and make sure the LinkedCell is B2

[Martin P]

I think you should split it up into the sum of two IF functions:
if(B2="male",expression1,0)+if(B2="female",expression2,0)

"thomasstyron" wrote:

>
> Hello:
>
> I am trying to incorporate an IF Logic function into my worksheet but
> it doesn’t seem to work.
>
> I am working on a body fat calculator, and one of the components of
> this calculator is the gender of the individual. I have a drop down
> menu in a cell, with Male and Female for choices. This is in cell B2.
>
> In cell B9, I have the formulas for Body Density and Body fat
> incorporated together.
>
> They are as follows:
> 1. Body Density (BD): 1.109380 - [0.0008267 x (SKF)] + [0.0000016 x
> (SKF)2] - (0.0002574 x Age)
> 2. Body Fat (in percent): [(4.95/Db) - 4.5] x 100
> (From Jackson & Pollock (1978))
>
> Written for Excel, I have condensed it to:
> ((4.95/(1.10938-(0.0008267*B8)+(0.0000016*((B8)^2))-(0.0002574*A2)))-4.5)
>
> I omitted the *100 at the end, as the cell that the result is displayed
> in is formatted for percent.
>
> This formula works great, with cell B8 being the sum of three
> measurements (in mm) on the body and cell A2 being the age. The results
> are displayed as a percent.
>
> Unfortunately, this formula is applicable for males only. For females,
> the constants are changed, but the formula remains essentially the
> same.
>
> The female formula is:
> ((5.01/(1.0994921-(0.0009929*B8)+(0.0000023*((B8)^2))-(0.0001392*A2)))-4.57)
> (Derived from the formulas of Jackson et al., (1980))
> This formula too works well.
>
> Now I am trying to incorporate the two functions into one function
> using an IF Logic; this is where I hit a snag.
>
> I have come to here but without success:
>
> =IF(B2=Male,"((5.01/(1.0994921-(0.0009929*B8)+(0.0000023*((B8)^2))-(0.0001392*A2)))-4.57))","((4.95/(1.10938-(0.0008267*B8)+(0.0000016*((B8)^2))-(0.0002574*A2)))-4.5)")
>
> This is in cell B9; again cell B8 is the sum of three body measurements
> and cell A2 is the age.
>
> My expectation was that if Male is selected from the pull down menu in
> cell B2, then the first formula is used. If not then the latter formula
> for Females is used. Unfortunately I have been disappointed and have
> become frustrated. The function
> (=IF(B2=Male,"((5.01/(1.0994921-(0.0009929*B8)+(0.0000023*((B8)^2))-(0.0001392*A2)))-4.57))","((4.95/(1.10938-(0.0008267*B8)+(0.0000016*((B8)^2))-(0.0002574*A2)))-4.5)")is
> displayed in the cell B9 rather than the computed value.
>
> I have used “ “, [ ], for the separation of values; and I have tried
> substituting 1 and 2 for Male and Female (no indicator of superiority)
> to simplify matters.
>
> If anyone can offer help in this matter I would greatly appreciate it.
> Thank you for your time.
>
> Best Regards,
> Thomas Styron
>
>
> --
> thomasstyron
> ------------------------------------------------------------------------
> thomasstyron's Profile: http://www.excelforum.com/member.php...o&userid=25568
> View this thread: http://www.excelforum.com/showthread...hreadid=389991
>
>

[thomasstyron]

Thank you for your responses Tommy and Martin (and to all others who were working on a solution for me).

I actually tried Tommy's suggestion first with good results. Of course, I did have a problem but it was my fault (I couldn't get the Value If False formula to work right). So, I just cleared the cell, started from scratch, selected the IF logic function next to the function editor. I then used B2="Male” for the Logical Test, and I copied the formulas from my sheet where I worked them out, and viola! Success!

Thank you again for your help. I do appreciate it. And if you want a copy of this spreadsheet, please let me know. I am modeling it after the calculations section in the Bowflex training manual, but using excel for the computations in case you want to see a "what if" scenario. It's kinda' neat. Right now it has Body Mass Calculations, a Results section (before and after measurements) and a nomogram (the problem we just worked on); taking three measurements and using your age you can compute your percent body fat. The nomogram is pretty big though as it is a spreadsheet with 10 to 150 mm in the i direction and 15 to 50 years old for the j direction. I like the single cell formula which depends on other cells input. It’s cleaner too.

Well, until next time…
Best Regards,
Thomas
[email protected]