=IF Function to Score AHT

[RamsesOR]

Very good evening, a pleasure to say hello.

I was wondering if you could help me with the creation of the following formula:

I've been all day trying to figure out a correct formula to represent the% of Score for the metric of AHT (Average Handle Time) that is, the time an agent lasts during a phone call.

Now, what I'm trying to do is first establish a range of between 400 to 700 seconds, where a percentage value of 25% could be put into play within that range. That is, 400 (less or equal) would give 25% and 700 (greater equal) would give 0%. That is, 450 would give a percentage of 23.78% (to give an example) implying that the less time an agent spends during the call, the higher the percentage granted.

I have tried with the following formula, but I can not do it in reverse. Practically, the formula increases you from 400 to 700, where 700 is 25% and 400 is 0%.

There is no way to do the inverse percentage, that is, the less seconds the agent has, the greater the percentage is in favor.

= YES.ERROR (YES (J4> = 650.0%, YES (J4> 0.25%, (J4 / 650) * 25%)), "-")

Any way on how to achieve it?

Thanks in advance.

[Roel Jongman]

I would use

=0,25-(J4-400)/300*0,25

where
J4 is the score in seconds of the agent
300 is the difference between the 400 and 700

[RamsesOR]

Hello Roel Jongman,

That is so helpful.

What if I would like 400 to be the limit to grant 25% and if is lower than 400, just always keep it at 25% as a limit. Same happen to 700 granting 0% above that amount of seconds?

Regards,

[Roel Jongman]

then we just min and Min the scores

=MIN(0.25,MIN(0,(J4-400))/300*0.25)

Edit: Hold on give me a minute to get it right.. I was being a bit of a showoff by fast resonding

[RamsesOR]

Hehe,

Thank you very much for your assistance.

If it is not much to ask, would it be possible for you to do it in a =IF function?

I'm trying to add this metric on:

=IF.ERROR(IF(COUNT(D12,J12,P12,V12)<3,"-",(IF(D12<80%,0%,IF(D12="-",25%,D12*25%))+IF(J12<-649,25%,IF(J12>=650,-25%,(J12/-649)*25%))))+IF(P12<80%,0%,IF(IF(P12="-",25%,P12*25%)>25%,"25%",IF(P12="-",25%,P12*25%)))+IF(V12="-",25%,(V12/100%)*25%),"-")

Which J12 would take that formula into it.

Would it be possible?

Thanks for your patience.

[Roel Jongman]

Ok so mr Showoff took some time to come up with ...

=MIN(0,25;0,25-MIN(300;J4-400)/300*0,25)

Edit, oh missed your post there, but that is a bit to complex formula to do from a screen.
that would need an example file to make it work.

[RamsesOR]

I really appreciate your help,

Here is the link of my excel work:

mediafire.com/file/q0rab99a90ezs8y/EXAMPLE.xlsx

If you have time, I would be more than pleased to receive your help.

Practically, is the AHT table.

Regards,

[JohnTopley]

Please post a file on this forum as many members will not access file-hosting sites.

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Roel Jongman]

Hi RamsesOR

That is a bit of tonguetwister for a formula you got there but I managed to work something out.
And although my formula above gives the correct result I have "reworked" it to fit the logic of the other parts of your formula so it now comes to

=IF(OR(J4="-",J4<=400),25%,IF(J4>700,0%,25%-(J4-400)/(700-400)*25%))

ofcourse this could be shorter by using /300 instead of (700-400), but I also like to make complex formula a bit more userfriendly. If at some point you deside to lower of raise the criteria of 400 or 700, you will almost automiticly also chance the other 400 or 700 value in the formula, whereas if it reads /300 300 you might forget to recalc the new difference between high and low boundries and have wrong percentages as a result.

I also made a breakdown of your entire formula to understand the other parts of the formula and I saw some simplyfications that might make them more readable.
See the attachement I of the breakdown of your formula's in column AB and my replacements in column AC.

[RamsesOR]

Oh god, thank you very much four your assistance and patience.

That was so helpful.

You are such a good guy.

I appreciate it.

I hope you have a wonderful day.

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.