CODEWITHSUNDEEP
excelif-statementexcel-formula
jparnell8839
jparnell8839

Reputation: 530

Attempting an Excel IF statement with a range of possible outcomes

OK, so I'm trying to create an excel fitness tracker that calculates body fat percentages from the measurements that are input. That part is good and works fine, and I have it set so that if there is no data in the dependent cells, it displays N/A. I then have a cell that I put a formula in as follows:

=IF(C28<=5%,"Body Builder", IF(C28<13%,"Athletic", IF(C28<17%,"Fit", IF(C28<25%,"Average", IF(C28>=26%,"Obese","No Data Yet")))))

Essentially, I want it to set so that if N/A is displayed in cell C28, it states "No Data Yet". As it stands, if there's nothing there, it displays "Obese".

The rest of the formula works awesomely. I just dont know why it's throwing Obese in a non numeric value :/

Any help?

Upvotes: 2

Views: 3423

Answers (2)

Ross Larson
Ross Larson

Reputation: 2437

may I suggest using a vlookup for clarity?

=iferror(vlookup(C28, BodyFatTable, 2, True), "No Data Yet")

here is what table would look like with two columns (adjust numbers to represent the LOWEST body fat % for each level (they need to be in order)) and then name this table BodyFatTable to have a nice named range in your formula

  0    Body Builder
.05    Athletic
.13    Fit
.17    Average
.25    Obese

Upvotes: 1

lnafziger
lnafziger

Reputation: 25740

Just add another IF to your statement to check for N/A:

=IF(ISNA(C28),"No Data Yet", IF(C28<=5%,"Body Builder", IF(C28<13%,"Athletic", IF(C28<17%,"Fit", IF(C28<25%,"Average", IF(C28>=26%,"Obese","No Data Yet"))))))

Upvotes: 0

Related Questions