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

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

"thomasstyron" wrote:


Hello:

I am trying to incorporate an IF Logic function into my worksheet but
it doesnt 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

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


--
TommySzalapski
------------------------------------------------------------------------
TommySzalapski's Profile: http://www.excelforum.com/member.php...o&userid=25561
View this thread: http://www.excelforum.com/showthread...hreadid=389991

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



--
thomasstyron
------------------------------------------------------------------------
thomasstyron's Profile: http://www.excelforum.com/member.php...o&userid=25568
View this thread: http://www.excelforum.com/showthread...hreadid=389991