Logical formula in multiple criteria

Dear Moderators
Many thanks for your support in providing the necessary help in constructing the automated files.

I am in middle of constructing a formula please see the attached file:


Need help in the following criteria:

1 If E7 is "FD" then, the H7 can exceeds maximum limit of 80 then in cell I7 it should give result of only excess km will be there
2 In the same way If E7 is "HD" then, the H7 exceeds maximum limit of 40 then in cell I7 it should give result of only excess km
3 In the same way If E7 is "FD" then, the M7 can show the extra hrs if it is >8 hrs
4 In the same way If E7 is "HD" then, the M7 can show the extra hrs if it is >4 hrs



Appreciate you earliest help in this regards

Amul

=IF(H7>=HLOOKUP(E7,$C$1:$D$3,2),H7-HLOOKUP(E7,$C$1:$D$3,2),"0") for I
=IF(((K7-J7))*24>HLOOKUP(E7,$C$1:$D$3,3),((K7-J7))*24-HLOOKUP(E7,$C$1:$D$3,3),0) for M

Originally Posted by Kaper

I7:

Formula:

Please Login or Register  to view this content.

and copy down.
H7 similar:

Formula:

Please Login or Register  to view this content.

MAX is easier to use than IF in this case.
Why?
Instead of IF(X>0,X,0) we can use MAX(X,0)
and both formulas for X < = 0 will return 0 and for X > 0 just X

Dear Kaper
Many thanks it worked..........
regards/amul

Originally Posted by tim201110

=IF(H7>=HLOOKUP(E7,$C$1:$D$3,2),H7-HLOOKUP(E7,$C$1:$D$3,2),"0") for I
=IF(((K7-J7))*24>HLOOKUP(E7,$C$1:$D$3,3),((K7-J7))*24-HLOOKUP(E7,$C$1:$D$3,3),0) for M

Dear Tim..
thanks a lot.. it also works...
regards/amul

Originally Posted by mehmetcik

Formula for H7

Formula:

Please Login or Register  to view this content.

Formula for M7

Formula:

Please Login or Register  to view this content.

Dear Mehmetcik

many thanks...
it works too......
thank you so much.... appreciated your efforts...
regards
amul