Solved: Multiple If

AllanBerces

Hi good day can anyone help me on my calculated column with multiple if.

- IF % Complete is equal to 0 then Estimate hrs

- IF % Complete is equal to 100 then Actual hrs

- IF % Complete is greaterthan 75 then Actual hrs
- IF % Complete is lessthan 75 then pick value which ever is higher in Estimate hrs or Actual hrs
- IF Estimate is Blank then use Actual hrs

OUTPUT

JOB No	% Complete	Estimate hrs	Actual hrs	Final hrs
23284443	50.00	36	15.72	36
23284900	100.00	260	10.31	10.31
23283999	74.00	224	89.82	224
23284504	70.00	224	68.61	224
23284310	100.00	212	85.98	85.98
23247663	50.00	248	308.87	308.87
23111481	100.00	224	11.58	11.58
23273038	80.00	248	170.88	170.88
23247636	56.00	212	435.35	435.35
23247667	100.00	224	52.74	52.74
23274821	43.00	26	62.12	62.12
23284009	67.00	212	41.6	212
23267333	34.00	224	92.65	224
23285786	88.00	212	110.4	110.4
23284304	88.00	212	27.23	27.23
23284032	90.00	215	384.98	384.98
23247677	73.00	48	221.21	221.21
23247660	23.00	248	160.26	248
23284036	0.00	215	86.81	215
23284057	0.00	308	130.73	308
23284386	0.00	215	3.47	215
23284776	56.00	96	212.19	212.19
23284842	76.00	72	19.72	19.72
23284694	43.00	24	170.12	170.12
23284052	23.00	215	138.61	215
23188629	0.00	284	196.07	284
23284311	0.00	212	24.79	212
23284094	89.00	212	71.36	71.36
23284084	67.00	36	91.67	91.67
23273129	77.00	212	83.14	83.14
23284411	98.00	212	48.65	48.65
23207647	89.00	224	217.48	217.48
23284649	56.00	224	179.03	224

qqqqqwwwweeerrr

Hi @AllanBerces ,

Here is my solution:

Final Hours =

VAR Pct = Sheet1[% Complete]

VAR Est = Sheet1[Estimate hrs]

VAR Act = Sheet1[Actual hrs]

RETURN

SWITCH(

TRUE(),

-- 1. If Estimate is blank : use Actual

ISBLANK(Est), Act,

-- 2. If % Complete = 0 : Estimate hrs

Pct = 0, Est,

-- 3. If % Complete = 100 : Actual hrs

Pct = 100, Act,

-- 4. If % Complete > 75 : Actual hrs

Pct > 75, Act,

-- 5. If % Complete < 75 : pick higher of Estimate vs Actual

Pct < 75, MAX(Est, Act)

)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: www.youtube.com/@Howtosolveprobem

Regards

View solution in original post

qqqqqwwwweeerrr