more than 7 nested if (revised)
Hi All
Thanks for the responses. I am sorry for not writing it clearly and sending wrong formula. My formula will work if it had less than 7 if statements. =IF(H8-$H$7<0.041,H8+$O$21, IF(H8-$H$7<0.0826,H8+$O$22, IF(H8-$H $7<0.1243,H8+$O$23, IF(H8-$F$7<0.166,H8+$O$24, IF(H8-$H$7<0.2076,H8+$O $25, IF(H8-$H$7<0.2493,H8+$O$26, IF(H8-$H$7<0.291,H8+$O$27, IF(H8-$H $7<0.3326,H8+$O$28, IF(H8-$H$7<0.375,H8+$O$29, IF(H8-$H$7<0.4167,H8+$O $30, IF(H8-$H$7<0.4583,H8+$O$31, IF(H8-$H$7<0.50,H8+$O$32, "End of day") what i am doing is - making a table with time as unit. In cell I8 during first hour i want to use value of cell O21 during second hour i want to use the value in cell O22 during third hour i want to use the value in cell O23 ..so on till cell O40 I subtracted the previous 2 cells(H8-H7) to check if the difference is less than one hour. I was not able to enter Hour format in 1:00 formula bar. so i used the value 0.041 which is nothing but 12:59:00 or 59 min. what it does is (H8-H7 < .041) checks if it is hour 1, if true - it adds O21 to H8 and gives output in cell I8 If false if test next condition(H8-H7 < .082) for hour 2, if true it adds O22 to H8 and gives output in cell I8 and so on. If I am at the 9th hour of the day it will not work. Because it exceeds the limit of If statement. The above formula will let me do it till O28 but i still have more if statements. I know i have written this formula like a kid in excel would. I will really appreciate if someone can write it more practically. Thanks once again for all the efforts. |
more than 7 nested if (revised)
On 6 Mar, 15:43, wrote:
Hi All Thanks for the responses. I am sorry for not writing it clearly and sending wrong formula. My formula will work if it had less than 7 if statements. =IF(H8-$H$7<0.041,H8+$O$21, IF(H8-$H$7<0.0826,H8+$O$22, IF(H8-$H $7<0.1243,H8+$O$23, IF(H8-$F$7<0.166,H8+$O$24, IF(H8-$H$7<0.2076,H8+$O $25, IF(H8-$H$7<0.2493,H8+$O$26, IF(H8-$H$7<0.291,H8+$O$27, IF(H8-$H $7<0.3326,H8+$O$28, IF(H8-$H$7<0.375,H8+$O$29, IF(H8-$H$7<0.4167,H8+$O $30, IF(H8-$H$7<0.4583,H8+$O$31, IF(H8-$H$7<0.50,H8+$O$32, "End of day") what i am doing is - making a table with time as unit. In cell I8 during first hour i want to use value of cell O21 during second hour i want to use the value in cell O22 during third hour i want to use the value in cell O23 ..so on till cell O40 I subtracted the previous 2 cells(H8-H7) to check if the difference is less than one hour. I was not able to enter Hour format in 1:00 formula bar. so i used the value 0.041 which is nothing but 12:59:00 or 59 min. what it does is (H8-H7 < .041) checks if it is hour 1, if true - it adds O21 to H8 and gives output in cell I8 If false if test next condition(H8-H7 < .082) for hour 2, if true it adds O22 to H8 and gives output in cell I8 and so on. If I am at the 9th hour of the day it will not work. Because it exceeds the limit of If statement. The above formula will let me do it till O28 but i still have more if statements. I know i have written this formula like a kid in excel would. I will really appreciate if someone can write it more practically. Thanks once again for all the efforts. Hi there. Couldnt you just put your logic checks into a module (in a public function) and call that function from the forumla bar? -Mike- |
more than 7 nested if (revised)
can you tell me the format of that function
tx |
more than 7 nested if (revised)
It'd be better if you set up a table to examine with a VLOOKUP; something like
..041 =H8+O21 ..0826 =H8+O22 ..1243 =H8+O23 etc then =VLOOKUP(H8-H7,the above table,2) Bob Umlas Excel MVP " wrote: Hi All Thanks for the responses. I am sorry for not writing it clearly and sending wrong formula. My formula will work if it had less than 7 if statements. =IF(H8-$H$7<0.041,H8+$O$21, IF(H8-$H$7<0.0826,H8+$O$22, IF(H8-$H $7<0.1243,H8+$O$23, IF(H8-$F$7<0.166,H8+$O$24, IF(H8-$H$7<0.2076,H8+$O $25, IF(H8-$H$7<0.2493,H8+$O$26, IF(H8-$H$7<0.291,H8+$O$27, IF(H8-$H $7<0.3326,H8+$O$28, IF(H8-$H$7<0.375,H8+$O$29, IF(H8-$H$7<0.4167,H8+$O $30, IF(H8-$H$7<0.4583,H8+$O$31, IF(H8-$H$7<0.50,H8+$O$32, "End of day") what i am doing is - making a table with time as unit. In cell I8 during first hour i want to use value of cell O21 during second hour i want to use the value in cell O22 during third hour i want to use the value in cell O23 ..so on till cell O40 I subtracted the previous 2 cells(H8-H7) to check if the difference is less than one hour. I was not able to enter Hour format in 1:00 formula bar. so i used the value 0.041 which is nothing but 12:59:00 or 59 min. what it does is (H8-H7 < .041) checks if it is hour 1, if true - it adds O21 to H8 and gives output in cell I8 If false if test next condition(H8-H7 < .082) for hour 2, if true it adds O22 to H8 and gives output in cell I8 and so on. If I am at the 9th hour of the day it will not work. Because it exceeds the limit of If statement. The above formula will let me do it till O28 but i still have more if statements. I know i have written this formula like a kid in excel would. I will really appreciate if someone can write it more practically. Thanks once again for all the efforts. |
more than 7 nested if (revised)
Hi
Could you not use =H8+INDEX($O$21:$O40,INT((H8-$H$7)*24)) -- Regards Roger Govier "sharmashanu" wrote in message ps.com... can you tell me the format of that function tx |
more than 7 nested if (revised)
I didn't deal with possibility of times crossing midnight, and I only
just noticed the part of your formula saying End of Day. Amend the formula to =IF(MOD(H8-$H$7,1)0.5,"End of Day", H8+INDEX($O$21:$O40,INT((MOD(H8-H7,1))*24))) -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Could you not use =H8+INDEX($O$21:$O40,INT((H8-$H$7)*24)) -- Regards Roger Govier "sharmashanu" wrote in message ps.com... can you tell me the format of that function tx |
more than 7 nested if (revised)
still dont work
|
more than 7 nested if (revised)
hi Roger
Dont give any importance to the "end of day". That was something to put on when the if statement is false. |
more than 7 nested if (revised)
Why?
Works perfectly for me. What data do you have in H7,H8 and in O21:O40. With 08:30 in H7, 15:30 in H8 and 01:15 in cell O27 the formula returns 16:45 The number of hours between 08:30 and 15:30 is 7. The 7th value in the range O21:O40 is cell C27 15:30 + 1:15 gives 16:45 -- Regards Roger Govier "sharmashanu" wrote in message oups.com... still dont work |
more than 7 nested if (revised)
Thanks Roger.
I dont know why it works in different sheet and not the sheet i was working. So i copied it to new sheet. Thanks once again. Shanu |
All times are GMT +1. The time now is 02:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com