I need to calculate the employees incentives based on target achievement in different scales. In attachment sheet i filled the output manually but i need the actual formula .
View 11 RepliesI am a part of an construction company, I am looking to calculate Rig Pay, Truck pay and Perdiem for each employee based on an Excel Spreadsheet pulled from the Time Card Lines out of our accounting software. I am struggling terribly, and need assistance. The Calculations are as follows
Every Employee has multiple lines in the Time Cards sheet.
Rig Pay, Truck Pay, and Perdiem come from the Employee file in our system
If employee id is the same, job number is the same, cost code is the same, calc rig pay * hrs
If employee id is the same, job number is the same, cost code is the same, calc truck pay * hrs
If employee id is the same, job number is the same,Data, and cost code is the same, calc Perdiem /day
The final result i should see how much each employee charges rig pay, truck pay, and perdiem per job, and cost code.
I am trying to write an IF statement. The IF statement should calculate BONUS for an employee GREATER THAN 50 YEARS OLD.
View 4 Replies View RelatedIn a sheet, like the example, I have monthly shift schedules for employees. What I want is to count the total shifts for Saturday and Sunday, separately Night (N) and Morning (M)/Afternoon (A) shifts, as in columns with red.
ABCDEMNOPQRAGAHAIAJ1JUNE 20132DATE1234121314151617....
HOW MANY "N" HOW MANY "M" AND "A"3SATSUNMONTUEWEDTHUFRISATSUNMON....
AT SAT-SUNAT SAT-SUN4Employee1NNNNNMM--A205Employee2MMMM-NNNNN246
Employee3AAAAM--MMM067Employee4--MM-AANNM408............................................9EXAMPLE
I have to calculate employees work hours for overtime.
in the timesheets:
A B C D E
1 ID Date Code Hours PayMethod
2 A123 4/14/14 TRN 20:00 Regular
3 A123 4/14/14 TTT 15:00 Regular
4 A123 4/14/14 TRN 13:00 <----- total for cell D2,D3, and D4 is more than 40
A B C D E
1 ID Date Code Hours PayMethod
2 A123 4/14/14 TRN 20:00 Regular
3 A123 4/14/14 TTT 15:00 Regular
4 A123 4/14/14 TRN 5:00 Regular <----- to make 40
5 A123 4/14/14 TRT 8:00 Overtime <----- 13-5=8, so i have to write down 8 here for overtime
How can I do this?
I want to make a command button for macro to perform this.
I am trying to write a command to calculate the commission for my employees. There commission is based on the spread between sale price and cost. For example:
If Profit is between $1.00 and $2.00 - commission = 15%
If Profit is between $2.01 and $4.00 - commission = 20%
If Profit is between $4.01 and $6.00 - commission = 25%
If Profit is > than $6.00 then - commission = 30%
I am able to calculate the first level ex: =IF((C3-B3)<=2,"15%") It Displays the 15% in the formatted cell. (C3-B3 is the profit spread). How can I include the other 3 commission levels in the formula to display the correct commission % based on profit spread?
Im having a hard time creating the formula to calculate the min number of employees to complete a number of jobs that come in each day and need to be finished in 18 days or less.
Data that I have:
Forecast of the number of jobs for 251 days ( jobs range from 10 -80 per day) Each job needs to be finished in 18 days or less we need to staff a fixed number of employees for the whole year each employee can complete 4 jobs per day no weekends
i have excel 2010 and crystal ball
I have a spreadsheet representing a month where I am trying to figure out different scenarios for employees. One scenario is that an employee could have to move to a temporary position. In that case, I need to calculate the salary payments to temporary employees in a particular work unit. I've tried several different approaches to this problem, but am still getting the error.
One method has been using this sumproduct formula:
I have a target to achieve every month in my department for the number of items completed named BC.I am trying to create a formula so that I know how many minimum items I need to complete every day in order to achieve this target by month end. The target to complete each month is calculated with multiple variables and therefore I am not sure how create a formula to calculate the minimum item to be completed everyday to achieve the target by month end. I have attached the spreadheet and appreciate help. The target to achieve is named "Target BC" in red.
In addition, at the beginning of the month, we only have forecasted numbers and these numbers needs to be replaced by actual numbers everyday.
I am trying to create a formula to calculate the total number of calls that I need to receive to hit the target % if the current % is less than the target %. I have tried various formulae, but not having much luck. Also, it shouldn't show me any negative figures.
A = Departments
B = Calls Dropped
C = Calls Received
D = Current % (C/F)
E = Target %
F = Total (B + C)
G = Required (When I enter B, C and E, the rest needs to calculated automatically. G should tell me the amount of calls I need to receive in order to achieve the target specified in E)
Departments
Calls DroppedCalls ReceivedCurrent %Target %TotalCalls Required to hit TargetDept 1
21083.33%95.00%12 Dept 2
067100.00%90.00%67 Total
27791.67%95.00%79
I'm a teacher and would like to show progress in my pupils achievements by colouring coding cells in excel. For each subject they are given a level, e.g. 2b. If they show progress, they would move up, i.e. to a 2a. The whole progress scale is shown below:
wc,wb,wa,1c,1b,1a,2c,2b,2a,3c,3b,3a,4c,4b,4a,5c,5b,5a,6c,6b,6a,7c,7b,7a
To make the spread sheet visual i would like colour code the cells depending on whether progress has or has not been made. If they go up (e.g. 2b to 2a) i would like the cell to turn green, if they go down (e.g. 2c to 1a) i would like the cell to turn red. If not progress has been made then the cell can stay uncoloured. Each cells colour would only be dependent on the cell directly to the left.
I would like to generate a dynamic list of employees on sheets based on information on Sheet A. Here is my workbook structure:
-Sheet A contains all employees information (Name, division, salary hire date, etc)
-Sheet B is named Div A
-Sheet C is named Div B
-Etc...
Here is what I want to happen:
-Sheets B should pull the employees name and salary from sheet A only for employees of division A
-Sheet C should pull the employees name and salary from sheet A only for employees of division B
-Etc..
Sheet B, C , etc. should update the info and resort every time sheet A is updated.
i am needing to issue a dos command in excel? basically i need to send a target link to a file. i cant use a hyper link for several reasons, and this is the only way i know how to go about this.
View 2 Replies View RelatedI am developing a multiple sheet workbook with lots of shapes - with another person. The shapes are moving if he opens the file when I save it and they also move when I open the file after he saves it. So, I coded in the beforesave function, I save .top and .left position of every shape to a hidden sheet. On workbook open, I re-position all Shapes based on their saved locations. Both of our screens are set to the same resolution... What I think it is - display scale. His is set to 100%, and mine is set to 125%! Anyway to adjust for this? I cannot find out how to find / change the display scale with vba.
View 3 Replies View RelatedI have 3 charts on 3 seperate worksheets. The data for each is sourced from a list of up to 100 but only the top 20, or all if less than 20 in full list, are displayed on the chart. A seperate worksheet ("Calcs") is used to sort all the data so that just the top 20 are sourced for the charts and they are displayed in descending order from max to min and alphabetically in the case of 2 or more names having the same value.
I have attached one ot the charts.
I need the y axis scale to auto-adjust to suit the max and min series 1 values. I need the y axis maximum to be 5 more than the max series 1 value (unless the value is 5 or less when axis should be 10) and the min y axis to be 3 less than the min series 1 value (unless the value is 3 or less when the axis should be 0)
I have done the formulae to sort out these conditions and put the required y axis values in a cell which the copies across to the chart sheet with the required max in cell M1 and the min in cell L1. These values will change when another macro runs.
I searched this site for the code to auto-adjust the scales and found one which I have tried to edit to suit my needs. This is the code I have ended up with, placed within the worksheet containing the chart....
I want that Excel writes in a row the a sequence of musical notes corresponding to a certain tonality;
I have in one cell the musical notes, which are validated data that comes from a list and I could pick one from this list:
C C# D D# E F F# G G# A A# B
chosen one of the notes, excel would write down in a row the corresponding chromatic scale for that note. example - choosen A:
A A# B C D D# E F F# G G#
As you see, the scale begins with A, and then when it gets to the end of the list (tone B) it goes back to the beginning (C). In other words, what I want to implement it´s sort of a circular validated data.
I have a spreadsheet at work . I am tracking the quality target for the department and I need to create a formula that adjust accordingly to the target set for month end which is 90%.
I have build in the foreacasted numbers for the whole month but I need the formula to indicate the minimum error points needed to achieve the target of 90% for month end when I replace the forecasted numbers on a daily basis with actual numbers.
I have attached the spreadsheet which will be clearer.
I am trying to figure a formula that will give me a monthly increase with a annual target. Example:
2006 sales were $100,000 and December 2006 sales were $9,000.
For 2007 I would like a 10% increase in sales which means I should end up with $110,000 at the end of 2007.
The formula should figure a gradual sales increase each month until December 2007. I am assuming the business sales trend is upward with no seasonality so that January 2007 should be the annualized increase over December 2006.
.
I have used the formula =100000*((1+.1)^(1/12)-1) which works for 2007, but it gives me a large jump between December 2006 and January 2007 and then a slight increases each month.
I have a sheet (example is attached) that lists multiple jobs and their related tasks. I want to know if it's possible to create a conditional formatting that says if the percentage complete under the "task" is under 100% and within X number of days of the target date, it's red/yellow/green.
So, if a target date is 70-100 days out; 0%-10% - RED, 11%-45% - YELLOW, 46%-100% - GREEN
If a target date is 40-69 days out; 0%-45% - RED, 46%-75% - YELLOW, 75%-100% - GREEN
If a target date is 20-40 days out; 0%-60% - RED, 61-90% - YELLOW, 91%-100% - GREEN
If a target date is 0-20 days out; 0%-85% - RED, 86%-95% - YELLOW, 96-100% - GREEN
If a target date has passed, Anything under 100% is RED.
I have a problem here in calculating the Daily sales target based on Monthly Targets and Year End Target.
I am attaching the file herewith which has Yearly & Monthly Targets defined. Need calculating Daily targets which should match with Monthly & Year end target .
I have the split of day wise sales for a week as well in another tab. However not able to get the exact monthly target as listed .
I have a list of issues and depending on their priority would require a different target closure date e.g. low priority = 90 days, medium = 45 days, High = 10 days.
So if I have the following columns:
Issue No (Unique Ref #)
Date Raised
Priority (Low/Medium/High)
Target Closure (based on the pre-determined criteria as detailed in above paragraph)
Can somebody suggest a formula to enter in the "Target Closure" column so it is automated?
how to allow both these codes to work on say sheet 1. I can get one to work just fine. So, any tips on how to get them both to work on the same sheet would be great.
Private Sub Worksheet_Activate()
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel. Range)
With Worksheets("Sheet1")
If Not Application.Intersect(Target, .Range("n1:n200")) Is Nothing Then
If Target = "S" Or Target = "s" Then Target = "Submitted"
If Target = "A" Or Target = "a" Then Target = "Approved"
If Target = "I" Or Target = "i" Then Target = "Investgating"
Else
End If
End With
End Sub
and this,.............
IF key of C has notes CDEFGABCDEFGABC progression, then notes
1-2-3-4 : 2-3-4-5 : 3-4-5-6 will result in C D E F D E F G E F G A respectively
I want to create note patterns regardless of scale type: Major, minor, modal etc in each of 12 keys. Music software exists but simple EXCEL formulas should be available or easily created.
I have main worksheet (target a) that I am trying to populate data from target(worksheet) b. The data I am trying to get from target b changes every month,(declining balance) based on a new month. So how can I get financial data from different cell each month from "b" into same cell in "a"? (so "A" # would be overwritten in same cell based on new # from "B". I have tried VLookup but can't be doing something correct.
View 2 Replies View RelatedI am using a piece of track changes VBA code mentioned on this site, which among other things creates a new column that reports the number of any cell that has been changed (e.g., $K$32). What I would like to do is, next to that cell, report the title of the row in which that cell appears. In other words, if someone changes cell $K$32, for easier reference I'd like others to be able to see that this cell appears in a row titled "New Sales". I'll be happy to clarify with more specifics if need be.
View 2 Replies View RelatedI been working on excel sheet that will distribute 200 items between the employees that are available to work. I've attached a copy of the workbook. First I need to look at the quantity of employees I have working that day. Second Distribute the items to each one of the employees. Third give the first employee the first piece. Fourth give the next available piece to the last person with a zero piece in their list.
book8.zip‎
How can I protect by workbook from ex-employees and the competition from using (I am aware of the weak security measures Excel offers, but something is better than nothing)? Each worksheet is password protected to slow down novice Excel users. However, ex-employees know the password. I was thinking if there was a way to have a changing password (which only a couple people would know) or have the worksheet look for a hidden file on our network, that one of these methods (or another) might work.
View 6 Replies View RelatedI am creating a employee time sheet.
I am doing calculation.
I used total hours formula is =(I5-B5+(I5<B5))*24
and also worked hours formula is =((I5-B5-(I5<B5))-(D5-C5+(D5<C5))-(F5-E5+(F5<E5))-(H5-G5+(H5<G5)))*24
Problem is don't show the correct time of total hrs & worked hrs
Here i am attaching my timesheet format.
I am trying to get a count of supervisors and employees at any given time during a few day span. I think maybe a pivot chart might work but I'm stuck.
In my example I have a number of supervisors and employees in separate groups. I would like to type in two dates and two times for an in between time and get the total amount of employees working during a certain time.
Count of Employees at any given time.xlsx
After much searching today I've been unable to find a way to do this. All my searches resulted in some generic looking site looking to sell me some scheduling software.
I have a team of 7 people and I need one person to work a late shift each day of the business week. M-F
I would like a way to have the schedule automatically advance to the next person.