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-