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??
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Hello:

One way to get the correct grade is to copy your data to another section of the worksheet or to another worksheet(to keep your original data intact)...then use find and relace the "abs" with 0 (zeros), then do your math to get averages, etc.

I am sure someone will post a better formula that will substiture the "abs" with "0" in a single cell formula.

Hope this helps
 
Upvote 0
On 2002-10-26 08:12, bingolover99 wrote:
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??

Care to post your grade formula?
 
Upvote 0
It is easy to get your answer all in one column with a reault for each row's data. If your grades are in a range of columns(C to H) and you want your "Average" score in Column (I). And you have a new student on each row, then:

In column (I) of your first set (row) of scors put:

=Sum(C2:H2)/(Count(C2:H2)+CountIf(C2:H2,"ABS"))

Note: Change the Range used above to the Range used on your sheet. Also, I used "ABS" for absent, make this whatever you are using.

Then Copy-Paste the above formula down for each student.

The formula Sums all the grades then devides this total by the number of scors plus the number of Absents. Hope this helps. JSW
 
Upvote 0
The formula i have is:

(M1(100)(0.15))/100 WHERE M1 is the score gained on the the first exam. This is where the abs is for absent pupils.
 
Upvote 0
On 2002-10-26 12:26, bingolover99 wrote:
The formula i have is:

(M1(100)(0.15))/100 WHERE M1 is the score gained on the the first exam. This is where the abs is for absent pupils.

I suppose you mean:

=(M1*(100)*(0.15))/100

although I'm unclear about the computation involved.

You'll get indeed a #VALUE! error if M1 houses "abs" instead of a score. You can change the formula to...

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

It gives 0 when M1 is not a number/score.
 
Upvote 0
thanks but what happens if im taking three M values, M1 M2 AND M3 into account?? where the formula would be

=SUM(M1*100*0.15)/100+(M2*100*0.15)/100+(M3*100*0.7)/100

where more than one value of M can be abs
 
Upvote 0
On 2002-10-26 12:49, bingolover99 wrote:
thanks but what happens if im taking three M values, M1 M2 AND M3 into account?? where the formula would be

=SUM(M1*100*0.15)/100+(M2*100*0.15)/100+(M3*100*0.7)/100

where more than one value of M can be abs

Is a 0 ever given as score?
 
Upvote 0
Hi bingolover99:

use

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

Does this do it?

Regards!

Yogi Anand
 
Upvote 0
method 1:
use Aladin's code concate'd 3 times:

=IF(ISNUMBER(M1),M1*15%,0)+
IF(ISNUMBER(M2),M2*15%,0)+
IF(ISNUMBER(M3),M3*70%,0)

method 2:
construct a lookup table including abs, eg.
abs 0
0 0
1 1
...

100 100

and vlooup on that :) [that's not really a serious suggestion, I just like thinking of alternatives :wink: ]
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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