|
If formula
Hi, hoping someone can assist with an IF formula I am trying to write. it goes; = IF number between 1 & 3, then 1 *10%, IF number between 4 & 7, then 1 *20%, IF number between 8 & 10, then 1 * 30% thanks, Nik -- Ntisch ------------------------------------------------------------------------ Ntisch's Profile: http://www.excelforum.com/member.php...fo&userid=5791 View this thread: http://www.excelforum.com/showthread...hreadid=389973 |
=1*if(And(A1=1,A1<=3),.10,if(And(A1=4,A1<=7),.20 ,if(And(A1=8,A1<=10),.3,0
))) another possibility might be =1*if(A1<1,0,if(A1<=3,.1,if(A1<=7,.2,if(A1<=10,.3, 0)))) -- Regards, Tom Ogilvy "Ntisch" wrote in message ... Hi, hoping someone can assist with an IF formula I am trying to write. it goes; = IF number between 1 & 3, then 1 *10%, IF number between 4 & 7, then 1 *20%, IF number between 8 & 10, then 1 * 30% thanks, Nik -- Ntisch ------------------------------------------------------------------------ Ntisch's Profile: http://www.excelforum.com/member.php...fo&userid=5791 View this thread: http://www.excelforum.com/showthread...hreadid=389973 |
Hi Tom - i am trying to use yr formula below but am getting a zero result =1*if(And(A1=1,A1<=3),.10,if(And(A1=4,A1<=7),.20 ,if(And(A1=8,A1<=10),.3,0 ))) any ideas why? thanks, Nik -- Ntisch ------------------------------------------------------------------------ Ntisch's Profile: http://www.excelforum.com/member.php...fo&userid=5791 View this thread: http://www.excelforum.com/showthread...hreadid=389973 |
Ntisch
Are you putting the number in cell A1 ? If so, is it in the range 1 to 10 ? Is the value numeric ? Could the cell have been formatted as text before you put a value in it ? Try formatting the cell as General and then re-inputting your number. Otherwise, no, no idea. Regards Trevor "Ntisch" wrote in message ... Hi Tom - i am trying to use yr formula below but am getting a zero result =1*if(And(A1=1,A1<=3),.10,if(And(A1=4,A1<=7),.20 ,if(And(A1=8,A1<=10),.3,0 ))) any ideas why? thanks, Nik -- Ntisch ------------------------------------------------------------------------ Ntisch's Profile: http://www.excelforum.com/member.php...fo&userid=5791 View this thread: http://www.excelforum.com/showthread...hreadid=389973 |
It worked fine for me for integers entered in A1. A number like 3.5 would
be excluded and return zero in accordance with the criteria you posted (or implied in the criteria you posted). -- Regards, Tom Ogilvy "Ntisch" wrote in message ... Hi Tom - i am trying to use yr formula below but am getting a zero result =1*if(And(A1=1,A1<=3),.10,if(And(A1=4,A1<=7),.20 ,if(And(A1=8,A1<=10),.3,0 ))) any ideas why? thanks, Nik -- Ntisch ------------------------------------------------------------------------ Ntisch's Profile: http://www.excelforum.com/member.php...fo&userid=5791 View this thread: http://www.excelforum.com/showthread...hreadid=389973 |
Hi Trevor, thanks for your suggestion. I am using your formula; =1*if(And(A1=1,A1<=3),.10,if(And(A1=4,A1<=7),.20 ,if(And(A1=8,A1<=10),.3,0 ))) I am using a cell reference as the constant - the 1 (multiplied by) in the forumla. I am putting different numbers in the cell that is used as the constant. I reinputted the numbers and found that the formula works using some numbers in the cell reference, but otherwise doesn't work instead returning a NAME? error. Puzzled? thanks -- Ntisch ------------------------------------------------------------------------ Ntisch's Profile: http://www.excelforum.com/member.php...fo&userid=5791 View this thread: http://www.excelforum.com/showthread...hreadid=389973 |
Ntisch
the formula: =1*IF(AND(A1=1,A1<=3),0.1,IF(AND(A1=4,A1<=7),0.2 ,IF(AND(A1=8,A1<=10),0.3,0 ))) works for me If, as Tom suggests, your value lies between the (integer) numbers in the formula, you will get a zero result. Hence 3.5 is between 3 and 4 so does not meet any of the criteria. You might want to try this instead: =1*IF(A1<=3,0.1,IF(A1<=7,0.2,IF(A1<=10,0.3,0 ))) Regards Trevor "Ntisch" wrote in message ... Hi Trevor, thanks for your suggestion. I am using your formula; =1*if(And(A1=1,A1<=3),.10,if(And(A1=4,A1<=7),.20 ,if(And(A1=8,A1<=10),.3,0 ))) I am using a cell reference as the constant - the 1 (multiplied by) in the forumla. I am putting different numbers in the cell that is used as the constant. I reinputted the numbers and found that the formula works using some numbers in the cell reference, but otherwise doesn't work instead returning a NAME? error. Puzzled? thanks -- Ntisch ------------------------------------------------------------------------ Ntisch's Profile: http://www.excelforum.com/member.php...fo&userid=5791 View this thread: http://www.excelforum.com/showthread...hreadid=389973 |
Thanks for your help Tom and Trevor. The second formula works. Interestingly the first formula - =1*if(And(A1=1,A1<=3),.10,if(And(A1=4,A1<=7),.20 ,if(And(A1=8,A1<=10),.3,0 ))) Failed to work even though I wasn't using integers. regards, Nik -- Ntisch ------------------------------------------------------------------------ Ntisch's Profile: http://www.excelforum.com/member.php...fo&userid=5791 View this thread: http://www.excelforum.com/showthread...hreadid=389973 |
"Ntisch" wrote in
message ... Interestingly the first formula - =1*if(And(A1=1,A1<=3),.10,if(And(A1=4,A1<=7),.20 ,if(And(A1=8,A1<=10),.3,0 ))) Failed to work even though I wasn't using integers. This formula will not work for any value between, (but not including), 3 & 4, and 7 & 8. Could that explain the failure? -- HTH Sandy Replace@mailinator with @tiscali.co.uk |
| All times are GMT +1. The time now is 12:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com