# Help for Excel IF Statement

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

See More: Help for Excel IF Statement

#1
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:=IF((MOD(B2,1)*100)>20,"error",IF(INT(B2)>(MOD(B2,1)*100),"error",""))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?MIKEhttp://www.skeptic.com/

Report •

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

Report •

#3
September 30, 2010 at 04:06:57
 HiI am facing a strange issue that I can't resolve. In cell A5, I enter a number, say 5.09In cell B5, I enter formula =mod(A5,1)In cell C5, I enter formula =trunc(A5,1)In cell d5, I enter formula =B5*100In cell e5, I enter formula =D5-C5Ideally, 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-15I can't figure out why.Thanks in advance.-Ajay

Report •

Related Solutions

#4
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 ```MIKEhttp://www.skeptic.com/

Report •

#5
September 30, 2010 at 20:18:14
 Hi MikeI 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.-Ajay

Report •

#6
October 1, 2010 at 07:19:47
 Look here:http://office.microsoft.com/en-ca/e...It may give you some insight into why =INT() and =TRUNC(), although similar, can sometimes give different values.Glad you got your system working.MIKEhttp://www.skeptic.com/

Report •