more than 7 nested if (revised)

  • Thread starter Thread starter sharmashanu
  • Start date Start date
S

sharmashanu

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 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.
Click to expand...

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-
 
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
 
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)))
 
hi Roger
Dont give any importance to the "end of day". That was something to
put on when the if statement is false.
 
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
 
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
 
Back
Top