I'm trying to create a body fat% calculator (cells B19/C19) that will measure in both metric and imperial (cell B9) depending whether cell B10 is male or female.
I've got the imperial calculation to work just fine, but the metric keeps coming up with 'formula parse error' - not only that, but even when I separate the male/female calculations for the metric, the male is correct but the female isn't. It's way off what my scientific calculator tells me for the same equation (-68 instead of 32)
I also want to make it so BF% (B19) is dependent on the metrics(B9) instead of having two results. I think a dependent dropdown will be needed here which is fine, but I first need to understand why the formula doesn't work?
Here is the link to my spreadsheet:
https://docs.google.com/spreadsheets/d/14KNBTghqpt4BF6FAgdqNQKEp634E3h3JdScgKISZ4XE/edit?usp=sharing
Height = B11
Neck = B12
Navel = B13
Hip = B14
Here are the raw equations for BF%:
Body fat % formula for males:
Imperial Units:
BF% = 86.010×log10(navel-neck) - 70.041×log10(height) + 36.76
Metric Units:
BF% = 495/1.0324 - 0.19077×log10(navel-neck) ) + 0.15456×log10(height)-450
Body fat % formula for females:
Imperial Units:
BF% = 163.205×log10(navel+hip-neck) - 97.684×(log10(height)) - 78.387
Metric Units:
BF% = 495/1.29579 - 0.35004×log10(waist+hip-neck) + 0.22100×log10(height)-450
To clarify, I currently have the formulas set up as such:
Imperial
=IF(B10="Male",86.01*LOG10(B13-B12)-70.041*LOG10(B11)+36.76,163.205*LOG10(B13+B14-B12)-97.684*LOG10(B11)-78.387)
Metric
=IF(B10="Male",495/1.0324-0.19077*log10(B13-B12))+0.15456*LOG10(B11)-450,495/1.29579-0.35004*LOG10(B13+B14-B12)+0.22100*LOG10(B11)-450
Use either
=IF(B10="Male",
495/(1.0324-0.19077*log10(B13-B12)+0.15456*LOG10(B11))-450,
495/(1.29579-0.35004*LOG10(B13+B14-B12)+0.221*LOG10(B11))-450
)
or
=IF(B10="Male",
86.01*LOG10((B13-B12)/2.54)-70.041*LOG10(B11/2.54)+36.76,
163.205*LOG10((B13+B14-B12)/2.54)-97.684*LOG10(B11/2.54)-78.387
)