Formula with IF AND

[Lukma]

good Day friend

I need a help with a fomula to solve in ( column F ) with IF formula withmultiple criteria

IF Column B = Totes Tanks and column D is greater or equal =>70% subtract Column C from Column E ( 20 hrs)
IF Column B = Totes Tanks and column D is greater or equal <70% subtract Column C from Column E ( 16 hrs)
AND IF column B=Bundle and C is Greater than 12 Hrs subtract column C from Column E
AND IF column B=Bundle and C is LessGreater than 12 Hrs subtract column C from Column E

Appreciate with help with formula in Column F

Regards

A	B	C	D	E	F ( Formula Answer)
Tom	Totes Tanks	25:00	70%	20:00	5:00
Jame	Bundle	16:00	60%	12:00	4:00
Frank	Bundle	12:00	70%	12:00	00:00
Tomas	Totes Tanks	16:00	55%	16:00	00:00
Dan	Totes Tanks	20:00	65%	16:00	4:00

 

[DanteAmor]

Hi @Lukma

I have this doubt_

IF Column B = Totes Tanks and column D is greater or equal =>70% subtract Column C from Column E ( 20 hrs)
IF Column B = Totes Tanks and column D is greater or equal <70% subtract Column C from Column E ( 16 hrs)

You could review your rules, in both cases C minus E is subtracted
--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[ExceLoki]

it looks as though all in all ifs, it ends with subtract column C from Column E
is this correct?

 

[Lukma]

Hi Dante and Exceloki

Please find remark though is KPI for criteria set hours to be subtracted but i dont seem to get the formula in Column J

Appreciate your Support

Regards

Average time spent (Cargo Volume ≥ 70%) on Totes Tanks ≤ 20Hrs.

Average time spent (Cargo Volume < 70%) on Totes Tanks ≤ 14Hrs.

Average time spent in day light operations on Bundle ≤ 12Hrs.

ILSP Vessels Time spent Offshore April-2023 (LPT).xlsx
	F	G	H	I	J
1	Totes Tanks	70%	20:00
2	Bundles		14:00
3	Name	Unique Location	Deck %	Total Hours Hrs	Exceeded Hors
4	Jame	Bundles	0%	32:00	FALSE
5	Frank	Totes Tanks	70%	16:00	FALSE
6	Peter	Bundles	100%	16:00	FALSE
7	thomas	Totes Tanks	0%	1:27	FALSE
8	Tom	Bundles	0%	0:40	FALSE
9	Jame	Totes Tanks	0%	1:00	FALSE
10	Frank	Bundles	0%	17:00	FALSE
11	Peter	Totes Tanks	50%	17:00	FALSE
12	thomas	Bundles	0%	8:25	FALSE
13	Tom	Totes Tanks	0%	12:00	FALSE
14	Jame	Bundles	0%	12:00	FALSE
15	Frank	Totes Tanks	50%	9:10	FALSE
16	Peter	Bundles	10%	4:40	FALSE
17	thomas	Totes Tanks	100%	16:00	FALSE
18	Tom	Bundles	80%	14:00	FALSE
Sheet1

Cell Formulas
Range	Formula
J4:J18	J4	=IF(AND(G4=$F$1,G4=$F$2,H4>$G$1),$H$1-I4)

 

[Lukma]

Hi Dante & ExceLoki

I have also try to see if i am getting this right but still i have some doubt in my formula

when i trying my formula in Column J it gives me without error Time in Days, but when i entered same formula in column K in hours i keep having some time Error #####

Appreciate if there is better way.

ILSP Vessels Time spent Offshore April-2023 (LPT).xlsx
	F	G	H	I	J	K
1	Totes Tanks	70%	20:00
2	Bundles		14:00		My Try 1
3	Name	Cargo Tpye	Cargo %	Total Hours Hrs	Exceeded Time In Days	MY Formula Hours
4	Jame	Bundles	0%	32:00	0.75	18:00
5	Frank	Totes Tanks	70%	32:00	0.50	12:00
6	Peter	Bundles	100%	16:00	0.08	2:00
7	thomas	Totes Tanks	0%	1:27		###########
8	Tom	Bundles	0%	0:40		0:00
9	Jame	Totes Tanks	0%	1:00		Met
10	Frank	Bundles	0%	17:00	0.13	3:00
11	Peter	Totes Tanks	50%	17:00		Met
12	thomas	Bundles	0%	8:25		0:00
13	Tom	Totes Tanks	0%	12:00		Met
14	Jame	Bundles	0%	12:00		0:00
15	Frank	Totes Tanks	50%	9:10		Met
16	Peter	Bundles	10%	4:40		0:00
17	thomas	Totes Tanks	100%	16:00	-0.17	###########
18	Tom	Bundles	80%	14:00		0:00
Sheet1

Cell Formulas
Range	Formula
J4:J18	J4	=IF(AND(G4=$F$2,I4>$H$2),I4-$H$2,IF(AND(G4=$F$1,H4>=$G$1),I4-$H$1,""))
K4	K4	=IF(AND(G4=$F$2,I4>$H$2),I4-$H$2,IF(AND(G4=$F$1,H4>=$G$1),I4-$H$1,""))
K5:K6	K5	=IF(AND(G5=$F$2,I5>$H$2),I5-$H$2,IF(AND(G5=$F$1,H5>=$G$1),I5-$H$1,IF(AND(G5=$F$1,H5<$G$1),I5-$H$1,"")))
K7	K7	=IF(AND(G7=$F$2,I7>$H$2),I7-$H$2,IF(AND(G7=$F$1,H7>=$G$1),I7-$H$1,IF(AND(G7=$F$1,H7<=$G$1),I7-H1,"Met")))
K8:K16,K18	K8	=IF(AND(G8=$F$2,I8>$H$2),I8-$H$2,IF(AND(G8=$F$1,H8>=$G$1),I8-$H$1,IF(AND(G8=$F$1,H8<$G$1),"Met",)))
K17	K17	=IF(AND(G17=$F$2,I17>$H$2),I17-$H$2,IF(AND(G17=$F$1,H17>=$G$1),I17-$H$1,IF(AND(G17=$F$1,H17<$G$1),"Met","")))

 

[Lukma]

Hi @Lukma

I have this doubt_

You could review your rules, in both cases C minus E is subtracted
--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

good day Dante

I have send you for review
appreciate your support if my formula is right

Regards