Formula With If Condition: Calculate Bonus Based On Productivity
Jul 10, 2006
I have a file to calculate bonus based on productivity. The bonus percentage is dependant on productivity percentage and the category of employee. I have given in the below attached file, the range of productivity% and the bonus % for each category. I want to build a formula, to get the bonus % in a seperate table. I tried to do the same using "If" condition, but its not working, as the number of nested IFs are going beyond 7. I have highlighed the column where I want to calculate the formula.
View 5 Replies
ADVERTISEMENT
Nov 6, 2009
i m trying to work out the productivity of employees based on how many hours they work (Time in Back Office). How many pieces of work they complete(Back Office items Completed) if 1 piece of work should take 7 mins. the item in red is what i cant seem to figure out.
View 5 Replies
View Related
Mar 6, 2012
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 Related
Mar 29, 2006
I am updating a sports competition ladder. There are two aspects to compiling the table I would like to automate but have been unable to find out how via the excel documentation.
The aspects are.
1. When a team loses by a margin of seven points or less below the total points scored by the winning team then the team is awarded a single "1" bonus point that contributes to their overall competition points tally.
2. When a team scores four tries or more they are likewise awarded a bonus point.
I require formulas that will automatically calculate the bonus points into a cell.
Situation One
cell 1 = Points For (eg. 17)
Cell 2 = Points Against (eg. 24).
In this instance the team did not win. They scored 17 points and the winning team scored 24 points. Therefore the team lost but lost by seven points and is therefore entitled to one (1) bonus point.
SOLUTION NEEDED - I need a formula that will calculate whether cell 2 is seven or less than cell 1, and if so automatically place a total of "1" in cell 3.
Situation 2
Cell 1 = Tries Scored (eg. 5)
In this instance the team has scored five tries. They have scored more than four tries or more and is therefore entitled to one (1) bonus point.
SOLUTION NEEDED - I need a formula that will scan the number in cell 1. If this number is 4 or more, then a total of "1" should be automatically placed in Cell 2.
I have looked into the excel help but to no avail and the search terms are so broad it was a nightmare trying to search the forums.
View 11 Replies
View Related
Mar 8, 2014
production01.png
I am trying to figure a way to search for a cell that has a specific date and time range. There are several cell titles pending on the activity. I want to find a cell that has a time ** 7:30-15:30 , 15:31-17:30, 17:31-20:30. The end result is to calculate the activity between those time periods based on the data cells.
Example
If the date searched time field ** the activity ranges is 1635 I need to split the time and credit the activity time in the 730-1530 time and the rest on the 15:31-17:30 time
I have been able to do it on a single labor group based on time alone, but when I try to add the DATE to it my numbers go null. Eventually i will need to add 11 labor groups daily for weeks at a time .
Excel 2007
View 3 Replies
View Related
Oct 2, 2008
I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days
17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7
On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.
View 2 Replies
View Related
Mar 22, 2009
I have a column called Average and it have numbers from 0 to 100. I want to set a string on another column based on the value of Average. The condition is
< 50 is negative
= 50 is neutral
> 50 is positive
For example
Column A Column B
45 Negative
50 Neutral
80 Positive
View 4 Replies
View Related
Apr 18, 2008
I'm trying to hard-set the value of the current cell (i.e. remove formula and keep value) if there is data entered into another cell. =IF(C14="",IF($M$5="",G13*($I$6+1),G13+($L$6/ COUNTIF(A$12:A$200,"<"&$M$5))),Set the value of this cell and remove the formula.
View 6 Replies
View Related
Mar 17, 2013
I have workbook with multiple sheets with full of formulas. I need a excel macro which can convert formulas to values based on multiple conditions.
The formulas are in B2 and below.This formula provides results as soon as a value is entered in C2 and one among D2 and E2 cells (see attached excel)I want a excel macro to convert the values provided by formula to actual values as soon as results are obtained.Further, when user delete data in C2, D2 and E2, i want the formula to be working again since if a user enters different data in C2, D2, or E2 the value should get updated.
I found a similar post which addresses some part of my problem but works only with one condition. [URL] ....
View 8 Replies
View Related
May 8, 2014
I have three columns of Data. A is vehicle number, B is miles and C is a Date. I want to be able to pull the miles for the newest date when the user types in a bus number next to the formula. I think It can be done with an array formula but I am not 100% sure on how to do it. Also is it possible if they enter a vehicle number and a date that a different formula finds the miles for the most recent date to the date entered.
Excel Help.JPG
View 6 Replies
View Related
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
Apr 20, 2007
I have a spreadsheet that has staff id in one column and the work items number that they have done in a daily basis in another column.
The actual list is very long. I need to summarize in another column how many work items that they have completed in a daily basis.
I have attached a sample spreadsheet as an example. I would need to summarize in column H based on the staff ID. Some work items are shared by two staff but it will have to be counted as one work item completed for each staff. If work item B123466 is completed both by staff M56 and M54, then it will be counted as one for each.Currently, I am doing this manually with the filter function which is very tedious and often has mistakes. I would like to formularize this task.
View 6 Replies
View Related
Dec 13, 2012
I have a scorecard that looks something like this:
90% or greater=3
80%-89%=2
70%-79%=1
0%-69%=0
I need the "cell" to react accordingly and I'm lost.
View 2 Replies
View Related
Dec 10, 2009
I need a formula to calculate age today based on a person's date of birth. I used to know this but I have not used it for awhile.
View 4 Replies
View Related
Dec 8, 2009
I would like to have something that looks like a running total, but isn't.
I have in a colomn 'rate' and after that a column for each month in a year.
I would like to have at the bottom a total cost, so rate x hours.
have a look at the picture.
is there a formula for this?
ratemayjunejuli
$10 2,02,01,0
$12 2,03,00,0
$10 3,02,05,0
$15 4,02,53,0
total cost $134,0 $113,5 $105
View 3 Replies
View Related
Apr 23, 2009
I am looking for a formula that can change the price of some of the items on sheet1 in column C by the amount found on Sheet2 in Column D. I would like it to base the calculation on column C in sheet2 (so I can choose if I want to add, subtract, multiply, divide, or make the price exact). I would like all prices that don't match these UPC codes to remain unchanged.
Intended Results can be seen on Sheet1 in Column E. Not sure if I should a formula with the Vlookup function or a macro, or maybe there is even a better solution.
Example spreadsheet may be viewed at http://spreadsheets.google.com/ccc?k...WLMyhNJLiPLTfA.
View 9 Replies
View Related
Jul 21, 2006
i am trying to make an excel spreadsheet so i can keep track of my hours and pay at work... i know how to do mult and add but i wanted to know how to do the 2 together.... for example
Hours ST OT Total Hrs Gross Pay
8 2 10 ?
i would like help figuring out the gross pay if say for example my st time rated is 21.21 an hr, and the ot rate is 31.82 how can i get the 8 times 21.21 = 169.68 and the OT 2hrs times 63.64 to show up in the gross pay as a total of 233.32 - what would the formula look like?
View 3 Replies
View Related
Aug 30, 2006
I only want the formula to operate when there are values to calculate and then ONLY refer back to the last calculation. If there are gaps between the calculations I would like those to remain blank. Is that possible?
I have attached an example of what I am trying to do.
View 3 Replies
View Related
Aug 25, 2009
I have in column A a list of investment fund names. In columns B to D is respectively for each fund its Market Value, the Rate of Return and the Rank (1, 2, 3 or 4). I am trying to create a formula that will calculate the market value-weighted rate of return for each ranking.
For example, the 3 funds that rank first have market value weights of 10%, 12% and 13%, and rates of return of 1%, -3% and 5%. The total market-value weighted return for the top-ranked funds would therefore be 0.39% (i.e. 0.10*0.01-0.12*0.03+0.13*0.05)
The formula would first need to look in the ranking column to identify those funds with a particular rank (column D), then calculate their weights by dividing their market value by the total market value of all funds (column B), and finally multiplying these weights by the rate of return (column C), before summing the results to say, for example, that the total market-value weighted return for the top-ranked funds is 0.39%
View 5 Replies
View Related
Jan 30, 2013
I'm looking for a function that calculates a fee deduction based on the 28th of each month.
I'm paying back £200 on 28th of every month starting 28th Feb and was hoping that a formula could keep track of this...
A
B
C
D
1
£1,300.00
=TODAY ()
2
-£200.00
28/02/13
FUNCTION
How to do it but basically I'm trying to put a formula in D2 as follows:
If Today's date (C1) equals C2 I need B1 to reduce by the amount in B2
How to continue it calculating reductions per month by duplicating the formula...
View 1 Replies
View Related
May 14, 2014
I have a form in work which acts as a tick sheet for tasks complete on a construction site. When a task is complete I copy & paste from a key of dates i.e. week ending 11.05.15, this allows me to see what week a task was complete.
Generally I only marked off tasks which were 100% complete but my manager wants me to enter the % of the task complete also, i.e. 80% in the box.
So my question is how can I have the formula assume that any box with a date & shading is 100% and any cell with a number i.e. 80 is only 80% complete. I need it to monitor around 150 cells and give me the overall % complete based on what i have discussed.
View 5 Replies
View Related
Jul 10, 2014
When speaking with parents I want them to enter in their child's birthday i.e. 12/1/1998 in one cell and then automatically generate what grade they should be in the next cell. If the grade year cutoff date is Sept 1.
View 2 Replies
View Related
Aug 6, 2014
I've got a spreadsheet where the start date is amendable and this is what I wanted. E.g. start date is Jun-14 and I want the formula to to calculate annual salary divided by 12 in each month from June onwards until Mar-15 (Financial year is April to Mar) and the same way to not show anything in April or May as they started in June... How can i achieve this please as I tried IF statements and it doesn't recognise dates and years...
Link: [URL] ....
View 1 Replies
View Related
Nov 15, 2013
I am looking to setup a formula to calculate a Fee that is based on the number of users of a service in a month, with the rate varying by the number of users. The first million users in a month will bring in $0.40 per user, for users 1-4M it will bring in $0.30 per user, for users 4M-7M it will bring in .20 per user, and for users beyond 7 million it will be .10 per user. So for example, if a user count was 9 million in a month, the calculation would be ($0.40 x 1 million) + ($0.30 x 3 million) + ($0.20 x 3 million) + ($0.10 x 2 million). I know the answer to the problem is obviously $2,100,000 but I can't build the formula that solves that and can handle instances where the user count is capped in one of the individual brackets. (IE if there are 3.5M users)
I've attached an example spreadsheet : Variable Fee Schedule.xlsx
View 7 Replies
View Related
Apr 9, 2009
Basically a cell might have a number with 3 decimal places
For Example 90.554, and i will format the cell two 2 decimal places. But when i use those cells which are formated to 2 decimal places in a formula it produces results based of all the decimal places in that cell.
Is there a way for the formula to only calculate the results based off of what is shown in the cell? . This is messing up our accounting area by alot of dollars since we sell many items.
I included an example of what i mean in this excel file which should clear up any confusion.
View 2 Replies
View Related
Apr 5, 2014
I need to calculate the subscription amount based on the capacity of the wind mill.
The slab rates are as follows:
1. Up to 0.6 MW - Rs.3,000
2. more than 0.6 up to 3MW-Rs. 7,000
3. 3 MW up to 98MW -Rs. 7,000 for the first three MW and Rs.1,500 per MW in excess of 3 MW
4. Above 98 MW -Rs. 1,50,000
I had tried IF statement but was not successful.
View 1 Replies
View Related
Jun 18, 2009
I need to present dollar values to my boss for a proposed tiered bonus structure and am trying to figure out how to create the necessary formula.
The bonus structure is as follows:
Level 1$8,500 = 3% of fees up to $8,500
Level 2$10,500 = 5% of fees between $8,501 and $10,500
Level 3$15,000 = 7% of fees between $10,500 and $15,000
Tricky part is bonus amounts do not go back to dollar zero. The percentages are only for the stated range.
EX: Level 1 = $255, Level 2 = 99.95, Level 3 = 314.93
Therefore collecting $15,000 would net $669.88
View 3 Replies
View Related
Jan 21, 2008
I m working with a workbook containing a work sheet for every day of the months productivity. Each work sheet contains the employees name and ID number in first two columns with 18 additional rows of data. I've created a master sheet which keeps track of the entire month production but I would like to be able to count the employess from each of the 28-31 sheets within the workbook.
On a given day depending on the day of the week, I may have 8 on staff or 27. Names and ID number are always in the same columns from a different reporting software which generates excel reports.
View 9 Replies
View Related
Jul 10, 2014
I have a model that makes projections based on annual growth rates. However, I need to evaluate the data based on monthly intervals. With an 8% return on an investment of $1,000,000 my ending balance at the end of the year should be $1,080,000 and in year 2 it would be $1,166,400 and so on. In order to evaluate the monthly data I need each month in year 1 to be based off $1,000,000, so it would be 8%/12=.006666% or $6,666.66 per month. The next year would be based off $1,000,000 + (6,666.66*12) = $1,080,000 and each month would be $7,200.
writing a formula to evaluate over 360 periods.
View 5 Replies
View Related
Apr 3, 2008
I have two columns of data. Column B is age column C is gender. I want to calculate average male age and average female age. Suggestions?
Sample
B C
57 f
53 f
47 m
40 f
42 m
Average female age is ___
Average male age is ___
View 9 Replies
View Related
