We help IT Professionals succeed at work.

Response times in Excel

113 Views
Last Modified: 2017-03-23
Hi, I've been testing with users and recording their response times during the period of an hour and a little bit after that hour.  Now that i have the exact response times, I'm trying to figure out the correct formula to calculate the percentage of response times after 15, 30, and 60 minutes in Excel.  Does anyone have any thoughts?

Attached is my chart
Atl-Time-Test.xlsx
Comment
Watch Question

Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What for example does "19:01:49" represent? Hours/minutes/seconds?
Kelly Denise HortonConfiguration Management Analyst

Author

Commented:
yep, hours:minutes:seconds
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
So did that user respond in 19+ hours or 1 minute and 30 seconds?
Kelly Denise HortonConfiguration Management Analyst

Author

Commented:
1 minutes and 30 seconds.  Sorry if my chart is confusing.
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
No problem I just wanted to make sure. Is a solution that involves user defined function  (in other words VBA code) OK?
Kelly Denise HortonConfiguration Management Analyst

Author

Commented:
i don't know what that is, but if it's easy to understand and translate....yes
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What it means to you is that you'd have to save the workbook as an xlsm file rather that an xlsx file. Other than that it would be like using any other formula.
Kelly Denise HortonConfiguration Management Analyst

Author

Commented:
oh ok.  let's try it.
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Double check these percents. They represent the number of values greater than or equal to 15 minutes (for example), divided by the number of rows (ignoring the header, row 2 and and the row that contains "Test End".
29008425.xlsm
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Slight change.
29008425a.xlsm
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Hmmm. I just thought about user CC. Is that the next day?
Kelly Denise HortonConfiguration Management Analyst

Author

Commented:
I was wrong....instead of calculating after 15, 30, and 60 mins....I need to figure out the percentages of those that responded within those times (within 15 mins, within 30 mins, within 60 mins).  Does that change the formula at all?
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
My percents should show the results for 0 to 15.0 minutes, 0 to 30.0, and 0 to 60 minutes.

If you want 0 to 15.0 minutes, >15.0 to 30.0, and >30.0 to 60 minutes then let me know.
Kelly Denise HortonConfiguration Management Analyst

Author

Commented:
could you do the 0 to 15, 15 to 30 and 30 to 60?  I've been wracking my brain and I just can't figure this out!!!!
Protect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Kelly Denise HortonConfiguration Management Analyst

Author

Commented:
thank you sooooo much!!!!  I was able to get this to work.
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Please don't forget to close this question.
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2016

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions