Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3rd and last Revised WorkBook | Excel Discussion (Misc queries) | |||
REVISED Nexted IF and Countif | Excel Worksheet Functions | |||
Format excel to revised date automatically when revised | Excel Programming | |||
n or U Revised ? | Excel Worksheet Functions | |||
Revised btnprint1_click() | Excel Programming |