Calculate Soccer Standings
I have a spreadsheet that tracks the score of youth soccer games. For the team standings, the teams get the following points per game based on final score.....
+6 points for a win
+1 point for a shutout
+x points goal differential no greater than 3 (so a 30 win would be 10 total points, as would a 40 or 50 win)
1 point for goal differential greater than 5 (60 would be 9 pts, 70 would be 8 pts, etc)
+3 points for a tie
+0 for a loss
I've tried CSE, ARRAY, SUMIFS, OR, IF and nothing works.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Compare The Pts Values And Then List The Team Standings Based On Those Points
I have a list of nine teams from B13:B21 with Wins in column C, Losses in Column D, Ties in Column E. Pts are in Column F (all of these run row 13 down to 21). The team listed in B13 has a cell name of One in the Name Box, team in B14 has a cell name of Two, etc up to Nine. The pts total for each team is also named, so F13 is called ptsone and has a current value of 8. F14 is called ptstwo and has a value of 10, etc. I'm trying to do is in a different area of the spreadsheet put a formula that will compare the pts values and then list the team standings based on those points.
View Replies!
View Related
Soccer Statistics Spreadsheet
Back to test you with a few problems as I work through a project I'm carrying out, based on past results data of the English Premier League. I have some of the solutions/formulas I require already, but there are still some gaps to be filled in! *** the spreadsheet is set out as follows: Key Columns / SubTitle (Remarks) B Date (the date a match was played  the s.sheet is sorted by this column, A>Z) C Home Team D Away Team E Home team goals scored F Away team goals scored IM Indicates via U or O whether the match contained under or over 0.5,1.5,2.5,3.5,4.5 goals N Points obtained for Home team (i.e. 3 for a win, 1 for a draw, and 0 for a loss) O Points obtained for Away team (i.e. 3 for a win, 1 for a draw, and 0 for a loss) There are 381 rows in total for each worksheet (season)  1 for the titles, and 2381 for each match played in the season...................
View Replies!
View Related
Soccer System And Dates
Ive got an example below of how i'm sorting fixtures and how to determine winners/losers and draws. ******** ******************** ************************************************************************>Microsoft Excel  Book2___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF1G1H1F2G2H2F3G3H3F4G4H4= ABCDEFGH102/03/2007Team*A**Team*B000202/03/2007Team*C**Team*D000309/03/2007Team*A**Team*D000409/03/2007Team*B**TeamC000Sheet1* [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR. What I'm struggling with is the ability to change dates and still be able to add the teams figures up. When adding, for instance, Team A's wins I'm using =f1+f3 and similar for defeats and draws. But when I change dates, for instance game 3 to the 10th, Team A is still counting F1 and F3 yet its games now in row 1 and row 4. How can i get it to change with the sorting. Im sorting fixtures via the sort feature in data.
View Replies!
View Related
Make A Soccer Game
I would like to make a soccer game in excel, there are two divisions of eight teams, and 77 non league teams. I want to be able to enter the teams and then randomdly do the fixtures and when that is done i would like to be able to enter the scores and on another worksheet have the table.
View Replies!
View Related
Autosort In Excel(Soccer Table)?
Create a macro for a soccer table. image : http://i25.tinypic.com/1tk0na.jpg sort all characteristics (Win,Draw,Lost and and so forth) Formulas: D2 =IF(B9>C9;1)+IF(B11>C11;1)+IF(C13 D3 =IF(C9>B9;1)+IF(C12>B12;1)+IF(C14>B14;1) D4 =IF(C10>B10;1)+IF(C11>B11;1)+IF(B14>C14;1) D5 =IF(B12>C12;1)+IF(B10>C10;1)+IF(C13>B13;1) E2 =IF(B9=C9;1)+IF(B11=C11;1)+IF(C13=B13;1) E3 =IF(C9=B9;1)+IF(C12=B12;1)+IF(C14=B14;1) E4 =IF(C10=B10;1)+IF(C11=B11;1)+IF(B14=C14;1)................
View Replies!
View Related
How To Calculate Combinations ?
I want to see how much combinations are possible when i got 6 numbers..  got this numbers 123456  want to calculate how much combinations of ( 2 ) numbers possible  want to calculate how much of ( 3 ) numbers possible  want to calculate how much of ( 4 ) numbers possible  want to calculate how much of ( 5) numbers possible  never 2 same numbers together (22) or (223) or (2234) may not be in list  How can i make the result visible in kind of list ? I would be nice if somebody knows a good solution..
View Replies!
View Related
Calculate Variances
Just wanted to find out if the formulas in the attached apreadsheet are correct. Formulas from E6 to E10. Also, if you multiply the "new daily target" with "working days" should'nt this give you the "remaining" figure? Currently it's not doing this
View Replies!
View Related
Calculate Totals
I have a spread sheet that is used to review calls placed by a call center. Column A has the extensions of the phones, and Column E has the type of call (Outgoing or Incoming). Each line is a new phone call. We have about 8 extensions, but the worksheet could have a couple thousand calls. So, Column A could have extension 1401 from rows 1100 as extension 1401 made 100 calls. I'm looking for a formula or macro that will summarize how many outgoing and incoming calls extension 1401 had. Thoughts? My initial thought was something like this: =SUMPRODUCT((A2:A5000=H2),(E2:E5000)) In this formula, I would type in the extension of H2 and it would scan Column A and add up the values in Column E. The only problem is, that Column H doesn't contain a numerical value. It only has "Incoming" and "Outgoing" (minus the quotes), so this doesn't work.
View Replies!
View Related
Calculate Ratio
I'm trying to create a quote... basically I need to find out about calculating ratios. We have 47 students going on a trip to different venues, we offer 1:10 teachers (i.e. one teacher free, for every ten students going), however some venues offer a different ratio e.g. 1:5 teachers free of charge. I need to somehow create a column where it will calculate the amount charged for the different ratios the venues offered.
View Replies!
View Related
Calculate The Variance
i have a dynamic list of numbers....currently 10 numbers in the list. how can i calculate the variance? i have the upper limit (=MIN(1,(mean+half width)) i have the lower limit (=MAX(0,meanhalf width) i have the mean (avg of all numbers) i have the t value (TINV(alpha, (n1))) i have the half width (t value * SQRT of Var/N) i just don't know how to get the VAR/N
View Replies!
View Related
Calculate If There Is Anything In The Cell
Im wanting to do a sum calculate if there is anything in the cell. Ive tried using the 'count' and 'count if' but both return values of zero. There are words in the cell, rather than numbers. I want it to return the value of cells with data in them, and exclude those with nothing ("") in them.
View Replies!
View Related
How To Calculate Age
to calculate the age from the format date of birth shown below. SQL Data S1Date of Birth2Jun 9 1947 12:00AM3Jan 1 1957 12:00AM4Jan 1 1958 12:00AM5Jan 1 1956 12:00AM6Jun 4 1951 12:00AM7Dec 10 1963 12:00AM8Jun 17 1958 12:00AM Excel tables to the web >> Excel Jeanie HTML 4
View Replies!
View Related
Calculate Hours
I am designing a simple time card. Column D=time in, Column E=time out. My formula in column F to calculate total hours is =TEXT(E2D2,"[h]hrs"). The result is not correct. Example: In at 9:30 out at 5:00 and the calculated total is 4 hours.
View Replies!
View Related
Calculate The Probabilities
How to calculate probabilities... say that I am buying pop bottles that say "1 in 6 wins" or drawing buttons from hats where 5 of them are numbered 15 and the other one is the winner. (Note that you would have to keep putting the buttons back after drawing so there is always 15 and a winner whenever you draw) The possible combininations are easy to figure out... just do 6 to the power of how ever many bottles you buy or how many times you draw a button. So obviously if you draw once, there is 6^1 possible... if you draw twice there are 6^2 (or 36) possible combinations. To figure out how many winning possibilities (win atleast once) there are it's not too hard at this level... you can just list the possibilities: First  Second 11 12 13 14 15 1W (1 winning combination) 21 22 23 24.................................
View Replies!
View Related
Calculate Overtime
I have a time sheet for my employee's that I need to calculate their overtime in 1.5x and 2x rates. Their overtime totals are done in individule columns from D33 to S33. The first 4 hours per day are charged at 1.5x and anything over that is 2x. I want to show the 1.5x in one box and the 2x in another. I do believe that I need two formulas one in each of the boxes where the final totals would go. Here's an example, in columns D37 to D41 the employee has worked 12, 14, 9, 16 and 14.5 hours. so that's 17 hours @ 1.5x and 8.5 @ 2x.
View Replies!
View Related
Calculate The Value In The NPV
I have two columns in a payment schedule (which adjusts according to certain user inputs) that I need to use in my NPV calculation. The first column is the Total Payment and the second is Inducements. Therefore each value in the NPV calc. needs to be the sum of a given period's payment and inducement (but i don't want/have a separate column which calculates the sums). The number of periods adjusts with the users input of Term. There also may be periods where there is a payment but no inducement.
View Replies!
View Related
Calculate The Percentage
I have attached a file with both a sample section of data on the first sheet and the outcome I would like on the second sheet... I would do it all manually but there are over 200,000 rows in the actual file. The macro needs to calculate the percentage of sale for each reference number within each part number and move down to the next part number and do the same until it reaches the bottom.
View Replies!
View Related
How To Calculate Time
WHen I was a beginner at Excel, I came up with the following formula to calculate the time difference between two "time" values. Since I was too lazy to add a ":" between my hour and minute, I had decided to simply enter the start (column B) and end time (column C) as a military time (e.g., 0100, 1230, 1500, 1930, 2300, 2359). The following formula would be in Column D. =IF(C7
View Replies!
View Related
How To Calculate Date
how to calculate the date for one year. If i have 24Jun08 (A1) i need to calculate 24Jun09. If i do =A1+365 it gives me an error (the little green thing in the corner of the box) is there a way to do this without the error showing up.
View Replies!
View Related
Calculate Total
I need a formula that will calculate the total of the items in column "W" when the row directly below it contains a "1" in column "V". It will need to calculate the totals from that row all the way up to the next row that contains a "1" in column V. Example: In X14 (where I will put the formula) it would total W9 through W14. However the formula still needs to be able to calculate the total if there are more or less cells to sum. So that the same formula could calculate that in X8 it will total W5 through W8.
View Replies!
View Related
Formula To Calculate A %
I use this formula to calculate a %, but naturally when there is a 0 excel returns the #DIV/0! error. Can someone fix my formula so the error gets replaced with a 0 in the formula the 0 would be in cell W2 =IF(N2="yes",BN2/W2,0.5).
View Replies!
View Related
Calculate Text As Value
trying to calculate a text as value in a formula. In cell A5 (please see the excel attached) I have a weighted formula that is supoosed to return a final result based on the drop down from cells B5 to V5. Drop downs give the option of choosing a value. Everything is going fine as long as you select a value (5, 3 or 0). In cells H5, J5, K5, R5, S5, U5 there is also the option of choosing NA. I am trying to get the formula to calculate NA as being equal to 5. Basically, if for the respective parameter someone choses NA, the final result in cell A5 should not be influenced. If all the parameters are 5 and only one has NA (any of the cells H5, J5, K5, R5, S5, U5), the final result (in cell A5) should be 100. The same applies if all parameters are 5 & more than one cell or all the cells that have the NA options (H5, J5, K5, R5, S5, U5) are NA.
View Replies!
View Related
Calculate Overlap
Here is my data Range A1:L8 1234567891011Overlap111122275%22275%22250%22225%2220%2220% I am trying to figure out a formula in L3 which is the calculation of overlap of region1 and region2. The top row is the number of days. What formula can I use to calculate the percent overlap? The numbers in column L show the expected result. (Using XL 07)
View Replies!
View Related
How To Calculate Overtime
I am trying to formulate a formula that will calculate overtime hours worked. Now standard hours are 17:30pm  20:45pm. Anything outside these hours are overtime. If the start time is 18:00pm then the person is still paid from 17:30pm @ standard rate regardless. Now I am trying to work out a formula that will cover hrs outside of the standard hrs AND hrs unworked but paid for. see attached! September tab {blue highlighted cells}
View Replies!
View Related
Calculate Workbook
I'm opening 2 workbooks and I'm on Manual Calculation option. I wanted to calculate only workbook A. The standard button only give me the option of calculating a worksheet or all open workbooks (F9).
View Replies!
View Related
Calculate A Certain Weekday
I need to set up something that "calculates" an orders next delivery date. We deliver on Tuesdays, Wednesdays, and Thursdays for different stores. IE. Store 1  Today's DateWednesday 10/28/09 = Next delivery date is Tuesday 11/3/09. Store 2  Today's DateWednesday 10/28/09 = Next delivery date is Wednesday 11/4/09. Store 3  Today's DateWednesday 10/28/09 = Next delivery date is Thursday 10/29/09.
View Replies!
View Related
If Cell Has Value Calculate
I am setting up a spread sheet to compare an invoice between 2 suppliers. It has formulas throughout and a total column. If no data is entered in cells then total column has "value" in it. How can I eliminate the"value" while still keeping formulas in place. Not all cells will have data in them on every instance. But I still need all formulas to be saved
View Replies!
View Related
Calculate Within Ranges
I have a range of cells and when an amount is entered into a cell I would like it to pick which row to pick from and do the sum from that row. for eg if I was to put $201,000 I want it to pick the row with $200,000 to $540,000 range and use the sum from that row. eg below Taxable ValueRates Percent $0$200,000 $00.0% $200,000$540,000$2000.2% $540,000$900,000$8800.5% $900,000$1,190,000$2,6800.8% $1,190,000$1,620,000$5,0001.2% $1,620,000$2,700,000$10,1601.8% $2,700,000> $29,6003.0% the sum I have for the rows is ((E3A3)*D3+C3) but somehow need to have it choose from ranges and use appropriate sum.
View Replies!
View Related
Calculate Discounts According To Value
i dont know if IF statements can do this BUT if you look @ this image! what it is i need the spreadsheet to automatcally work out the discount for xample "Example Jhon" spent 29.99 and i want the spreadsheet to work out the discount automatcally for 15% i know IF statmenet can do this but i need it to work out what discount theyll get Each price is going to be diffrent for example if someone spend 50 they would get 25% off i need excel to figure out what discount thell get automatcally
View Replies!
View Related
IF To Calculate If One Date Is Greater Than Another
I am having great difficulties getting the following formula to calulate wether the date in cell f2 is greather than of less than the given date. =IF(F2<="18.11.06", "No Cover", "Under Warranty") f2 = 14.09.02 This does produce the wanted result of "No cover" however if the date is 14.09.07 and therefore greater the 18.11.06 i will not get the expected result of "Under Warranty"
View Replies!
View Related
Calculate Redemption Date
attached spreadsheet, columns AE are given data, col B shows the redemption frequency, col C shows the advanced notice period and col E shows the our first investment date. I would like to display the result in one column showing the next redemption date.. e.g. for Fund 1, it's redemption term is quarterly 30 days notice, which means.. next redemption period will be 30/09/2007 (get money back) and we have to give the redmeption notice latest on 31 Aug 2007 becos of 30 days advance notice to exit the fund e.g. for Fund 2, it's monthly 90 days notice, which means if we put in a redemption notice today then 90 days from now on will be 8 november, hence can get money back on 30 November 07. What formula shall I use to display  next available redemption date  exit date (get money back)
View Replies!
View Related
Formula To Calculate Discounts
I have attached an example .... If both of the discounts being offered are positive then I am ok with. In this example the first discount ofered is 25% and the second one is +50%. If the required charge is higher than the standard charge then, in this example, the final charge should give the value as 2,500. What would the formula be that added up the 2 discounts together and gave the correct value in cell B8?
View Replies!
View Related
Calculate Difference In Dates
I need to calculate the amount of time between two dates, and show it in the number of hours and minutes. My dates are formatted to show the time in Zulu time. So 1406 = 2:06PM. Here is what I have: A1 = 6/4/08 1406Z B1 = 6/5/08 0402Z For this example, the formula should return a result of 13 hours and 58 minutes. With the result, there is no need to desingate Zulu time with a 'Z'. But I need the result in a format that will allow me to calculate an average for all my data.
View Replies!
View Related
On Calculate (or Change) Hide
I need an excel macro to run on a change of a cell and unhide the row below that cell based on the results of a formula in that hidden row (happens to be offset). Essentially I need this.  Change is made to cell  Based on results of a cell in the row below, unhide that row I am unsure how to do this, as I am fairly new to the VBA world. I know I need to attach it to the oncalculate or change event, but I am unsure how to get the current cell that is changing and go from there.
View Replies!
View Related
Calculate Time Outside Of Specified Ranges
I am staring at this excel spreadsheet blankly ... I am at a loss as to how to accomplish the detailed analasys but simple math this spreadsheet reequires. The data consists of three columns pasted from a report that is exported as a .csv: B Code C Start Time D End Time The following analyses must be made on each row: 1. determine if the start time falls within the scheduled shift
View Replies!
View Related
Calculate Date Difference
I request a help: A1 = 01Apr2009 B1 = 02Apr2009 C1 = 03Apr2009 D1 = Blank E1 = 05Apr2009 F1 = 06Apr2009 What is the best suitable formula to find "First blank cell", then "date difference" between today and its previous cell's (i.e today minus "C1" in this case).
View Replies!
View Related
Textbox With Characters To Calculate
I have an userform say frmNew. In the form, I have three textboxes: txtCost, txtRate, txtSale. The following two boxes are filled by a user: txtCost contain the Cost of an item (numbers) txtRate contain the % Rate (3 character), i.e. the txtRate can contain only A,B,C,D,E,F,G,H,I and J. The corresponding value is 1,2,....9,0 respsctively. The value for txtSale is generated on clicking the command button i.e. txtSale = (txtCost + (txtCost * txtRate)). Could you please help in writing a macro for calculating txtSale value.
View Replies!
View Related
AUTOMATIC CALCULATE VALUES
I have a list where i enter invoice values lets say on column (A:A) and i want to calculate all the invoice values each time i enter the values on A:A for example; i entered $2500 on cell A1 and pressed enter, then i entered another figure on A1 let say $500 i want the total value to be shown such as $3000
View Replies!
View Related
VBA Calculate Pause
Hi everyone I am having a little trouble with a sheet i'm currently working on. Having exhausted what i believe to be every avenue trying to do this without VBA, i've finally had to sucomb to it as i can't see an alternative and i'm no VB expert by any means! This is what i have so far:
View Replies!
View Related
Disabling Auto Calculate For Others?
I'm working on a workbook that has alot of calculations in it, on a pretty slow PC (Workrelated), so autocalculate is casuing crashing, so what I've done with this sheet is disabled autocalculate and added a macro to calculate each sheet indiviually instead, which avoids the crashing (The last sheet still takes a minute and a half to process but thats ok I guess). My worry is, when this workbook does the rounds, peoples excel will already have autocalculate on, and will crash it themselves. Is there anyway I can ensure that the sheet turns atuocalculate on or, failing that, does anyone have any ideas on how to help the issue?
View Replies!
View Related
