Formula for calculating

[John 4348]

I want to calculate students'scores in excel form. Who can help me?
Formula is: (A2+2*B2+C2+4*D2)/8
Scores are from 0 to 20,but for absent students score is 97 but not calculated for them.
If A2=97 is or B2=97 or C2=97or D2, how I can formulate so that 97 is not calculated?

 

[Fluff]

Maybe

=(A2*(A2<>97)+(2*B2)*(B2<>97)+C2*(C2<>97)+(4*D2)*(D2<>97))/(8-COUNTIFS(A2:D2,97)-(B2=97)-(3*(D2=97)))

If this does not give the correct answers, then you will need to explain exactly what the criteria are.

 

[John 4348]

Maybe

=(A2*(A2<>97)+(2*B2)*(B2<>97)+C2*(C2<>97)+(4*D2)*(D2<>97))/(8-COUNTIFS(A2:D2,97)-(B2=97)-(3*(D2=97)))

If this does not give the correct answers, then you will need to explain exactly what the criteria are.

The criteria is 97 not calculated in the row. Supposing 97 is in the coulmn A2, the formula is( 2*B2+C2+4*D2)/7. If 97 is in the coulmn B2, the formula is ( A2+C2+4*D2)/6 and so on.

 

[Fluff]

Did the formula work?

 

[John 4348]

Did the formula work?

Yes. Thanks a lot.

Maybe

=(A2*(A2<>97)+(2*B2)*(B2<>97)+C2*(C2<>97)+(4*D2)*(D2<>97))/(8-COUNTIFS(A2:D2,97)-(B2=97)-(3*(D2=97)))

If this does not give the correct answers, then you will need to explain exactly what the criteria are.

Hi
I apologize you.
If I want to use this formula for another sheet that has only 3 columns ( A2+2*B2+C2)/4 with the criteria number 97 which is not calculated. Could you please help me?
Thanks a lot

 

[Fluff]

How about

=(A2*(A2<>97)+(2*B2)*(B2<>97)+C2*(C2<>97))/(4-COUNTIFS(A2:C2,97)-(B2=97))

 

[John 4348]

How about

=(A2*(A2<>97)+(2*B2)*(B2<>97)+C2*(C2<>97))/(4-COUNTIFS(A2:C2,97)-(B2=97))

Hi
Excellent.
Thanks a lot

 

[Fluff]

Glad to help & thanks for the feedback.