Scoresheet - show scores without allocating points or ranking that competitor

I have a scoring spreadsheet set up to input scores, calculate percentage, rank competitors and allocate points accordingly, however I wish to exclude competitors who have a Y in column B from receiving points and being ranked for a placing. I tried the "IF" function but cant get the syntax correct.

I used

=IF(AND(ISNUMBER(Q7),B7=""),RANK(Q7,Q$1:$Q$23),"")

in the event placing column but it doesn't exclude them from the ranking it just doesn't return a value. Any help would be most appreaciated.

Rider No HC Rider Horse Club CL Judge SL Judge Test % Placing Points CL Judge SL Judge Test % Placing Points Combined Points Event Placing Test Ave Test 1 Test 2
Class 3 - Primary Novice 2.1 2.2
5245 Y xx xx xx 121 44.81% 8 23 126 48.46% 5 26 49.00 46.64% 270 260 HC
6016 Y xx xx xx 122 45.19% 7 24 111 42.69% 8 23 47.00 43.94% 270 260 HC
5448 xx xx xx 143 52.96% 5 26 123 47.31% 6 25 51.00 #VALUE! 50.14% 270 260
5834 Y xx xx xx 140 51.85% 6 25 141 54.23% 3 28 53.00 53.04% 270 260 HC
6047 Y xx xx xx 151 55.93% 2 29 142 54.62% 2 29 58.00 55.27% 270 260 HC
5448 xx xx xx 149 55.19% 4 27 119 45.77% 7 24 51.00 #VALUE! 50.48% 270 260
5245 xx xx xx 150 55.56% 3 28 131 50.38% 4 27 55.00 #VALUE! 52.97% 270 260
0.00% 0 0.00% 0 0.00 #VALUE! 0.00% 270 260
y 155 57.41% #VALUE! #VALUE! 155 59.62% 1 30 #VALUE! 58.51% 270 260
0.00% 0 0.00% 0 0.00 0.00% 270 260

Hopefully this will be a little clearer and will help show what I want to do

thank you

In column R I still need to place the remaining competitors in

so it should end up looking like this:

thanks, I have it worked out now. ties will be sorted by the Average Test Score.

I really appreciate your quick response. I was really struggling.

Regards


Annette