G
Guest
I've put this out here before but have yet to get any response.... is it too complex or detailed or not detailed enough for a response??? At least let me know if I need to re-direct my question???
This is pretty detailed, I hope you can follow my train of thought. Normally I would attempt to do this in Access, but since the participants in this project do not have the program or the ability, we will need to do it in Excel. I can't get my brain around where to begin or what tools to use on this one. I think I'm just overwhelmed. We have manually placed in formulas and such to get us through #2. I'm just not sure where to go from there that would be easy for me to get to work and for the end users who have limited skill working with Excel. Also, once this is programed to give us the end results we want, I'm hoping to "dummy proof" this by making some sort of menu screen with push button choices. I've done some simple ones in the past, and I'm sure this will be another question at a later date. I'll worry about that when I get there. Thanks in advance for any advice/help you can give
The data that is collected is as follows
Site Multiple Site
Code Multiple Code
Priority 3 different prioritie
Order Date
Order Time 24 hour cloc
Collection Dat
Collection Time 24 hour cloc
Release Dat
Release Time 24 hour cloc
Total Hours % of an hour based on the difference between the Release Time and the Collection Tim
Also: Each Code has a SET TAT (Set Turn Around Time) based on the three different priorities. i.e. Code 123 has a TAT of 60 minutes for priority 1, 90 minutes for priority 2 and 240 minutes for priority 3. Code 456 has TAT of 20 min, 40 min, and 60 min. etc
With this data, the following needs to be determined
1) Determine if the Order Time is greater than the Collection time.
a. If this is true, designate this as “errorâ€. These records are not used in final calculations
b. If this is false, designate this as “OKâ€. These will be the records used in the final report
2) Calculate the TAT on each record not in error (“OKâ€). Using the Total Hours field, multiply this by 60 to get total minutes. This will provide the TAT for each record
3) Determine if the TAT for each record is greater than the SET TAT for the test/priority in that record AND If the TAT is more than 6% over the SET TAT, a true condition will be used.
a. If this is true, designate this as “overâ€
b. If this is false, designate this as “underâ€
4) For each Test / Priority report out the following
a. % of TAT that meet the SET TAT (those from 3 that are “underâ€)
b. Count of those that meet the SET TAT
c. The Min, Max and Avg. of TAT for each Test/Priorit
REPORT
1) By means of a pivot table or some other means such as that, provide a way for a user to choose which site and test they wish to evaluate. Preferably, a drop down of all available choices within the data table
2) Format as follows
Site [Site]
Code
This is pretty detailed, I hope you can follow my train of thought. Normally I would attempt to do this in Access, but since the participants in this project do not have the program or the ability, we will need to do it in Excel. I can't get my brain around where to begin or what tools to use on this one. I think I'm just overwhelmed. We have manually placed in formulas and such to get us through #2. I'm just not sure where to go from there that would be easy for me to get to work and for the end users who have limited skill working with Excel. Also, once this is programed to give us the end results we want, I'm hoping to "dummy proof" this by making some sort of menu screen with push button choices. I've done some simple ones in the past, and I'm sure this will be another question at a later date. I'll worry about that when I get there. Thanks in advance for any advice/help you can give
The data that is collected is as follows
Site Multiple Site
Code Multiple Code
Priority 3 different prioritie
Order Date
Order Time 24 hour cloc
Collection Dat
Collection Time 24 hour cloc
Release Dat
Release Time 24 hour cloc
Total Hours % of an hour based on the difference between the Release Time and the Collection Tim
Also: Each Code has a SET TAT (Set Turn Around Time) based on the three different priorities. i.e. Code 123 has a TAT of 60 minutes for priority 1, 90 minutes for priority 2 and 240 minutes for priority 3. Code 456 has TAT of 20 min, 40 min, and 60 min. etc
With this data, the following needs to be determined
1) Determine if the Order Time is greater than the Collection time.
a. If this is true, designate this as “errorâ€. These records are not used in final calculations
b. If this is false, designate this as “OKâ€. These will be the records used in the final report
2) Calculate the TAT on each record not in error (“OKâ€). Using the Total Hours field, multiply this by 60 to get total minutes. This will provide the TAT for each record
3) Determine if the TAT for each record is greater than the SET TAT for the test/priority in that record AND If the TAT is more than 6% over the SET TAT, a true condition will be used.
a. If this is true, designate this as “overâ€
b. If this is false, designate this as “underâ€
4) For each Test / Priority report out the following
a. % of TAT that meet the SET TAT (those from 3 that are “underâ€)
b. Count of those that meet the SET TAT
c. The Min, Max and Avg. of TAT for each Test/Priorit
REPORT
1) By means of a pivot table or some other means such as that, provide a way for a user to choose which site and test they wish to evaluate. Preferably, a drop down of all available choices within the data table
2) Format as follows
Site [Site]
Code
Code:
TAT TAT TA
SET TAT Count % Meeting SET TAT Action Average Min Ma
Priority 1 [SET TAT] # of priority 1 [all “under†records/Total Count]
Priority 2 [SET TAT] # of priority 2
Priority 3 [SET TAT] # of priority 3
[Total Count]
3) Those < 90% in the “% Meeting SET TAT†column, place “INVESTIGATE†in “Action†column