Help for Excel IF Statement

Microsoft / Microsoft excel 2002
September 28, 2010 at 03:26:42
Specs: Windows XP

I am a math teacher. During my classes I conduct a good number of paper-based quizzes (over 40) for my students and record their marks in an Excel spreadsheet. Typically these quizzes have a negative marking system in that the student is penalized a certain number of marks for an incorrect answer. Hence a student gets +4 marks for a correct answer, -1 for an incorrect answer and 0 if the question is not attempted. The final score is calculated after deducting marks for incorrect responses, if any. A quiz always has 20 questions.

I want to keep a record of both, the number of questions the students attempt and the number of their correct answer. This helps me calculate their final score. If a student, say Mary, attempted 12 questions out of which she got 9 correct, the data entry is done as 9.12 (9 point 12). There is room for two kinds of human error while someone enters this data on the computer.
Error 1) The marks may be entered as 12.9 instead of 9.12.
Error 2) The marks may be entered as 9.22 instead of 12 (although the max questions were only 20).

I have entered the marks in cell B2 and put the formula in another cell say Z2.


I need to be sure of the following:
i) The number of correct answers cannot exceed the number of questions attempted i.e. avoid Error 1.
ii) The total attempts cannot exceed 20 (Max questions being 20; Error 2)

Ideally there should be a formula that should prevent both the above errors, but the one I have pasted above doesn’t do that.

Funnily, the formula otherwise shows the final score correctly all the times, but when the final score comes to Zero (say 2 correct out of 10: this would mean 2 correct x 4 – 8 incorrect x 1 = Zero), the formula displays a strange output
instead of Zero. I checked with all possible instances of final score zero (1.05, 2.1, 3.15 and 4.2), and in each of these cases I receive a strange outputs (respectively: -1.1E-15, 2.22E-15, and -4.4E-15).

I use MS Excel 2002 XP in Windows XP OS.

Can someone please help me with the above formula and also point out why doesn't it show Zero as final score?

Many thanks in advance. Any help / tips will be appreciated.

A Shahane

See More: Help for Excel IF Statement

September 28, 2010 at 17:22:43
I need to be sure of the following:
i) The number of correct answers cannot exceed the number of questions attempted i.e. avoid Error 1.
ii) The total attempts cannot exceed 20 (Max questions being 20; Error 2)

This formula should work for problems 1 & 2:


As for why your formula does not work, my best guess would be that somewhere your order of precedence is incorrect.

In Excel, it multiplication and division and they have equal precedence, are done left to right in the order they come.
Then addition and subtraction which have equal precedence.

What is the output of the formula supposed to look like?


Report •

September 28, 2010 at 20:18:24
thank you very much, Mike. Am tied up in a small urgent assignment so will respond tomorrow in detail.

Report •

September 30, 2010 at 04:06:57

I am facing a strange issue that I can't resolve.

In cell A5, I enter a number, say 5.09
In cell B5, I enter formula =mod(A5,1)
In cell C5, I enter formula =trunc(A5,1)
In cell d5, I enter formula =B5*100
In cell e5, I enter formula =D5-C5

Ideally, all the following numbers in A5 should show 0 (zero) in e5.
1.05; 2.1; 3.15; 4.2; 5.25; 6.3; 7.35; 8.4.

All the above entries display 0 in e5 as expected, except that 7.35 shows 3.55271E-14.

Can Mike or anyone else tell me why this unusual error comes up and how can I solve it?

Many thanks in advance.

PS Mike: I have, with my penchant for unnecessarily complex formulas :), set up the following formula for my earlier issue.

=IF(MOD(A1,1)*100>20,"ERR: ATT > 20",IF(TRUNC(A1)>MOD(A1,1)*100,"ERR: COR > ATT",(TRUNC(A1)*4-(MOD(A1,1)*100-TRUNC(A1)))))

Things are ok, except that for entries like "1.05" or "3.15" in cell a1, I should get 0 (zero) as the output but I am getting a message like -4.44089E-15. for 1.05 and so on.

Just to try I expanded the formula
=IF(MOD(A1,1)*100>20,"ERR: ATT > 20",IF(TRUNC(A1)>MOD(A1,1)*100,"ERR: COR > ATT",IF(TRUNC(A1)*4=(MOD(A1,1)*100-TRUNC(A1)),"0",(TRUNC(A1)*4-(MOD(A1,1)*100-TRUNC(A1))))))

Strangely for 1.05, 3.15, and 4.2 the output shows 0 but 2.1, I get -8.88178E-15
I can't figure out why.
Thanks in advance.

Report •

Related Solutions

September 30, 2010 at 07:49:43
I tried your example and got the following:

NOTE: I modified your =TRUNC(A5,1) function
When I used the =TRUNC(A5,1) with the precision indicator of One,
It truncated 5.09 to 5, but when doing the same thing with 2.1 it truncated to 2.1.
With a precision indicator of 1, the truncation adds one more number,
So 2.1 is 2.1 but since Excel hates zeros, if it is a zero it is dropped.
Therefore, 5.09 will come out five.
I simply used =TRUNC(A5)

      A      B         C         D        E
         =MOD(A5,1) =TRUNC(A5) =B5*100	=D5-C5
1)  5.09    0.09       5         9        4
2)  1.05    0.05       1         5        4
3)   2.1     0.1       2        10        8
4)  3.15    0.15       3        15       12
5)   4.2     0.2       4        20       16
6)  5.25    0.25       5        25       20
7)   6.3     0.3       6        30       24
8)  7.35    0.35       7        35       28
9)   8.4     0.4       8        40       32


Report •

September 30, 2010 at 20:18:14
Hi Mike

I guess I must live with it. I don't have a clear idea of precision indicators. Does "Precision Indicator of One" mean I need to restrict the number of decimal values to 1 (Format>Cells>Numbers: Decimal places)?

But I guess I shouldn't grumble since my requirement was only upto 20 and that's taken care of.... :)

Thank you for the help all along.


Report •

October 1, 2010 at 07:19:47
Look here:

It may give you some insight into why =INT() and =TRUNC(), although similar, can sometimes give different values.

Glad you got your system working.


Report •

Ask Question