How to hide a value of a formula in a formula cell

0
votes

I have looked around for this but cant find an answer im sure its easy enough so maby im wording my searches incorrectly.

I have made a BMR calculator in excel, it can calculate both male and female i have a drop down to select male or female.

The calculation is the same apart from one part. A male adds 5 at the end and a female takes away 161.

Male: BMR = (10 × Weight in Kilograms (kg)) + (6.25 × Height in Centimetres (cm) - (5 × Age in Years) + 5

Female: BMR = (10 × Weight in Kilograms (kg)) + (6.25 × Height in Centimetres (cm) - (5 × Age in Years) - 161

My formula to calculate the above is:

=IF(A5="Male",(10*B5)+(6.25*C5)-(5*D5)+(5),(10*B5)+(6.25*C5)-(5*D5)-(161))

problem is when selecting either it will display 5 or -161 in the cell until you input data in the relevant cells.

How can i get the cell to remain blank until date is input rather than showing the last part of the equation ?

Example view: enter image description here

1
excelexcel-formulaformula
Just add some IF first to check if the relevant cells needed in calculations are blank or not. If they are blank, then return "". If not, then return your calculation - Foxfire And Burns And Burns
Thanks! appreciate the response. - Andrew Walker

1 Answers

2
votes

If you want to check if all your inputs are filled a simple if statement should work. Although if you use the output of this formula as input in other formulas expect error messages.

Just add/remove cell comparisons from the OR function to match your needs.

=IF(OR(A5="",B5="",C5="",D5=""),"",IF(A5="Male",(10*B5)+(6.25*C5)-(5*D5)+(5),(10*B5)+(6.25*C5)-(5*D5)-(161)))