Please help

[bingolover99]

I have a spreadsheet with results of two class exams and coursework for each pupil in my class. A few of the pupils were absent so therefore instead of a value 0 for there mark, they have been given abs. therefore obviously, when i use the fill function to work out the percentage for each pupils, the pupils who were absent were given a #VALUE for their grades. is there anyway i can overcome this? I want to keep the value of abs in the table, but need to give it a 0 value. Can anyone help me please??

 

[attc]

sorry, just missed Yogi's reply. quick question: why is the =SUM( ... ) necessary?

 

[Yogi Anand]

or how about the following array based formula ...

'{=SUM(IF(ISNUMBER(M1:M3),M1:M3)*(N1:N3))}

where N1:N3 house 0.15,0.15, and 0.70 -- I believe these are your weightage factors.

Regards!

Yogi Anand

 

[Yogi Anand]

On 2002-10-26 13:13, attc wrote:
sorry, just missed Yogi's reply. quick question: why is the =SUM( ... ) necessary?

Hi attc:

Use of SUM in the formula:
'=SUM(IF(ISNUMBER(M1),M1*0.15)+(IF(ISNUMBER(M2),M2*0.15)+(IF(ISNUMBER(M3),M3*0.7)))))

is redundant and not necessary ... You are right! It was my oversight ... proper usage would be

'=(IF(ISNUMBER(M1),M1*0.15)+(IF(ISNUMBER(M2),M2*0.15)+(IF(ISNUMBER(M3),M3*0.7))))

Regards!

Yogi Anand

 

[bingolover99]

REGARDING the query from Aladin Akyurek OF WHETHER 0 IS EVER GIVEN AS A SCORE. It can be. but only if the pupil did actually gain 0 in the exam. If they were just absent, the mark is marked as absent.

I attempted to use the coding suggested earlier, but i get strange values.

Thanks for everyones solutions to my problem. Much appreciated!!

 

[Aladin Akyurek]

On 2002-10-26 20:36, bingolover99 wrote:
REGARDING the query from Aladin Akyurek OF WHETHER 0 IS EVER GIVEN AS A SCORE. It can be. but only if the pupil did actually gain 0 in the exam. If they were just absent, the mark is marked as absent.

I attempted to use the coding suggested earlier, but i get strange values.

Thanks for everyones solutions to my problem. Much appreciated!!

If 0 was not possible as score, a simple formula would suffice.

Two additional options...

(1)

In N1 enter:

=IF(ISNUMBER(M1),0.15*M1,0)

In N2 enter:

=IF(ISNUMBER(M2),0.15*M2,0)

In N3 enter:

=IF(ISNUMBER(M3),0.70*M3,0)

Then use

=SUM(N1:N3)

for the final grade/score.

(2)

Array-enter:

=SUM(IF(ISNUMBER(M1:M3),M1:M3*{0.15;0.15;0.7}))

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.
This message was edited by aladin akyurek on 2002-10-27 01:04

 

[bingolover99]

i still cant get it working Im going to bed. ill have another go in the morning.

 

[bingolover99]

Finally i got the code working! Thanks very much BUT ive got another aspect to take into consideration. Some of the pupils were excused from the exam and therefore have 'exc' as there mark. exc carries a weigthing behind the mark, unlike abs where abs equals 0.
eg

=SUM(IF(ISNUMBER(M1),M1*0.15)+(IF(ISNUMBER(M2),M2*0.15)+(IF(ISNUMBER(M3),M3*0.7))))*1/(1-W)

where w is either 0.15 or 0.7 depending on which mosule of the work is missed. How can i incorporate this into the coding??
This message was edited by bingolover99 on 2002-10-27 05:32
This message was edited by bingolover99 on 2002-10-27 05:33
This message was edited by bingolover99 on 2002-10-27 05:39

 

[attc]

AA - I liked the array formula. veery niice!

bingolover-- you pretty much answered your own question, didn't you?!

Method 1:
-----------
using the array formula approach (I'm new to these, but I have tested):

=SUM(IF(ISNUMBER(M1:M3),M1:M3*{0.15;0.15;0.7}))/SUM(IF((M1:M3)<>"exc",{0.15;0.15;0.7},0)
)

Method 2: (non-array approach)
--------------------------------
=(IF(ISNUMBER(M1),M1*0.15)+IF(ISNUMBER(M2),M2*0.15)+IF(ISNUMBER(M3),M3*0.7)) / (IF(M1<>"exc",0.15,0) + IF(M2<>"exc",0.15,0)+IF(M3<>"exc",0.70,0))

Assumptions:
--------------
If I'm absent, I'm marked "abs" and I score a value of 0. so 100 / 100 / abs would score as 66.67

If I'm excused, I'm marked "exc" and that module doesn't count towards the score. so 100 / 100 / exc would score 100.


Hmm... better have a rigorous policy on excuses. Lots of scope for naughty students to beat the system!

 

[bingolover99]

On 2002-10-27 07:29, attc wrote:
AA - I liked the array formula. veery niice!

bingolover-- you pretty much answered your own question, didn't you?!

Method 1:
-----------
using the array formula approach (I'm new to these, but I have tested):

=SUM(IF(ISNUMBER(M1:M3),M1:M3*{0.15;0.15;0.7}))/SUM(IF((M1:M3)<>"exc",{0.15;0.15;0.7},0)
)

Method 2: (non-array approach)
--------------------------------
=(IF(ISNUMBER(M1),M1*0.15)+IF(ISNUMBER(M2),M2*0.15)+IF(ISNUMBER(M3),M3*0.7)) / (IF(M1<>"exc",0.15,0) + IF(M2<>"exc",0.15,0)+IF(M3<>"exc",0.70,0))

Assumptions:
--------------
If I'm absent, I'm marked "abs" and I score a value of 0. so 100 / 100 / abs would score as 66.67

If I'm excused, I'm marked "exc" and that module doesn't count towards the score. so 100 / 100 / exc would score 100.


Hmm... better have a rigorous policy on excuses. Lots of scope for naughty students to beat the system!

Yeah ur assumptions are correct!! Just cant seem to get the coding working. I aint too good with the coding in excel, so if u could just explain what the
"(IF(M1<>"exc",0.15,0) + IF(M2<>"exc",0.15,0)+IF(M3<>"exc",0.70,0))"
part actually means. esp the <>!
Thanks in advance

 

[attc]

The "<>" just means "NOT EQUAL TO" (isn't that in the HELP somewhere?)

So the idea is:

(1) Add up M1, M2 and M3 but ignore if they are not numbers (i.e. are "abs" or "exc") 'cos I can't add text:

=(IF(ISNUMBER(M1),M1*0.15)+IF(ISNUMBER(M2),M2*0.15)+IF(ISNUMBER(M3),M3*0.7))

(2) Divide by the total weight of the modules that were not excused (ie. where <>"exc")

/(IF(M1<>"exc",0.15,0) + IF(M2<>"exc",0.15,0)+IF(M3<>"exc",0.70,0))

Of course let's say your marks are in cells A2, B2 and C2 for your first student. Then M1 should be replaced by A2, M2 should be replaced by B2 and M3 should be replaced by C2.

You can then fill down for the rest of your students.

If you still can't get it work, let us know which cells your data is in for one student, then perhaps someone can give you the code to copy/paste if you're lucky :wink: