Please help

bingolover99

New Member
Joined
Oct 25, 2002
Messages
20
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??
 
Hi bingolover:

So, are you having fun or are you getting frustrated ... Well, based on three possible conditions, 1)student has a score in a module, 2)was absent, or 3)was excused, I have the following formula

'=IF(OR(ISNUMBER(M1),M1="exc"),MIN(M1,100)*0.15,0)+IF(OR(ISNUMBER(M2),M2="exc"),MIN(M2,100)*0.15,0)+IF(OR(ISNUMBER(M3),M3="exc"),MIN(M3,100)*0.7,0)

I have also considered several cases of possible test score cases and I have shown those in different colors in the following simulation.
y021027h1.xls
KLMN
10.1510085.0
20.15abs
30.70exc
4
59083.5
6abs
7exc
8
9abs83.5
1090
11exc
12
13100100.0
14100
15100
16
17exc100.0
18exc
19exc
20
21abs0.0
22abs
23abs
Sheet1
</SPAN>

Regards!

Yogi Anand
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
I misread your coding. You actually multiply the marks gained in the modules completed by

1/1-W

WHERE W is the weighting behind the module the pupil was excused from.
 
Upvote 0
The only problem is that the pupil does not get the full 100% for the module they missed. They simply get the values of the modules they have a mark for multiplied by 1/1-(w). w depending on the weighting for the module was missed.

Hope this makes sense :s
This message was edited by bingolover99 on 2002-10-27 13:50
 
Upvote 0
On 2002-10-27 13:48, bingolover99 wrote:
The only problem is that the pupil does not get the full 100% for the module they missed. They simply get the values of the modules they have a mark for multiplied by 1/1-(w). w depending on the weighting for the module was missed.

Hope this makes sense :s
This message was edited by bingolover99 on 2002-10-27 13:50

In my simulation, I have given you six cases, let us call them 1,2,...6 starting from top. Please show me which result is incorrect, what is it in the simulation and what it should be, and what logic is your number based on, and then let us take it from there. We don't have to deal with all 6 cases, just pick one of the more relevant ones.

Regards!

Yogi Anand
 
Upvote 0
Ok, take the first example. 100/abs/exc

the scores would be 15%/0/0 (because abs = 0 and exc would also be 0) but as the third value is exc, the overall value would be (15+0+0)*1(1-0.70) = giving an overall value of 50% not 85%.

The weighting for each module is as follows

M1 = 15% M2 = 15% and M3 = 70%
This message was edited by bingolover99 on 2002-10-27 15:51
 
Upvote 0
Hi bingolover:

Did you just fix that solution to the first case -- that is what I was trying to sort out. Let me post the simulation. Please look it over, and then there is one more case we have to fix -- what happens if a student is excused from all three m1, M2, and M3
y021027h1.xls
KLMNO
10.151550.00
20.15abs
30.70exc
4
51517.65
6exc
7abs
8
9abs50.00
1015
11exc
12
1315100.00
1415
1570
16
17exc#####
18exc
19exc
20
21abs0.00
22abs
23abs
Sheet1
</SPAN>

Regards!

Yogi Anand
 
Upvote 0
Hi bingolover:

Should the case with all three excuses be made 0% -- because in your formulation it gets to be divided by zero(0) =(0+0+0)/(1-(0.15+0.15+0.70)) equals 1/0

I am going to assume you want to make the case with all three excuses formulatically 0 -- other computations remain unchanged.

I have not made any attempt to simplify the formula -- the attempt has been to make the formula work according to your specification.

So with adjustment for the case of all three excuses, the updated formula is:

=(IF(ISNUMBER(M1),M1)+IF(ISNUMBER(M2),M2)+IF(ISNUMBER(M3),M3))/(1-(IF(OR(ISNUMBER(M1),M1="abs"),0,0.15)+IF(OR(ISNUMBER(M2),M2="abs"),0,0.15)+IF(OR(ISNUMBER(M3),M3="abs"),0,0.7))+IF(AND(M1="exc",M2="exc",M3="exc"),1,0))

and the worksheet simulation ...
y021027h1.xls
KLMNO
10.151550.00
20.15abs
30.70exc
4
51517.65
6exc
7abs
8
9abs50.00
1015
11exc
12
1315100.00
1415
1570
16
17exc0.00
18exc
19exc
20
21abs0.00
22abs
23abs
Sheet1
</SPAN>

Regards!

Yogi Anand
 
Upvote 0
Hi. I gave up last night cos it was testing my patience!

Had another look today and at the coding u suggested. Ill write the actual equation I have, cos i dont think ive explained it sufficiently for what i need it to do.

The final mark is produced using the following:

(M1*100*0.15/100)+(M2*100*0.15/30)+(M3*100*0.7/100)

"If abs is entered in a cell, the mark for that part of the calculation is 0.

If it is exc teh mark for the part would be given zero and then the final mark would be multiplied by 1/(1-percentage of the component)"

eg

John marks were as follows:

M1=32, M2=EXC, M3=42
therefore his score would be calculated as follows.

(32*100*0.15/100)+(0*100*0.15/30)+(42*100*0.7/100)*(1/(1-0.15))

(4.8 + 0 + 29.4)*(1/0.85)
34.2*1.1764
40.23529

Hope this explains it better. Thanks
This message was edited by bingolover99 on 2002-10-28 11:23
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top