Formula Required To Calculate Points For Football Results Prediction
Aug 2, 2009
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 do a football prediction competition at work and need help or the formula to calculate peoples scores.
The scoring works like this
10 points for a completely correct result and score for both teamse.g. 1-0 prediction and 1-0 result 7 points for a correct result but a correct score for only 1 teame.g. 1-0 prediction and 2-0 result 5 points for a correct result but no correct score for either teame.g. 1-0 prediction and 2-1 result 2 points for an incorrect result but a correct score for 1 teame.g. 1-0 prediction and 1-2 result
I have basic Excel knowledge but have no idea how to create a formula which will calculate the above and populate the correct scores for people in the spreadsheet.
A pal at work runs a football predictions spreadsheet but due to a recent surge in numbers, it is taking longer to adminster each week.
Basically, you get 3pts for a spot on home score, 4pts for a spot on draw, 5pts for a spot on away win and only 1pt if you get a correct result but not the actual scoreline, with no points being scored for being completely wrong.
At the moment, he manually inputs 0,1,3,4 or 5 pts each Monday, of course this is open to human error. Is there a way of getting excel to calculate this for him once he inputs the correct score in the first column?
i am trying to work with a formula that will look at date today (NOW) and compare this to a due date and in return provide me with only the working hours total. Working hours are 8am to 4pm (8 HOURS).If the due date is passed this will be a negative figure.
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%
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.
I have a work sheet which includes a column of numbers representing certain daily events. I am building a user defined function to analyse the trend in the numbers by assisigning values from -2, -1, 0, 1, 2 based on comparison of two days.
Below is the function I built but it is not working, it is resulting in zero values in most conditions. I have attached the sheet which includes the numbers and the function.
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.
Background: I play a game that requires 5 members to play, however you can have more then 5 members on the team. In order for a player to receive points for playing they must have played at least 30% of the total games played. The problem for me is when trying to figure out how many games someone needs to play to get to 30%, the total goes up as they play those games.
What I would like to accomplish: I'm looking for help on a formula that would figure out how many games a player would need to play to get to 30%. I have a column that is total games played, %of games played by player, games played by player, and games needed to play to get to 30%. Basically the user would enter the # of games played by each player, and the total games played overall for the team for that week, the spreadsheet would then show the # of games each player that isn't above 30% would need to play.
I tried to make the question as clear as possible, if anyone needs clarification,
Originally Posted by shg Welcome to Oz, Basca
spreadsheet with a representative sample of your data?
Games Played Player 1 20 Player 2 7 Player 3 13 Player 4 20 Player 5 13 Player 6 13 Player 7 7 Player 8 7
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've written the following code. It calculates the future value of required minimum distributions for traditional IRAs. When I enter the function in excel, it drops the last argument. I've tried changing the order of the arguments, and it always drops the last one. The arguments are : marginalTax (marginal tax rate), r (expected return on investment), t (time), begAge (beginning age), RDT (as range -- required distribution table; looks up the percentage required at each age), IRAValueat70 (calcualtes the value of the IRA at 70 years of age)
Function RMD(r As Double, marginalTax As Double, begAge As Integer, t As Integer, RDT As Range, IRAValueat70 As Long) age = begAge endAge = begAge + t Dim holder As Long Dim RMDnow As Long Dim RMDall As Long hat = 70 If endAge < 69 Then Else If begAge <= 69 Then Do While hat < (endAge) RMDnow = (IRAValue / (Application.VLookup(age, RDT, 2, False))) RMDnow = (1 - marginalTax) * RMDnow...........................
Attached is the zip file with the detail of what I am hoping to achieve which is calculating the number of lengths required from generated infromation.
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 have a range that has formulas that are based on other fcells outside the range.
What I want to do is, if the cell has a value to remove the formula and paste the results. If the filed is blank (no results from the formula) to leave the formula in place.
There could be a marco to run when this process is needed.
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
I am trying to sum the results of a filter via VBA but cannot seem to get it right. I have the code to count the number of "active" rows, now to get an average I have been trying to use similar code to get the sum so I can divide the sum by the number of rows not filtered out. I get the error "Unable to get the Sum property of the WorkSheet function class" This is the code I have butchered:
Set R = ActiveSheet. Range("d1"). CurrentRegion Set R = R.Offset(1, 0).Resize(R.Rows.Count - 1, R.Columns.Count) For i = 1 To R.Rows.Count If Not R.Rows(i).EntireRow.Hidden Then intCountCancelled = WorksheetFunction.Sum("Cancelled") ' cancelled is a named range. This is the line its failing on End If Next i
I have a list of data in Excel with Autofilters. At the bottom of the data set is a row that totals up many of the columns. Whenever I use the Autofilters, the totals at the bottom still reflect the entire set of data, rather than only the filtered data.
Is there a way (using macros, formulas, or something else) to make the totals automatically update to only reflect the data that is visible when using Autofilters?
In the attached sheet, I need to find all items in column D that start with "LAB". For those items, Multiply the corresponding values from column B x column E and return the sum of all to cell T1. Once the total is in cell T1, I need to then delete the values in column E for those items (or change them to zero). I dont even know where to start with this.
I'm wondering if it's possible to write a formala that if a certain condition is met, performs a formula referenced in another cell. For example: Column A contains a list of formulas. In column B is the condition, let's use Work Type as an example. The result (or the formula I need) would be in column C. So if the work type is "Type1", then the formula in the corresponding column C cell would perform the formula in cell A5. If the work type is "Type8", then the formula in the corresponding column C cell would perform the formula in cell A2.
