Average 3 to 5 numbers

[i43]

I had used this forum before when you had helped set up the excel sheet that I will upload. We now have close to 300 candidates on the list but have run into a problem.

We usually had 3 interviewers as seen on sheet 3, however, now for some candidates I have 4 or 5 interviewers. In that case I need to average the 4 or 5 interviewers & have the next candidate start beneath that. I think the sheet 3 is programmed to space every 4 cells then display the average for the next candidate. How can I go about integrating it to skip 5 spaces if there were 4 interviewers & skip 6 spaces if there were 5 interviewers?

For example for Candidate 6 on Sheet 3, I want the average of 12 to be displayed on H28 instead of H27. For Candidate 7 I need the average to be displayed on H34 without any numbers in between. But if Candidate 8 has only 3 interviewers then it should only skin 4 cells & then display the average in the subsequent cell. Does that make sense?

I have uploaded the sheet.

Thank you SO much for your help!

[NBVC]

Try, in H4:

=IF(B4="",AVERAGE(INDEX(G$2:G3,MATCH(2,INDEX(1/(B$2:B3=""),0))+1):G3),"")

copied down

[i43]

NVBC thanks for replying. That code worked well but now I have another problem. I used sheet 3 data in sheet 1, but now sheet 1 isn't working.

Sheet1 column F pulls the number from Sheet3 column H &
sheet 1 column G pulls the number from sheet 3 column L
PLUS it makes sure that the candidates are the same (i.e. candidate 1 in sheet 1 corresponds to candidate 1 in sheet 3)

But after I added that code, candidates 6 & 7 column F & G are blank. How would I fix this?

Thank you!

[i43]

I wanted to attach the excel sheet with the updated info for you to see.

[NBVC]

Try:

Please Login or Register  to view this content.

[i43]

hi again. now I have another question on the similar sheet. How do I alter the formula to average from 1 - 5 interviewers on sheet 3 rather than a set 3-5 and get it to display in the row below?

In the sample sheet attached for example, in the FIT category in sheet 1 from G12 to G11 it should display the error that is seen in sheet 3 (#DIV/0!) b/c I didn't put any value in that FIT column in sheet 3. Then on G17 should be the value 0, then the rest of it until G24 should be the error. Then the last G25 should be 20 but it displays 20 for all the rest above it. Could someone help me figure this out?

Thank you so much for your help!

[i43]

any suggestions or ideas anyone please? thanks again for trying!

[NBVC]

You never did acknowledge the suggested solution a year ago....

Anyway, try:

=IF($A7="","",INDEX(INDEX(Sheet3!L$4:L$400,MATCH(A7,Sheet3!$A$4:$A$400,0)):Sheet3!L$400,MATCH(1,INDEX((ISNUMBER(INDEX(Sheet3!L$4:L$400,MATCH(A7,Sheet3!$A$4:$A$400,0)):Sheet3!L$400)+ISERROR(INDEX(Sheet3!L$4:L$400,MATCH(A7,Sheet3!$A$4:$A$400,0)):Sheet3!L$400)),0),0),0))

you will need to format the column as Custom with Type: 0;-0;0;@ for the 0's to appear.

[i43]

THANK YOU SO MUCH!!!! It worked! Thank you! You guys are absolutely amazing!