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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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?
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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
 

bingolover99

New Member
Joined
Oct 25, 2002
Messages
20

ADVERTISEMENT

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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

bingolover99

New Member
Joined
Oct 25, 2002
Messages
20

ADVERTISEMENT

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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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?
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
 

attc

Board Regular
Joined
Oct 11, 2002
Messages
87
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: ]
 

Forum statistics

Threads
1,144,112
Messages
5,722,539
Members
422,446
Latest member
jsasiadek35

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
Top