S scobad Board Regular Joined Feb 7, 2005 Messages 164 Nov 18, 2005 #1 =IF(O2>10,40,IF(O2=4,16,IF(O2=5,20,IF(O2=6,24,IF(O2=7,28,IF(O2=8,32,IF(O2=9,36,))))))) What is happening is that if the O2 is 6 it is returning zero? 4=16 5=20 6=24 7=28 8=32 9=36 10,11,12 = 40
=IF(O2>10,40,IF(O2=4,16,IF(O2=5,20,IF(O2=6,24,IF(O2=7,28,IF(O2=8,32,IF(O2=9,36,))))))) What is happening is that if the O2 is 6 it is returning zero? 4=16 5=20 6=24 7=28 8=32 9=36 10,11,12 = 40
Andrew Poulsom MrExcel MVP Joined Jul 21, 2002 Messages 73,092 Nov 18, 2005 #2 I couldn't reproduce your problem, but maybe try: =MIN(O2*4,40)
L Legacy 51064 Guest Nov 18, 2005 #3 Andrew has a very nice solution. If you need all of the if statements: =IF(O2=4,16,IF(O2=5,20,IF(O2=6,24,IF(O2=7,28,IF(O2=8,32,IF(O2=9,36,IF(O2>10,40,0))))))) Will O2 ever be less than 4?
Andrew has a very nice solution. If you need all of the if statements: =IF(O2=4,16,IF(O2=5,20,IF(O2=6,24,IF(O2=7,28,IF(O2=8,32,IF(O2=9,36,IF(O2>10,40,0))))))) Will O2 ever be less than 4?
S scobad Board Regular Joined Feb 7, 2005 Messages 164 Nov 18, 2005 #4 No...But - here mught be the problem: Here is "O" =(M2-I2)/30
L Legacy 51064 Guest Nov 18, 2005 #5 If the product is not a whole number, then the my solution will not help you in its current form.
barry houdini MrExcel MVP Joined Mar 23, 2005 Messages 20,825 Nov 18, 2005 #6 Clearly the result of =(M2-I2)/30 isn't exactly 6 You perhaps need to adjust one of the formulas, either you round the value in O2 to the nearest integer =ROUND((M2-I2)/30,0) or perhaps adjust the other formula to cope with values that are not integers
Clearly the result of =(M2-I2)/30 isn't exactly 6 You perhaps need to adjust one of the formulas, either you round the value in O2 to the nearest integer =ROUND((M2-I2)/30,0) or perhaps adjust the other formula to cope with values that are not integers