Combining Two Independent Formulas in One Cell

[bern75]

At my job we have our employees that have to take a fitness and body fat test occasionally. I have built a spreadsheet that calculates all the information I need but I am having trouble with a cell that is supposed to grade their fitness score ONLY if they pass the body fat score. I am going to try to not make this post too long but I want to ensure I try to explain exactly what is happening.

1) The formula below labeled “Fitness Score” works just fine by assigning a value of 0-7 based upon their score.

Fitness Score: =IF(A2<180,0, IF(A2<205,1, IF(A2<230,2, IF(A2<255,3, IF(A2<280,4, IF(A2<300,5, IF(A2>=300,7)))))))

2) The next function, labeled body fat function, is supposed to allow the fitness Score Function to pass through if the person enters ‘P’ for pass in the Body Fat cell and enter a zero is the person enters ‘F’ for failing the body fat or there is nothing in the cell at all. I put 4 ‘?’ marks to annotate that the function is not complete and I am not sure what goes there. I am thinking that the fitness function should somehow go in the section where the 4 ?’s are but I’m not sure how to squeeze it in.

In F10 the graders will enter a 'F' or 'P' for failing the body fat scale
=​IF(​B2=​"",​0,​IF(​B2=​"F",​0,​IF(​B2=​"P",​????)​))​

Example:
Scoring their fitness test by entering the score in cell A2. They get a score of 1-7 depending on where their fitness score lands in the bracket. The reason there is no '6' is because a perfect score gets a 2 point jump. Then the person enters ‘P’ or ‘N’ if they pass the body fat.
- If they pass the body fat and score a 207, then Excel should put a 1 in cell C2.
- If they fail the body fat and score a 207, then Excel should put a 0 in cell C2.
- If nothing gets entered then a 0 should be put in cell C2.

 

[ask2tsp]

you can simplify the first formula a bit because the last 'IF((A2>=300,7)' is in fact the 'ELSE' part of the IF that contains it, so you can replace it with '7'.

Next copy this formula (without the '=') and paste it in place of the four question marks.

 

[Alphonse68]

Or you could just replace the ???? with the address of the cell where the first formula is.

 

[Eric W]

You can actually shorten both formulas:

	A	B	C
P

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]207[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(B2="P",A3,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A3[/TH]
[TD="align: left"]=LOOKUP(A2,{0,180,205,230,255,280,300},{0,1,2,3,4,5,7})[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[bern75]

This worked, thanks