Help with IF Formula for my Data.

[Lukma]

Hi friends

I need a Assistance with a new task and i have my data set in the way but i need a formula in Column S for the KPI criteria to subtract from my total hours Column R
Three Criteria is set for Percentage that mean
IF Criteria-1 Rigs&Barges ≥ 70% and the total hours is greater than 20:00 then i need to subtract 20Hrs from the total Hrs. in Column R
IF Criteria-2 Rigs&Barge <70% and total hours is greater than 14:00 then i need to subtract 14:00 from the total hours. in column R
Last Criteria-3 is Complex is greater than 12:00 in Column R then formula to subtract 12:00 from the total hours.

I have some Example of what i need the formula result in Column S

Appreciate all support to have this easy for my extraction.

Regards

ILSP_Offshore Vessel Time Spent Tracking 2023.xlsx
	O	P	Q	R	S
4	KPI Criteria	Location	Deck % Loaded Per Voyage	Total Spent Hrs Per Voyage	KPI Exceeded Hrs
5	Rigs&Barges	Rig Mehzem	70%	10:0
6	Rigs&Barges	Rig Al Noof	45%	15:0
7
8	Rigs&Barges	Rig SMS Essa	65%	14:50	0:50
9	Rigs&Barges	Rig Yemillah	70%	23:0	3:0
10	Rigs&Barges	Barge Lulwa		6:30
11	Island	Bu Sikeen Island		0:18
12
13	Island	Al Qatia Island		0:12
14
15	Complex	Umm Lulu Complex		14:0	2:0
16
17	Island	Umm Al Anbar		1:40
18	Island	Al Ghallan Island		11:35
19	Island	Ettouk Island		7:30
20	Island	Asseifiya Island		9:20
21
22
23
24
25
26	Rigs&Barges	Rig Nurai(Rig SMS Faith)		0:25
27	Rigs&Barges	Rig Al Hail		5:35
28	Rigs&Barges	Rig Aryabhatt-1		0:45
29
30
31	Rigs&Barges	Rig Al Bateel		1:0
32	Rigs&Barges	Rig Diyina		11:0
33
34
35		WOW		6:59
36	Rigs&Barges	Barge Keloa		2:54
37	Complex	Umm Lulu Complex		0:25
ILSP Offshore Vessel Tracking

Cell Formulas
Range	Formula
O16:O37,O5:O14	O5	=IFERROR(VLOOKUP(P5,'Total Location Serving'!$D$3:$E$100,2,0),"")
P5	P5	=IF(COUNTIFS(G5:$G$5,G5,E5:$E$5,E5)=1,G5,"")
P16:P37,P6:P14	P6	=IF(COUNTIFS(G$5:$G6,G6,E$5:$E6,E6)=1,G6,"")
R5,R16:R37,R10:R14,R7:R8	R5	=IF(P5="","",SUMIFS($K$5:$K$59996,$I$5:$I$59996,I5,$E$5:$E$59996,E5))
Q7,Q10:Q37	Q7	=IFERROR(VLOOKUP(C7,'[Vessels Actual Loadable Deck Space m2 (2023).xlsx]Vessel Location Deck M2'!$A$2:$J$5270,10,0),"")

 

[jorismoerings]

Hi, I couldn't exactly replicate your data.

When uing Excel 2019 - try S5, when using Excel 2016 - try S6.

Book1
	S
4	KPI Exceeded Hrs
5	0
6	0,041666667
Sheet1

Cell Formulas
Range	Formula
S5	S5	=SWITCH(TRUE(),AND(O5="Rigs&Barges",Q5>=0.7,R5>TIME(20,0,0)),R5-TIME(20,0,0),AND(O5="Rigs&Barges",Q5<0.7,R5>TIME(14,0,0)),R5-TIME(14,0,0),AND(O5="Complex",R5>TIME(12,0,0)),R5-TIME(12,0,0),0)
S6	S6	=IF(O6="Rigs&Barges", IF(Q6>=0.7, IF(R6>TIME(20,0,0), R6-TIME(20,0,0),0), IF(R6>TIME(14,0,0), R6-TIME(14,0,0),0)), IF(AND(O6="Complex",R6>TIME(12,0,0)),R6-TIME(12,0,0),0))

 

[Lukma]

Jorismoerings

Thanks so much for your support, after trying with formula in column T & U which you advised, and i have also tried with little of my knowledge but still i really dont know where problem is laying in the formula and reason am not getting the result

In my data you will see below in my data sheet result in meant to be 0.50 but am getting 0:0 but i really don't where the problem is laying

Appreciate if your support.

REgards

Rigs&Barges

Rig SMS Essa

0%

14:50

0:0

0:0

ILSP_Offshore Vessel Time Spent Tracking 2023.xlsx
	O	P	Q	R	S	T	U
5	KPI Criteria	Location	Deck % Loaded Per Voyage	Total Spent Hrs Per Voyage	KPI Exceeded Hrs	formula 1	Formula 2
6	Rigs&Barges	Rig Mehzem	0%	10:0		0:0	0:0
7	Rigs&Barges	Rig Al Noof	0%	10:0		0:0	0:0
8			0%			0:0	0:0
9	Rigs&Barges	Rig SMS Essa	0%	14:50		0:0	0:0
10	Rigs&Barges	Rig Yemillah	0%	1:50		0:0	0:0
11	Rigs&Barges	Barge Lulwa	0%	6:30		0:0	0:0
12	Island	Bu Sikeen Island	0%	0:18		0:0	0:0
13			0%			0:0	0:0
14	Island	Al Qatia Island	0%	0:12		0:0	0:0
ILSP Offshore Vessel Tracking

Cell Formulas
Range	Formula
O6:O14	O6	=IFERROR(VLOOKUP(P6,'Total Location Serving'!$D$3:$E$100,2,0),"")
P6	P6	=IF(COUNTIFS(G6:$G$6,G6,E6:$E$6,E6)=1,G6,"")
Q6:Q14	Q6	=IFERROR(VLOOKUP(C6,'[Vessels Actual Loadable Deck Space m2 (2023).xlsx]Vessel Location Deck M2'!$A$2:$J$5270,10,0),"0%")
R6:R14	R6	=IF(P6="","",SUMIFS($K$6:$K$59997,$I$6:$I$59997,I6,$E$6:$E$59997,E6))
S6:S10	S6	=IF(AND(O6=$O$2,Q6=$Q$2,R6>=$P$2),R6-$P$2,IF(AND(O6=$O$3,Q6<$Q$2,R6>$P$3),R6-$P$3,IF(AND(O6=$O$4,R6>$P$4),R6-$P$4,"")))
T6:T14	T6	=SWITCH(TRUE(),AND(O6="Rigs&Barges",Q6>=$Q$2,R6>TIME(20,0,0)),R6-TIME(20,0,0),AND(O6="Rigs&Barges",Q6<$Q$3,R6>TIME(14,0,0)),R6-TIME(14,0,0),AND(O6="Complex",R6>TIME(12,0,0)),R6-TIME(12,0,0),0)
U6:U14	U6	=IF(O6="Rigs&Barges", IF(Q6>=0.7,IF(R6>TIME(20,0,0), R6-TIME(20,0,0),0),IF(R6>TIME(14,0,0), R6-TIME(14,0,0),0)),IF(AND(O6="Complex",R6>TIME(12,0,0)),R6-TIME(12,0,0),0))
P7:P14	P7	=IF(COUNTIFS(G$6:$G7,G7,E$6:$E7,E7)=1,G7,"")
S11:S14	S11	=IF(AND(O11=$O$2,Q11=$Q$2,R11>$P$2),R11-$P$2,IF(AND(O11=$O$3,Q11<$Q$2,R11>$P$3),R11-$P$3,IF(AND(O11=$O$4,R11>$P$4),R11-$P$4,"")))

 

[jorismoerings]

Hi,

I can't replicate your outcome because i can't see what's in the cells your not sharing (anything above row 5) however:

Book1
	O	P	Q	R	S	T	U
2			70%
3			70%
4
5	KPI Criteria	Location	Deck % Loaded Per Voyage	Total Spent Hrs Per Voyage	KPI Exceeded Hrs	formula 1	Formula 2
6	Rigs&Barges		0%	14:50:00		00:50:00	00:50:00
7						00:50:00	00:50:00
Sheet1

Cell Formulas
Range	Formula
T6	T6	=SWITCH(TRUE(),AND(O6="Rigs&Barges",Q6>=$Q$2,R6>TIME(20,0,0)),R6-TIME(20,0,0),AND(O6="Rigs&Barges",Q6<$Q$3,R6>TIME(14,0,0)),R6-TIME(14,0,0),AND(O6="Complex",R6>TIME(12,0,0)),R6-TIME(12,0,0),0)
U6	U6	=IF(O6="Rigs&Barges", IF(Q6>=0.7,IF(R6>TIME(20,0,0), R6-TIME(20,0,0),0),IF(R6>TIME(14,0,0), R6-TIME(14,0,0),0)),IF(AND(O6="Complex",R6>TIME(12,0,0)),R6-TIME(12,0,0),0))
T7	T7	=SWITCH(TRUE(),AND(O6="Rigs&Barges",Q6>=0.7,R6>TIME(20,0,0)),R6-TIME(20,0,0),AND(O6="Rigs&Barges",Q6<0.7,R6>TIME(14,0,0)),R6-TIME(14,0,0),AND(O6="Complex",R6>TIME(12,0,0)),R6-TIME(12,0,0),0)
U7	U7	=IF(O6="Rigs&Barges", IF(Q6>=0.7, IF(R6>TIME(20,0,0), R6-TIME(20,0,0),0), IF(R6>TIME(14,0,0), R6-TIME(14,0,0),0)), IF(AND(O6="Complex",R6>TIME(12,0,0)),R6-TIME(12,0,0),0))

 

[Alex Blakenburg]

In my data you will see below in my data sheet result in meant to be 0.50 but am getting 0:0 but i really don't where the problem is laying

The formula2 @jorismoerings provided definitely works. However it is reliant on having a percent value that meets the criteria in Column Q.
In the Test data you posted in #3 you have all the % set to 0 which does not meet any of your criteria.
Change the 0% in Row 9 column Q to 65% per your first posting AND format the Formula 2 column as h:mm and you should get your 0:50.

 

[Lukma]

Hi Jorismoerings

Thanks and am not saying formula not working the forum as been major support to my work in office during issue so Alex please do not get me wrong.

However i was able to Add this to the Percentage in formula and am able to get the result, but please check if all is ok Please Jorismoerings

Appreciate always

Many Thanks

[=IF(O13817="Rigs&Barges", IF(Q13817="*>=0.7*",IF(R13817>TIME(20,0,0), R13817-TIME(20,0,0),0),IF(R13817>TIME(14,0,0), R13817-TIME(14,0,0),0)),IF(AND(O13817="Complex",R13817>TIME(12,0,0)),R13817-TIME(12,0,0),0))]