more than 7 nested if (revised)

[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.

 

[Mighele]

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-

 

[sharmashanu]

can you tell me the format of that function
tx

 

[Guest]

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

 

[Roger Govier]

Hi

Could you not use
=H8+INDEX($O$21:$O40,INT((H8-$H$7)*24))

 

[Roger Govier]

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)))

 

[sharmashanu]

still dont work

 

[sharmashanu]

hi Roger
Dont give any importance to the "end of day". That was something to
put on when the if statement is false.

 

[Roger Govier]

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

 

[sharmashanu]

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