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?

 

[kweaver]

Welcome to Mr. Excel
Please use XL2BB to illustrate your data and expected results.

 

[John 4348]

Welcome to Mr. Excel
Please use XL2BB to illustrate your data and expected results.

Unfortunately, XL2BB doesn't open on my PC.

 

[John 4348]

Welcome to Mr. Excel
Please use XL2BB to illustrate your data and expected results.

 

[John 4348]

 

[John 4348]

Hi
I wrote this Formula but doesn't work correctly.
= If(counting(A2:C2,97)=2,0,if(countif(A2:C2,97)=1, averageifs (A2:C2,A2:C2,"<>97"),(A2+2*B2+C2)/4))

 

[scoudny]

Hi John

Just want to check the first formula =(A2+2*B2+C2+4*D2)/8 as my results are coming out completely different from what you are getting on your demo.
Your second formula needs counting changed to countif.

 

[John 4348]

Hi John

Just want to check the first formula =(A2+2*B2+C2+4*D2)/8 as my results are coming out completely different from what you are getting on your demo.
Your second formula needs counting changed to countif.

Hi Scoudny
Yes, the first formula works correctly.
But I need to correct the second formula. When 97 is one of the scores.
Could you please correct it?
Thanks a lot

 

[John 4348]

Hi John

Just want to check the first formula =(A2+2*B2+C2+4*D2)/8 as my results are coming out completely different from what you are getting on your demo.
Your second formula needs counting changed to countif.

Hi Scoudny
Yes, the first formula works correctly.
But I need to correct the second formula. When 97 is one of the scores.
Could you please correct it?
I changed to "countif", but doesn't work correctly.
Thanks a lot

 

[Fluff]

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