Formula Help - Vlookup and Match?

[Bigtime]

Hi,

I can't figure out how to go about writing a formula for this.

Here's a link to my sample editable spreadsheet in Onedrive:
https://1drv.ms/x/s!AjE7rOJOD5SS0A56EdcLfmKnJeMO

At the top is some sample data collected from students taking a pre and post fitness assessment. If they did both the pre and the post, they receive 25 points. If they did only one, they receive 0 points. I also need to get the average between the pre and post test.

Any help is much appreciated!

Thanks,

Tim

 

[etaf]

for the points you can use
=IF(COUNTIFS($F$2:$F$6,A12,$A$2:$A$6,"pre")+COUNTIFS($F$2:$F$6,A12,$A$2:$A$6,"post")=2,25,IF(COUNTIFS($F$2:$F$6,A12,$A$2:$A$6,"pre")+COUNTIFS($F$2:$F$6,A12,A$2:$A$6,"post")=1,0,0))
BUT that can be simplified , as i assume if they have done none , they wont be on the list
so change to
=IF(COUNTIFS($F$2:$F$6,A12,$A$2:$A$6,"pre")+COUNTIFS($F$2:$F$6,A12,$A$2:$A$6,"post")=2,25,0)

for the crunches , only applies if they have done a pre and a post so use
=IF(COUNTIFS($F$2:$F$6,A12,$A$2:$A$6,"pre")+COUNTIFS($F$2:$F$6,A12,$A$2:$A$6,"post")=2,AVERAGEIFS($H$2:$H$6,$F$2:$F$6,A12),"")

 

[Bigtime]

Thank you very much! Worked perfectly!!!

 

[Anand Sharma]

you can also use sumproduct here to shorten you code e.g.

=IF((SUMPRODUCT(--($A$1:$A$5="Pre"),--($C$1:$C$5=A12))+SUMPRODUCT(--($A$1:$A$5="Post"),--($C$1:$C$5=A12)))>1,25,0)

 

[Bigtime]

Thank you Anand!!

 

[Bigtime]

How would I integrate into the formula a way to calculate % increase from pre to post test on the crunches?

 

[etaf]

i would use
=(INDEX($H$2:$H$6,MATCH("pre"&A12,$A$2:$A$6&$F$2:$F$6,0))-INDEX($H$2:$H$6,MATCH("post"&A12,$A$2:$A$6&$F$2:$F$6,0)))/INDEX($H$2:$H$6,MATCH("pre"&A12,$A$2:$A$6&$F$2:$F$6,0))
and enter using Control + Shift + Enter to get {} around the formula

this is the number on Pre - the number on Post divided by the number on Pre

OR
to avoid using an array formula {}
you can just use this
=((LOOKUP(2,1/($A$2:$A$6="pre")/($F$2:$F$6=A12),$H$2:$H$6))-(LOOKUP(2,1/($F$2:$F$6=A12)/($F$2:$F$6=A12),$H$2:$H$6)))/(LOOKUP(2,1/($A$2:$A$6="pre")/($F$2:$F$6=A12),$H$2:$H$6))
as is

 

[Bigtime]

Thank you!!!!!!!

 

[etaf]

you are welcome