#### bingolover99

##### New Member
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### plettieri

##### Well-known Member
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

##### MrExcel MVP
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??

#### Joe Was

##### MrExcel MVP
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
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.

##### MrExcel MVP
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
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

##### MrExcel MVP
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
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
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: ]

Replies
1
Views
450
Replies
15
Views
534
Replies
1
Views
189
Replies
2
Views
284
Replies
1
Views
74

1,181,900
Messages
5,932,703
Members
436,852
Latest member
Borekch

### 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.

### Which adblocker are you using?

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

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