CODEWITHSUNDEEP
excelexcel-formula
user12095530
user12095530

Reputation: 1

Time difference in percent

I am calculating SLA times and need a simple math/excel formula to show the right results.

If the Target is 6 hours and the tech was there in 0 minutes what is the appropriate %? Not a 100%, that would be if the tech went there in 6 hours, right? If he went there in 3 hours it would be 50%, easy. If he went there in 9 hours it would be 100something percent. I can't wrap my head around this without showing negatives. 

=IFERROR(IF(W9="00:00",0,X9/W9),0)


=IF(OR(LEN(W23)=0,LEN(X23)=0),"",IF(AND(LEN(W23)>0,W23+0=0),X23/TIME(0,1,0),X23/W23))

both giving me the same false results

Actual Target     Expected Result
00:00   06:00   Big question
14:30   06:00   over 200$
61:29   08:00   largely over 100%
04:37   08:00   some 50%
07:13   08:00   some 100%

Upvotes: 0

Views: 132

Answers (2)

Markus Bekken
Markus Bekken

Reputation: 101

The following expression:

=IF(A1/B1=0;"Not started";IF(A1/B1>1;"More than target";A1/B1))

...will produce this table:

Table displaying how the formula will work in practice

Upvotes: 0

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 12008

Just divide and apply % format to those cells:

enter image description here

The formula I've used in C1 and drag down is just =A1/B1

NOTE: About that 00:00 you'll need to set a criteria, but clearly if the tech was not there less than a minute, it almost a 0%. You'll need to set a rule to define what you want to do in those cases

Upvotes: 1

Related Questions