Formula To Calculate Staff Performance
Nov 6, 2008
I am trying to work out a % score for a telephone operator. To explain further, I would monitor a call that an agent takes. The agent starts at 100%. There are 20 points to be scored and they are scored like this
1 - Yes
0 - No
left blank - n/a
B3 counts the number of entries in the column
B2 counts the number of 1's in the column
B28 = B2/B3
Seems to work fine until the agent makes a fail which would enter a 0 in the column. Somewhere I think I need to take account that there are 20 possible fails or passes?
View 6 Replies
ADVERTISEMENT
Sep 8, 2009
I need formula to calculate a fee based on performance. For example I have 4 unique keys with the following performance:
KEYPerformance
2010,000
2120,000
2230,000
2340,000
I need to work out how I can formula drive a fee calculation based on performance which is subject to different ranges:
KEYMin FeeFee 1Fee 2Fee 3Fee 4
201000.10.20.30.4
211000.10.20.30.4
221000.10.20.30.4
231000.10.20.30.4
For example key 20 has a min fee chargeable of 100, however a fee is chargable based on performance as follows:
Fee Range 10-9999
Fee Range 210000-19999
Fee Range 320000-29999
Fee Range 430000-99999999
So key 20 gets charged 0.1% of amounts between 0&9,999, 0.2% of amounts between 10,000&19,999, 0.3% on amounts between 20,000& 29,999 and 0.4% on the rest.
How can this be combined into a lookup/range/low-high formula to extract the correct values????
View 13 Replies
View Related
Jun 26, 2007
I have a list of employees who each have a performance rating (very top, consistent, etc). Each employee is also either above the midpoint of a salary range or below (which is represented by a separate column with 'Yes' for above and 'No' for below).
What I am trying to do is apply a separate set of %'s for each group of employees. So, for the employees who are below, I want to return 10% and 8% for each respective rating, but for those employees who are above, I want to return 8% and 6%. (for example).
View 9 Replies
View Related
Nov 24, 2013
Is there a way I can count the number of different people in a column in any one day. The same persons name may appear more than once on the same day. My table is as follows:
Column A has my list of dates running from A2 to A500
Column E has my list of workers running from A2 to A500
My table of results is in a different workbook but basically what I need is this:
Nov 1 - Count how many people worked on 1st November
Nov 2 - As above
Nov 3 - As Above
Nov 4 - As Above
Nov 5 - As Above
etc
View 6 Replies
View Related
Jul 28, 2014
I need counting the number of staff within a time period. Unfortunately the schedule structure does not allow application of the simple solutions I've found regarding this which results in this being more complicated. My example is attached. My start and end time are in the same cell. I have used the Left() and Right() functions to manipulate the times. I am unable to count the number of occurrences of between the start/stop times of all my employees in the array. The formula also needs to take into account a manually entered time on the actual day.
Basically:
00:00 if F9:F42 = time is TRUE and I9:I42 = "" then count occurrences of 00:00 in scheduled times F9:F42 + if F9:F42 = time is FALSE, then if I9:I42 = time is TRUE then count occurrences of 00:00 in scheduled times I9:I42
01:00
02:00
"
"
Even when I think I've gotten close the midnight time crossover keeps throwing off.
Schd_05_TEST.xlsx
View 3 Replies
View Related
Dec 17, 2012
My boss wants me to take the holiday info from SAGE for 80 employees and create a record on excel. He wants to know what holidays each employee has taken and is due to take throughout the year. As each employee works a different amount of hours and a different shift pattern, SAGE records their holiday entitlement in hours rather than days. I have attached an example of one employees details and if come up with an excel document containing similar information for 80 individuals - all starting on different dates and all having a different amount of holiday entitlement. He wants to be able to look at each employees record for the year and see not only holiday data but sick days too. I don't know where to start with this - I've thought about creating a workbook with 80 pages and create a 12 month calendar for each individual with days off marked on it?
View 4 Replies
View Related
Feb 12, 2012
I have a table on a sheet called Contracted Staff. Table has staff names down the side and along the top is training needed.
Training H & S. fire. General training
Lucy. 5/4/12
Jeff. 5/4/12
Michael.
Dawn. 5/4/12
I would like on a training summary sheet to tell me who hasn't completed the training. I know I can use auto filter to select blanks to get who hasn't done it. However I want to print every training and list on the same sheet.
View 2 Replies
View Related
Nov 10, 2006
I have a relatively small Excel workbook that is using numerous lookup and Match function combinations. I also have a number of dynamic ranges defined and a few UDFs.
Problem is I am encountering some fairly severe performance issues. Changing a single value on a sheet is taking ages.
I suspect it may be due to the workbook dynamic ranges recalculating. Is there a limit to the amount of dynamic ranges in a workbook? And when do they get recalculated? Could it be something to do with sequencing of the calculations?
View 3 Replies
View Related
Jun 20, 2007
I'm trying to write some code that is performing operations on large arrays, using a pair of For loops. In languages like C++ and Fortran, it makes a big difference to the performance of the code which way round you put these 2 For loops, due to the way that array data is stored in the computer memory. However, for VBA, I've no idea which way round this is. For an array, A(i,j), do I want
For i = 1 To N
For j = 1 To M
A(i,j) = Cells(j, i).Value
Next j
Next i
View 9 Replies
View Related
Feb 25, 2014
From the attached data is it possible to create a formula to count the number of staff in post that are line managers?
View 7 Replies
View Related
Sep 17, 2013
Currently I'm working on functionality in my staff holiday spreadsheet and I've come up with this amazing idea for a calendar overview for month by month.Now the sheets I currently have is summary and jan to dec.
I plan on adding the sheets Jan (Cal) to Dec (Cal) now this is when my idea gets nifty - The calendar view will import the information from the month table and will show it on the calendar.
For example X has 1 holiday in July for the 20th - this will show up on the calendar as "x on holiday", I'm still working with spreadsheets so I'm not sure if excel is powerful enough to do this - but is this actually possible?
View 2 Replies
View Related
Nov 13, 2013
How to get the card# from staff ID as lookup value? ask.PNG excel question.xlsx
View 6 Replies
View Related
Apr 9, 2014
I have a table that will have a drop down list in all of the cells in column F that are in the table. (I think I have the drop down list figured out
I want to create a button that makes it so every time a new row is needed for new stock entries etc, the user just has to press the button and it should just create a new row within the table. Simples .
Is this achievable? And if it is, what is the best way to go about it?
Table width is from Column C - Column J.
View 1 Replies
View Related
Feb 26, 2010
It is a formatting problem based on an INDEX formula i believe, but I am unsure where to start.
I have added a template where I have shown examples of what i want to achieve, the explanation is a lot clearer.
View 2 Replies
View Related
Jun 10, 2014
I have 20 staff members that I need to schedule in half day shifts (AM & PM) across three different locations for the month of July.
I want to make sure there is no overlap/duplication for any one staff in a time slot. Nor do I want any one staff member to work an AM and PM shift in the same day.
Ideally, I would also like to avoid back-to-back shifts (e.g., not allow PM on Monday to be scheduled for an AM on Tuesday).
Lastly, I would like to have the number of shifts be be as equitably distributed across all of the staff members as well.
Any way that I can do this in Excel?
View 1 Replies
View Related
Oct 2, 2008
Morning all. I'm trying to use VBA to generate autoshape arrows to track changes in values.
Something allong the lines of Column B (Jans data) C (Febs Data) E (autoshape arrow, green up, for improvement, or red downwards for a worsening).
The problem I'm having is postioning my autoshapes. The only way I can do it at present is to keep all columns and rows a standard width, and position using multiples of those.
Is there anyway to set the autoshape to the cell height and width, and position it within the boundries of the cell? This way I don't have to worry about rewiting my code every time I change a cell width?
View 7 Replies
View Related
Nov 23, 2009
I am encountering a situation wherein each line of VBA code is essentially compiled as I type on that line, instead of when I move to a different line. For example, if the code I entered is incorrect, the compiler waits about half a second and then turns it red. Also, if I enter a space, it deletes it immediately.
View 4 Replies
View Related
Jan 17, 2013
I have thousands of rows of data sorted chronologically in a flat list. What I'm looking to do is have a pivot table or formula which picks out the highest value for a given person. Sound simple?
Okay,
- column A: date
- column B: name
- column C: exercise weight
- column D: exercise rep's
- column E: exercise predicted max (essentially, weight x reps)
Now, I have a pivot table which picks out each persons highest "exercise predicted max" (right click on table, field settings, MAX) but I want to know the actual weight they've lifted and how many reps they've done with it.
So basically, I want a simple table which shows me the date, persons name, heaviest weight they've lifted and how many reps they did with it. I've used the CONCATENATE function to group the "exercise weight" and "exercise rep's" in to one cell but the pivot doesn't return any values (all cells in pivot = 0).
View 1 Replies
View Related
Jun 4, 2008
I run a simple macro loop to clean some data across nine columns. The purpose is to collapse the data in the columns so that column 1 has the first value found in that row, for the set of columns. For instance, if columns 1-4 are empty, it deletes / shifts everything left until the first column is not empty. Then it goes to the next row and repeats. Data can range from a few rows up to 6000.
[I have a period in the data as the cell content to evaluate]
Sub A_Rollup_collapse()
StartT = Now
Dim Col As Integer
Col = Range("IV1").End(xlToLeft).Column - 9
LastR = Range("A60000").End(xlUp).Row
Application.ScreenUpdating = False
For R = 2 To LastR
Do While Cells(R, Col) = "."
Cells(R, Col).Delete Shift:=xlShiftToLeft
Loop
Next R
EndT = Now
Application.ScreenUpdating = True
msg = "Done. Duration: " & Format(EndT - StartT, "h:mm:ss")
MsgBox (msg)
this macro (B) runs after another macro (A) that populates the nine columns with data using vlookups. Macro (A) It builds out a chain of information from col 2 to col 9, converts to values etc. Nothing odd.
When macro (B) is called right after running macro (A), it can take about one minute for 500 rows of data.
When I save and close the workbook, reopen it and run macro (B), it only takes one second.
When I insert a ThisWorkbook.Save between the two call statments, macro (B) still takes over a minute.
A minute is not too bad but when I'm dealing with thousands of rows, the difference is more like 30 seconds vs. 9 minutes which is a problem.
View 9 Replies
View Related
May 1, 2009
I've a sheet that records performance on individuals, what it does is collate the performance daily on certain things.
Most are simple plus/minus one and i have control buttons that do this,what it does is: On click it adds one to a different sheet, or if to remove it minuses one, and there is a total box to sum these figures.
But there is also some provision to add collected money value, and what i would like to do is have a function that deletes the last figure added to the financial transactions
This is the code
If Target.Address = "$A$6" Then Sheets(2).Range("A65536").End(xlUp).Offset(1).Value = Target.Value
So for example
in A6 £10.50 is added, but i can't think how i can provide a service that will delete the last figure in Sheet 2 range A, as this will be constantly changing.
View 9 Replies
View Related
Aug 19, 2006
I have built a fairly complex app in excel which takes 50 webqueries and makes a table out of specific data. It generally takes ~34 seconds to run through the webqueries, and when its done, it starts all over and runs the 50 queries again on 30 second delays. My problem is that for about 5-10 of these cycles, everything works fine, but after that, the time per set of queries starts going up...in the range of 2:00 or more for the same 50 webqueries that originally took ~34 seconds.
Without posting each of the userforms and modules, is there a general way to clear out the memory or see whats bogging down the process or anything between sets of queries to maintain performance? the webquery itself is a
With ActiveSheet.QueryTables.Add(Connection:= "string" & variable
statement that gets called each of the 50 times. The only thing I can think of is that somehow variables or some other procedure isnt clearing after each of the sets of 50 queries runs. Is there any way to check that or make sure it is cleared before it begins the 50 query set again? I noticed this mostly by watching my "physical memory avaliable" drop a small amount each time the set ran, and I had to quit excel before it would return to its original amount.
View 6 Replies
View Related
Aug 24, 2006
why it takes about 100 times longer to set a value using a named range. The code below demonstrates the huge difference between Range("A1") and Range("my_range")
Public Sub testloop()
Dim counter As Long
Dim start As Double
start = Timer()
Application.Calculation = xlCalculationManual
Application. ScreenUpdating = False
For counter = 1 To 34000
Range("my_range") = 9999 ' 101 seconds
'Range("A1") = 9999 ' 1.2 seconds
Next counter
View 7 Replies
View Related
Jul 5, 2014
I have this calendar with a total of 31 staff assigned. I need to assign two staff to cover each day not grayed out utilizing the staff tab from the top (A1) to the last (A31). Need to go to next name if blank.
Example:
Bob
Bill
James
Kathy
Should show
Bob
Bill
then
James
Kathy
not
Bob
Space
Bill
James
View 3 Replies
View Related
Aug 4, 2014
I want a formula to calculate the average number of staff working per week in the attached data.
View 9 Replies
View Related
Oct 22, 2007
I have a sheet set up to record free pour tests for my bar team.
Column A has the date.
Alternating columns from B (B..D..F.. etc) hold a drop down with the staff names
Alternating columns from C (C..E..G.. etc) hold a drop down with either pass or fail as the result.
What I need to do is count the number of times a particular staff name appears, but more importantly how many times they pass or fail.
I can easily count the names, but how do I count if they have pass or failed?
View 12 Replies
View Related
Aug 31, 2012
I'm fairly new to excel and trying to create a simple spreadsheet to provide a log of staff allocation. I've created a first a sheet displaying a table with a week of rota. The first column lists where the staff are placed, the first row the date and inbetween the staff member covering that date. There are 5 further sheets, one for each member of staff with a simple table - first column is date, second is allocation.
Back on the first sheet below the timetable is a further grid showing on the first column staff names, and below each date a formula =VLOOKUP( B$3,David!$A:$Z,2,FALSE) pulls information from the 5 sheets displying whether a memeber of staff is on leave or there allocation on that particular day.
The problem i'm having is trying to get the 5 simple staff sheet to pull data from the Rota ie. I need say B3 to look at the date in A3, find the same date on the Rota sheet, look for the relevent staff name below it (if present), and display the allocation from the first column on the same row as the staff member.
I've tried a combination of Vlookup with Match and Index with Match but nothing seem to work.
View 7 Replies
View Related
Feb 25, 2013
I have data showing when staff arrive at, enter after clearing security and then exit a building after completing their task. The data for each cell is a combined date and time as opposed to separate cells for date and time.
I am trying to ascertain how long our security staff take to clear persons waiting to enter the building and also how many people at any one time are actually in the building (after being processed through security).
I also need to ascertain if particular issues arise at certain days of the week (eg Mon morning) and so need to add a further column which shows the day of week using the date/time data.
A sample of the data is as follows;
Staff No:
Arrival time
Processed time
Exit time
1
26/02/2013 07:13
26/02/2013 07:22
26/02/2013 13:52
[Code] ........
I had thought about trying to display the data using a a stacked bar chart but I still need to look at any particular date/time and ascertain how many persons are in the building without manually counting the rows!
View 6 Replies
View Related
Mar 4, 2014
What I'm trying to do is show monthly performance based on products of a few different areas. Where I'm running into problems, is with the month listings. If the report shows one month at a time, I can get it to work with SUMIF formulas, however when there is more than one (and there will always be three).
View 6 Replies
View Related
Jul 3, 2014
I have a home finance spreadsheet that I've been using for a few years and in the last couple of days it's ground to a halt. Whenever I enter a value in a cell and hit [ENTER] there is a long delay with the spinning wait icon (Windows 7) and the Excel window reports "Not responding". Eventually (3-4 minutes) the update occurs and everything is fine.
The workbook has 9 sheets on it but only one summary sheet references the others. The sheet I'm updating has about 2200 rows, 50 columns and the formulas are simple A+B-C types. Performance DOES NOT improve if I change recalculation to manual. The only VBA is some macros to perform tasks and they don't execute dynamically. Adding comments and changing formats responds immediately, it is only updating values that exhibits the problem.
I've tried replacing all formulas with their values (paste special/values) across the sheet but again this didn't work.
View 4 Replies
View Related
Aug 23, 2006
Im having slow performance reading a CSV file into 34000 sheet named ranges. Currently it takes about 8 minutes to read 34,000 records. After stepping through the code Ive realized there are two bottle necks.
1)finding which sheet that the range name resides see getRangeAddress() and
2) actually setting the value to the named range i.e. Range(ra) = dprecord(1)
The CSV file looks like this: <range name>,<data value>
e.g. DPA_1001,99090
Performanace actually seem to slow as the macro runs.
Public Sub readDatapoints()
'Macro readDataPoints
'This macro will read in the a comma seperated value (CSV) file of datapoints.
Dim sFile As String
Dim currentLine As String
Dim delimit As String
Dim counter As Integer
Dim ra As String
Dim fs As Object
Dim ts As Object
Dim dprecord
Dim oldStatusBar As Boolean
delimit = ","
View 9 Replies
View Related
