First Contact Resolution Calculation

[Dominic.Brice]

Hi All

I am hoping that someone out there can help me. I am in the process of building a spreadsheet to calculate the % of contacts that have been resolved upon first speaking to someone. If we were to receive a call from the same phone number within a specific time frame then we would not count this as resolved.

For arguments sake the time frame would be 3 days or 72 hours. This may change over time so I am looking to reference a cell with this information in to make it dynamic and adjustable.

I have attached a spreadsheet with an example of the data, and what it would look like if correct.

Really hoping that someone can help with this

Thanks
Dm

[Glenn Kennedy]

I am assuming (you did not say) that the formula is needed in F6 and downwards.

A wild guess, as your expected results seem odd to me:

=IF(SUMPRODUCT(($E$6:$E$18=E6)*($B$6:$B$18<=B6+3))>1,1,0)

Please justify your expected answers in the yellow shaded cells.

[Dominic.Brice]

Hi Glenn

The formula is required in column F.

Also the yellow shaded cells would return that result as the phone number has not made contact again within 3 days of the date on that line. Should the phone number call again within 3 days then it would return 0.

I hope that, that makes sense however, if you need me to clarify anything further please let me

[Glenn Kennedy]

OK. So I was right about the formula. BUT

I asked to you explain /justify HOW you derived the expected answers in the yellow shaded cells. You repeated your criteria. Those cells do not seem to meet those criteria!!!

[Glenn Kennedy]

Why is F6 a zero? Fa did not call again

[Dominic.Brice]

So for row 7,8,12,16 Those numbers have only called in once. The assumption is that as there are no further calls the call was resolved on the first contact.

Hopefully that clarifies enough for you

[Dominic.Brice]

F6 is zero as I had over typed the value in cell D6 which should have been 1111

[Glenn Kennedy]

OK. I'll look again... but my head is fried!!

[Glenn Kennedy]

Try this:

=--NOT(IFERROR(INDEX(B7:B18,MATCH(E6,E7:E18,0)),"")<B6+3)

Note that the ranges are NOT $'d to make them absolute.

[Dominic.Brice]

That is amazing!!! thank you for your help and sorry for any confusion that I may have caused.

I do have 1 question though, The plan for this is to be used on a much larger data set to amend the formula will it be as easy as just extending the formula down to the end of the data or is it more complex than that?

The end data will easily get to 1000s of lines quite quickly

[Glenn Kennedy]

Just change the 18s to something future-proof. It should then be OK!! Fingers crossed...

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

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