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?

 

[John 4348]

Is this what you want

+Fluff 1.xlsm
	A	B	C	D	E
1
2	97	16.5	18	14.5	13.625
3	19	97	18	16	12.625
4	17	14	97	16	13.625
Lists

Cell Formulas
Range	Formula
E2:E4	E2	=(A2*(A2<>97)+(2*B2)*(B2<>97)+C2*(C2<>97)+(4*D2)*(D2<>97))/8

Hi
No, 97 is one of the scores so must not calculated. I mean the scores in first row ( 109/7= 15.57). I calculated by a calculator.
Anyway, thanks.

 

[Fluff]

In that case how about

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

 

[John 4348]

In that case how about

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

The first and third row is corrected, but the second row is not.
Thanks a lot

 

[Fluff]

In what way?

 

[John 4348]

In what way?

rows 4&6 are incorrected.

 

[Fluff]

That does not really help. If the answers are wrong you need to explain why they are wrong & what they should be.

 

[John 4348]

That does not really help. If the answers are wrong you need to explain why they are wrong & what they should be.

In calculating, 97 must not calculated like the third & fifth rows.

 

[Fluff]

What are the correct answers?

 

[John 4348]

That does not really help. If the answers are wrong you need to explain why they are wrong & what they should be.

In calculating, 97 must not calculated like the third & fifth rows.

 

[John 4348]

What are the correct answers?

20