Automatically Calculate The Bonus Points Into A Cell
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.
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.
Something has X views, 10,000 for this example. I want:
1) The first 100 views to be worth 3 points each (=300 points) 2) The second 100 views to be worth 2 points each (=200 points) 3) The third 100 views to be worth 1 point each (=100 points) 4) Any view therafter to be worth 0.75 of a point (in this case, 9,700 views times 0.75 = 7,275) points.
How would I create a formula like that (without having to actually divide up the views into four groups and do the separate calculations?)
I am trying to calculate the duration between two times on different days.
For example - I want to know the number of days/hours/minutes/seconds between 25th Feb 2009 20:00 & 27th Feb 2009 22:00.
I am sure that the key is how I enter the data into the cells and how those cells are formatted, I just cannot get Excel to see that the times are on different days. At the moment my data is in a raw format with times in one column and dates in another.
Is there a way in Excel/VBA to calculate the width of a text string (in points or inches) with a specific font/font size/font attributes? Right now I have to use a static character count which assumes worst- case character widths for the font (Arial 8 point) but leaves unused whitespace to the right when the text string uses narrow characters.
There is a column in my spreadsheet which needs to reflect a " percent done". I used to enter just a numerical value in (eg. 20) to reflect 20% done. My boss wanted it to show as an actual percentage (eg. 20%). I changed the format to Percent, Zero decimal places. Then you had to enter .20 to get the 20% to show. Now my boss doesn't like that - and wants to be able to enter "20" to get the 20% to show. I put the following code in my SelectionChange event...
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'other logic If Target.Column = Asc(PercentDoneColumn) - 64 Then If Target.Value <> "" Then Target.Value = Target.Value / 100 End If End If 'end other logic End Sub
My problem is that this works well when putting a new percentage in, but not for when you click on a cell that already has a percentage in it. For example, if you have .2 (displaying "20%" in the cell), and click on that cell, it divides that number by 100, and gives you 0.2%, displaying as "0%". I want to enter a number between 0 and 100, have it displayed and stored as a percent. At this point, I'd settle for the displayed, but would prefer to have it stored as a percent, too. I also want it to not reset my value unless I'm putting a new value in!
I’ve got a soccer spreadsheet with the names of six national teams. Near it, the small table that present current condition of a team. Under it, I created the small table where I want them to be sorted automatically by gathered points. The spreadsheet. For example:...............
I want the name of the team, who gathered maximum points, to be shown in first row. In the second row, must be shown the team which has the second result. There must be one formula that I can copy down. So I wrote the next formula =MATCH(LARGE(A1:A9;ROWS($A$1:A1));A1:A9;0)
Everything was perfect until I saw that if the column has the same numbers (points) it returns the first matched row number. So I did the next
I'm creating an (English) football predictions competition for me and my family.
One problem that has stumped me is how to get the scores based on the 'home' & 'away' score predictions.
The rules are: If I predict the correct exact result I get 3 points. I want to add another 'rule' whereby if I predict the correct winner, I get 1 point. Incorrect predictions get 0 points. I don't know how to do this using a formula.
I have a spreadsheet that I am trying to automatically determine a dollar figure based on the number of points a cell has. A if the first set of a goal, B is their actual goal and C is the actual number of points. I am trying to determine the dollar figure for D. For each point between A and B the person gets .50cents. For every point above B and up to C the person gets 1.50. In the case below if the person doesn't reach B than they would only get the 50 cents from A up to C.
A= 2537, B=3252 C=2820
I have attached my spreadsheet and everything is perfect except on one point. I can't figure out how to tell the spreadsheet if it is a negative number to give me zero.
I have some rather complex formulas that when a value is placed in one of any three cells it solves for the other two, taking values from other places on a worksheet (including the cell itself).
Scenario: Cell F10, G10, and H10 all have formulas in them. If a value is placed in F10 it solves for G10 and H10. If a value is place in G10 it solves for F10 and H10. If a value is placed in H10, it solves for F10 and G10.
I really do not think the actual formulas matters at this time...
Has anyone done this before, using VBA code to place the formulas in cells F10 G10 and H10? Look for a working example of this if possible.
I am trying to use the worksheet calculate event to automatically change the color of a cell only when that particular cell changes. In E2 of the worksheet is a formula use to determine rating based on the result of 2 other cells. The rating is classified as follows
Low Moderate High Maximum
I would like to generate a different set of color to the cell and fonts for each of the rating. For example,
"Cyan" to the cell E1 and E2 with Black font if the result is "Low" "Plum" to the cell E1 and E2 with "Black font if the result is "Moderate" "Blue" to the cell E1 and E2 with "White" font if the result is "High" and "Red" to the cell E1 and E2 with "White" font if the result is "Maximum"
Engineer name, attended site(date), report received(date), commercial/heating and bonus(gbp)
Engineers will only get a bonus if they send the reports in 1 day after attended site date for commercial, and 2 days after for heating. If the report is received past this window, they do not get a bonus!
Weekends do not count as a day!
This should be on one row per engineer. I then want to see how much "John Doe" is owed in any given month...
I am a bit of a novice with excel. I have created my own sales tracker where I get two forms of bonus.
Sheet 1 I have with all my sales. Based on the amount of sales I do I get a set bonus for each amount. Sheet 2 I have for all the sales that progress.
They also are on a value basis- for every sale I get a certain amount of bonus. I have 2 cells calculating the amount of points. I was wondering if there was a way to have the cells calculate from the bonus table what i would get without me adding it up manually.
Sheet1 is booked leads.H3 calculates the total amount of points. Sheet 2 is the paid occurences. F2 of that sheet is total points. Sheet 3 is the bonus structure.
I am looking to put all the information in sheet 1:
Booked Bonus Occurred Bonus Total Bonus
Bonus structure is as follows:
Booked Payout Table Occurred Payout Table Net Points Total Bonus
I have salespeople who I want to pay a bonus to but I've also offered them a bonus on how much the salesmen they referred to the company sell and this goes like a pyramid down 3 levels.
so I have a list of salesmen next to there name is how much they have sold then who introduced them. from this I need to make a list of each salesman and below all the people he introduced and the people they introduced with the sales in the cell next to them.
To show how it could look I've done an example above of the information I need for tom, as you can see all the data is in the first 3 columns and tom needs to show that he introduced bob & Sue, but because Bob & Sue also introduced someone, tom needs to show he was involved in this as well and jo, terry, mark need to show, preferably in a different column as the bonus for them is lower.
code needed to be able to automatically calculate the percentage needed from one year to the next. I simply need it to automatically create a third column each time the copy old data button is pressed, and I need the column to have the percent increase ((current year - past year)/current year). Hopefully you can understand what I am aiming for. I have the current code below, and I will attach a compressed and stripped version of my workbook.
Hi to all. I am trying to calculate a list of person's age automatically using functions. Is that possible? I am using Microsoft Excel 2007. I try searcing the web and they say DATEDIF.... But i just can't find the function in Excel 2007!
I am creating a sales per day/week worksheet for my reps to keep track of their sales. I have created the sheet that they only have to enter in what they have sold and it automatically calculates what they have sold per hour in the day, and what that makes their weekly total.
The problem is sometimes they will work 5 days, sometimes 4, sometimes 6 or 7.
im trying to fix the weekly average formula to divide by 7.5 for 1 day worked, 15 for 2, 22.5 for 3, etc all through 7...
this is the formula I have now, obviously it doesnt work
Is there a way in which I can get excel not to run any formulas until I run some sort of command or click a button in order to tell it to?
For example, I have two sheets, one sheet has raw data entered manually into each cell, the other sheet has a large amount of formulas to calculate totals from this raw data. But every time a number is entered into a cell in the raw data sheet Excel says 'Calculating Cells', I want to be able to enter all my raw data, then go to my totals sheet and execute all my formulas.
I am trying to create a spreadsheet that will automatically calculate a date in the future.
The future date calculated needs to take the time NOW (as the user completes a field) and determines if this is before MIDNIGHT on the following SUNDAY. If so it calculates the date as a Tuesday 2 weeks on. For example... I request a date now on 1st Jan which is a Thursday. This is before midnight Sunday and so the date calculated would be Tuesday 20th January.
If the time NOW is after MIDNIGHT Sunday and before the next TUESDAY.. ie 0001hrs on the Monday and 2359hrs on the Monday then the date returned should be Tuesday 3 weeks on.. e.g 27th.
It would also need a function that would allow the total number of referrals on a Tuesday to 20 at which point it would overspill and provide a date on the Friday of the same week.. so in the first example it would be Friday 23rd
Is this something that can be achieved with formulas and a set of dates for it to calculate from or do I need to find someone who can do it in VBA for me.
I have a simple script that puts the save time in the last row of column C when the spreadsheet is saved. Another script puts a username in Column A and column B contains the time opened. I would like to add to the save script to make column D od the active row have a calculation similar to:
I am working on a depreciation schedule in which I want the monthly depreciation of an asset to automatically calculate and, if the asset is fully depreciation, caclulate a zero or the balance to be depreciation (if less than the monthly depreciation). Please see below example. As you can see my asset is fully depreciated at the end of February but because there remains a $0.01, the formula is calculating another month in March and then reversing it in April (less the $0.01). Here's the formula I'm using. What am I doing wrong?
Column H is March, Column C is my monthly depreciation, and column E is my beginning book value:
I have a Worksheet_Change event like the one below. When Checkbox1 is ticked the it subtracts and when it's not, it adds:
Private Sub Worksheet_Change(ByVal Target As Range) Dim intValue As Integer If Not Intersect(Target, Range("W12:X24")) Is Nothing Then intValue = CInt(Target.Value) If CheckBox1.Value Then intValue = intValue * -1 Select Case Target ' Apples Case Is = Range("W12") Logbook.Show Range("I10").Value = CInt(Range("I10").Value) + intValue Case Is = Range("X12")........................
I have a column of about 15,000 numbers that I need to update. They are all 6 digits long and I need to insert characters at 3 different points. The numbers currently look like
I am creating a workbook with multiple worksheets, one overview sheet and then one sheet for each month of the year.
In the monthly sheets I have a Category column for which I have created a drop-down menu of expense categories. Then I have an Expense Amount column where I enter the amount spent.
The Category column will not necessarily remain sorted by category because I will be entering the expenses as they come up throughout the month so the categories will be all mixed up, unless I manually sort them.
What I want to do is somehow automatically calculate a running subtotal by Category (that will update with each new entry) and simultaneously automatically transfer the running subtotal for each Category to a specific cell on the overview sheet.
I have a number of time data elements of different recordings that i would like to be able to use one formula in one cell and have it give me to total time. For example:
00:03 00:55 00:47 00:05
All in one cell (i.e., not having each data point in a cell), I would like to have the formula tell me that these four recording times equal 00:01:48 (i.e., 1min 48 sec).
