Need help in excel

[csKanna]

I'm using Excel 97 and the formula below is no problem, but is fix to one exam taken.

If each students taken more than 1 exam and maximum is 8 exam, then i have to change the formula manually.

Need your advise is there any formula can work flexible no matter is 1 exam, 2 exam or even 8 exam taken also will work out the same?

=IF(SUM(D10:G10)=4,25+SUM(J10:K10)*0.5+SUM(L10:N10)*1+SUM(O10:R10)*0.8+SUM(S10:U10)*3.2+SUM(V10:Y10) *0.6666666667,0)/100

For the above formula is working but fix to 1 exam taken only.

The concept is once one of the fatal elements is 0 (1 is pass, 0 is fail), then the overall score is zero.

If fatal elements are all pass, but some non-fatal elements not pass, then the overall score will calculate the sum of the fatal and non-fatal score.

my problem is on fatal calculation -- " SUM(D10:G10)=4,25", others non-fatal calculation is ok.

Need your expertise thought to assist as i really run out of idea. Thanks.
The formula that in the file is no problem, but is fix to one exam taken.

If each students taken more than 1 exam and maximum is 8 exam, then i have to change the formula manually.

Need your advise is that any formula can work flexible no matter is 1 exam or 2 exam taken also will work out the same?

=IF(SUM(D10:G10)=4,25+SUM(J10:K10)*0.5+SUM(L10:N10)*1+SUM(O10:R10)*0.8+SUM(S10:U10)*3.2+SUM(V10:Y10) *0.6666666667,0)/100

For the above formula is working but fix to 1 exam taken only.

The concept is once one of the fatal elements is 0 (1 is pass, 0 is fail), then the overall score is zero.

If fatal elements are all pass, but some non-fatal elements not pass, then the overall score will calculate the sum of the fatal and non-fatal score.

my problem is on fatal calculation -- " SUM(D10:G10)=4,25", others non-fatal calculation is ok.

Need your expertise thought to assist as i really run out of idea.

I can send you the file if i had confused you with my bad grammar.

Thanks.
I'm using Excel 97 and the formula below is no problem, but is fix to one exam taken.

If each students taken more than 1 exam and maximum is 8 exam, then i have to change the formula manually.

Need your advise is there any formula can work flexible no matter is 1 exam, 2 exam or even 8 exam taken also will work out the same?

=IF(SUM(D10:G10)=4,25+SUM(J10:K10)*0.5+SUM(L10:N10)*1+SUM(O10:R10)*0.8+SUM(S10:U10)*3.2+SUM(V10:Y10) *0.6666666667,0)/100

For the above formula is working but fix to 1 exam taken only.

The concept is once one of the fatal elements is 0 (1 is pass, 0 is fail), then the overall score is zero.

If fatal elements are all pass, but some non-fatal elements not pass, then the overall score will calculate the sum of the fatal and non-fatal score.

my problem is on fatal calculation -- " SUM(D10:G10)=4,25", others non-fatal calculation is ok.

Need your expertise thought to assist as i really run out of idea. Thanks.
The formula that in the file is no problem, but is fix to one exam taken.

If each students taken more than 1 exam and maximum is 8 exam, then i have to change the formula manually.

Need your advise is that any formula can work flexible no matter is 1 exam or 2 exam taken also will work out the same?

=IF(SUM(D10:G10)=4,25+SUM(J10:K10)*0.5+SUM(L10:N10)*1+SUM(O10:R10)*0.8+SUM(S10:U10)*3.2+SUM(V10:Y10) *0.6666666667,0)/100

For the above formula is working but fix to 1 exam taken only.

The concept is once one of the fatal elements is 0 (1 is pass, 0 is fail), then the overall score is zero.

If fatal elements are all pass, but some non-fatal elements not pass, then the overall score will calculate the sum of the fatal and non-fatal score.

my problem is on fatal calculation -- " SUM(D10:G10)=4,25", others non-fatal calculation is ok.

[Ecniv]

Do you have an example sheet to see how you are storing the exams ?

Can you use a pivot table or something?
Resort the list? Then perform calcs for each exam then total the passes for the exam taker?

Also - make sure your post is only the one question and not repeated

[Webtest]

csKanna:

Here is a function that will tell you whether the scores in D10:G10 Fail or not. It will accept Blank cells, or numerals 1 or 0. If any cell has a "0" in it, it is an automatic failure. If all 4 cells contain only a combination of "1" or Blank characters, it will display a "Calc Score" message. You can use the value of this cell to determine whether or not to calculate the score in another cell. Do you need help with that? It only takes a simple "IF" statement to do that.

=IF(OR(IF(ISBLANK(D10),FALSE,IF(CODE(D10)=48,TRUE,FALSE)),IF(ISBLANK(E10),FALSE,IF(CODE(E10)=48,TRUE,FALSE)),IF(ISBLANK(F10),FALSE,IF(CODE(F10)=48,TRUE,FALSE)),IF(ISBLANK(G10),FALSE,IF(CODE(G10)=48,TRUE,FALSE))),"FAIL","Calc Score")