Logic Function Help…

[thomasstyron]

Hello:

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

I am working on a body fat calculator, and one of the components o
this calculator is the gender of the individual. I have a drop dow
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 fa
incorporated together.

They are as follows:
1. Body Density (BD): 1.109380 - [0.0008267 x (SKF)] + [0.0000016
(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 displaye
in is formatted for percent.

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

Unfortunately, this formula is applicable for males only. For females
the constants are changed, but the formula remains essentially th
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 functio
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 measurement
and cell A2 is the age.

My expectation was that if Male is selected from the pull down menu i
cell B2, then the first formula is used. If not then the latter formul
for Females is used. Unfortunately I have been disappointed and hav
become frustrated. The functio
(=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)")i
displayed in the cell B9 rather than the computed value.

I have used “ “, [ ], for the separation of values; and I have trie
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 Styro

 

[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)

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 mak
sure the LinkedCell is B

 

[thomasstyron]

Thank you for your responses Tommy and Martin (and to all others wh
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 I
False formula to work right). So, I just cleared the cell, started fro
scratch, selected the IF logic function next to the function editor.
then used B2="Male” for the Logical Test, and I copied the formula
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
copy of this spreadsheet, please let me know. I am modeling it afte
the calculations section in the Bowflex training manual, but usin
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,
Results section (before and after measurements) and a nomogram (th
problem we just worked on); taking three measurements and using you
age you can compute your percent body fat. The nomogram is pretty bi
though as it is a spreadsheet with 10 to 150 mm in the i direction an
15 to 50 years old for the j direction. I like the single cell formul
which depends on other cells input. It’s cleaner too.

Well, until next time…
Best Regards,
Thomas
(e-mail address removed)