Formula For Calculating Win/loss Streaks....
I have data from a tems current win/loss record. I want to calculate what the longest win streak is and the longest losing streak as well. What formula do I use?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Counting Streaks In Data
I'm sure it's been asked before but I can't figure out what words to use to find it in the search. Is there a formula that can count number of occurences of a streak. For example... I have data of the Washington Redskins kicker, kicking field goals over 40 yards for the past 10 years in Row 2. It is much longer then this, but this is a short example. G...M...G...G...G...M...M...G...G...M...G...G...G...M G= Good M= Miss I'd like to count the number of times the kicker has made 3 field goals in a row. So, the first G...G...G would be 1 and so on. Any body know how to do this?
View Replies!
View Related
Calculating A Formula
i need to creat a 12 month calculation of accounts month 1 month 2 based on a precentage so i need to calulate the cell 1 pluse a precentage equals cell 2 and then cell 1 plus 2 and the precentage equils that
View Replies!
View Related
Formula For Calculating End Date
I was trying to figure out how you could caluculate a project end date, based on a start date (dd,mmm,yyyy) and a project duration of lets say 5 work day and the work week is five days long (monday to friday). If i have a leave on the week days like tuesday is bank holiday then it should give be the date excluding that tuesday and wednesday leave and caluculate 5 working days and shoule give the project end date. Example date: 5Oct09 Project end date:09Oct09 If i have bank leave on 06Oct09 means. My project end date:12Oct09 Note:Bank holidays are fixed date example(every year bank holiday 06oct09,02Dec09,25Dec09) Please dont need a macro.
View Replies!
View Related
Formula Not Updating/Calculating
How do I return the data from a single cell in a named range. If I type =INDIRECT("B12") into cell "B10". It will return data once, but as cell "B12" changes the value in "B10" does not update. The "B12" cell is part of a named range called "Datainfo".
View Replies!
View Related
Totalling A Win Loss Column
I have two columns for a Home and an Away winloss records for a team over a few seasons. The winloss stats in each column are in the form 2413 (2two digit numbers with a hyphen in between). How can I get a total at the bottom of the column for all the winlosses?
View Replies!
View Related
Inventory And Profit/Loss
I have a workbook that contains several worksheets. The 2 important sheets are Imports and Sales. In reading the MS KB, the help screens and this forum, I found a way to create a pivot table from 2 sheets using Data>Pivot Table>Multiple Consolidation Ranges. While that PT will provide a basic inventory and profit picture, it only allows 4 selects. When these products are imported there are several SKU's. What gums up the works is there are several vendors for the same SKU's. Each vendor has a different price. The selling price is constant so the profit made on each particular item varies. I would like to be able to keep track of inventory by vendor as well as profit by vendor. Using the Consolidation PT does not seem to work. I also tried making one sheet to hold all the data. i.e. data showing imports and sales. However, each entry is driven by the date. So while item A was received on 1/1/07, it was not sold until 1/2/07. The necessary columns for imports are filled in while the columns for sales remain blank until it is their row entry indicating a particular sale. At which point all the columns regarding Imports are blank. I swear, looking at the data sheet with all those blanks (actually0's) is like looking at a mouth full of broken teeth. The resulting PT is also not attractive. I can not imagine I am the first person to have this problem. How do others handle figuring inventory and profits when you have two sheets. Are Pivot Tables the wrong solution? What would be better?
View Replies!
View Related
Calculate The Profit And Loss
I have a trading system spreadsheet that I am at a loss as to how to calculate the profit and loss. I was unable to attach a sample of the spreadsheet. I hope this explanation works. If not, I could email it. Where: Market position is either 1 or 1 Entry price is the price where the position is reversed. For example: H3=1 and I3=100.56, then H4=1 and I4=100.88, then H5=1 and I5=100.53 I need to calculate the profit as follows: I4  I3 (because market position of 1 expects the price to go up) and then, I4  I5 (because market position of 1 expects the price to go down) I want to calculate this as a running total to the bottom of the column. This is the formula I was working with, but it doesn't work: =IF(OR($J4="",ISERROR(MATCH($J4,$J4:$J$55,0)),),"",$J4*(OFFSET($K4,MATCH ($J4,$J4:$J$55,0)1,)$K4))
View Replies!
View Related
Quarterly Interest Calculating Formula
I use Excel 2007 , I created Interest calculator , on Daily basis , to caluculate interest , compounded quarterly. But I want to make it compact , as d one I created is long enough. A3 = Principal Amount B3 = Date of Investment C3 = Interest as on Date D3 = Number of Days , amount Invested {comes out of formula set} E3 = Rate of Interest Now in F3 I want the Interest amount , compunded quarterly. Some times NUMBER OF INVESTED CAN BE LESS THAN 90 DAYS TOO...then what ?
View Replies!
View Related
Simple Subtraction Formula Not Calculating
I've got a list of marks (col T), and I want to create a new column subtracting 5 from each value. Obviously, the formula in this new column is =T15 =T25 etc... But I enter that formula and excel doesn't do any calculating, it just displays "T15" It even seems to recognize that it's a formula, highlighting T1 in the formula and the T1 cell itself, but no resulting value... I've tried changing the format of the cells (in both columns) to Number and back to General again, but without success. I've also tried copying and pasting (values only) the whole T column to another column, and tried there, also without any success.
View Replies!
View Related
Calculating Formula Not Working Properly
I am trying to work out an excel workbook for calculating family recipes. I have gotten most of it figured out but am having a problem with one thing. When the serving size of an item is 8 oz & the recipe calls for 14.5 oz the only way I can get it to work properly is to enter 1.75 in the serving size. But I would like (actually need) to be able to enter the 14.5 and have it calculate the calories correctly  since sometimes the amount to be added to the recipe may be 15 ounces and the serving size is 8 ounces etc....
View Replies!
View Related
Formula For Calculating No Of Days Worked
I need to create a formula that calculates the number of days a person has worked within a quarter, if they have also left the organisation within the same quarter. For example, I have someone who left on 26/08/08. I need to establish how many working days this person actually worked within the quarter (01/07/08  30/09/08). I need to do this for a large number of staff, so would appreciate it if anybody can let me know whether there is a formula that would calculate this.
View Replies!
View Related
Formula For Calculating Capitalized Expenses
I have the following information: Capitalized Expenses TOTAL, Date of Implementation, and Life Cycle as 36months. What I am trying to do is write a formula that knows to calculate the total amortized expense per year based on the implementation date and the life cycle. What's tricky is that not all implementation dates begin in 2010...so the formula has to know where to begin placing the amortized/depreciated expenses...see below: ABCDEFG1Implementation Date: 5/1/112Life Cycle in months:363Total CAPEX: 3,500 45Lump Sum201020112012201320146Capitalized Expenses 3,500 I am looking for formulas to inset in rows C6:G:6...
View Replies!
View Related
Pivot: Gain/loss Over Time
I have a excel 2000 spreadsheet that has beginning and ending dates for certain services. I'm trying to tease out what our customer base has been doing over time in aggregate i.e. how many customers subscribing to X in a given month/year. The only way I've been able to do this in the past has been generating pivot charts of start dates (with 'start mo' and 'start year' busted out) as a positive count (+1), stop dates (with 'stop mo' and 'stop year' busted out) with a negative count (1), then copying these tables into yet another page where I mash them together to get the 'flow' of our customer base over time and generating charts from there. Needless to say, it seems a whole lot more clunky than it should be, and I'm having a hard time searching the innarwebs for similar examples (even though I'm sure it's been done before). Example:
View Replies!
View Related
Formatting Dates To Wonloss Values
I currently copy/paste sports data from the web into MS Excel. One of the columns pasted contains "WonLoss", which appears as '10' on the web. However, once the data is pasted, Excel identifies this field as a date and changes the value of '10' to 'Jan'00. I would like to insert a new column and convert the field back to text (i.e. Jan00 to 10). I have attempted several formulas, however no luck as of yet.
View Replies!
View Related
Formula For Calculating Largest Square To Fit In A Quadrilateral
I know this ain't going to be easy if at all doable, but would anyone know if there can be a formula/s made to calculate the largest size "square" shape (e.g. all 4 corners at 90 deg.) that would fit in to a known size Quadrilateral? Value A,B,C,D and Z are all known values as are the diagonal lengths between corners I failed dismally in Geometry at High school, but how was I to know that 35 yrs later I would need to know this stuff!! Please see diagram for an idea of what I'm trying to achieve.
View Replies!
View Related
Formula: Calculating Averages To Reach A Goal
Below is my data point for each month. The goal I need to hit is 99%. So I need to figure out what minimum monthly percentage I need for the rest of the year, I will need to reach a goal of 99%, and if I can't reach it, return an error. Lastly, i want to be able next month to go in and fill in the AUG percentage with an absolute number (i.e. 89%) and then I would like the rest of the percentages to automatically update by figuring out the new minimum monthly average given the new value for August. I thought that I might be able to do that if there is a function that says "If cell is a number, leave it alone, if it's a formula, then include that cell in the calculation of the minimum monthly average. Jan 89% Feb 88% Mar 83% Apr 89% May 90% Jun 86% Jul 82% Aug Sep Oct Nov Dec Goal 99%
View Replies!
View Related
Formula Sheet For Calculating Prices For My Work
I wanted to do a sheet for calculating prices for my work. I want to have 2 cells where in one I would type in manufacturer price and in other one I would get my price after markup. I have a list of price ranges that I don't know how to make a formula for. For example: If price is $0$74.99 than add $50 If price is $75124.99 than add $75 If price is $125174.99 than add $100 and so on til $7000
View Replies!
View Related
Formula For Calculating 360 Rowsx180 Columns Data Set
I have a set of data 361 rowsx 180 columns. I need to calculate a volume using these depth measurements and the one row of latitude. The formula i need to use is along the lines of =111120*cos(A1)*B1. Then for the next calculation A and B need to be the next cell down, so =111120*cos(A2)*B2. This continues all the way to the bottom and then the equation starts back at A1 for the cos() value and B changes to column C, so it will be =111120*cos(A2)*C2, then you go down the columns until you reach A180. this goes for 360 colums.
View Replies!
View Related
Friction Loss Calculator For Fire Hose
I am attempting to create a friction loss calculator for fire hose, I am using a known formula that calculates the loss based on volume and diameter for pipe. the difference that I have with fire hose in lieu of pipe is that the hose diameter changes with the pressure drop. I have 3 variables that i input, pressure, beginning diameter and length. however as the water flows through the line the pressure changes in turn the diameter changes, I would like to set up my spreadsheet so that the initial variables inputed yield the correct diameter, and then reference back to the initial equation and recalculate based on the yeilded diameter, and recalculates, I can determine the friction loss at 1 foot, in turn determine the diameter at 2 foot, but I wish the spreadsheet to work the calculation over the entire length.
View Replies!
View Related
Prevent Loss Of Copied Data On Clipboard
Using a macro I want to paste from the clip board. I can manually do this by opening the clipboard, selecting the data, which pastes to my specified location. The macro copies to the clipboard okay, but loses the data that I want to paste because just before pasting I have to clear data which was previously been filtered  I have to do this at this point. The macro bogs down. If I end the macro there, I can manually open the clip board and choose the data I copied from there and paste it that way. I'd like the macro to do this for me.
View Replies!
View Related
Running Total Of Weight Loss & Percentage
I was wanting to put it on a spreadsheet. Probably a pretty easy deal, but I don't know the formula to use. this is what I want: Joes start weight 200 minus week ones weight, then the original 200 minus week 2's weight and so on till week 8. I want to keep a running total of weight lost in pounds as well as percentage.
View Replies!
View Related
Rank A List Of Teams Based On Their Win/loss Record
I want to rank a list of teams based on their win/loss record. The list is something as follows. Team A03 Team B02 Team C20 Team D12 Team E21 Team F21 Team G02 Team H30 Team I11 Team J11 Team K21 The column with the actual score contains the formula, =INDEX('2009'!$C$3:$BL$3,MATCH($A4,'2009'!$B$1:$BJ$1,0)). I would like to rank the list to something like, Team H301 Team C202 Team E213 Team F213 Team K213 Team I116 Team J116 Team D127 Team B029 Team G0210 Team A0311
View Replies!
View Related
Shortening The Formula I Used For Calculating Number Of Public Holidays That Passed
I am using this formula to calculate a set of PH(Public Holidays) that have passed from a period of 1/4/2009 to 31/3/2010. =IF(TODAY()>='Team Schedule'!D5,1.5,0)+IF(TODAY()>='Team Schedule'!D6,1.5,0)+IF(TODAY()>='Team Schedule'!D7,1.5,0)+IF(TODAY()>='Team Schedule'!D8,1.5,0)+IF(TODAY()>='Team Schedule'!D9,1.5,0)+IF(TODAY()>='Team Schedule'!D10,1.5,0)+IF(TODAY()>='Team Schedule'!D11,1.5,0)+IF(TODAY()>='Team Schedule'!D12,1.5,0)+IF(TODAY()>='Team Schedule'!H2,1.5,0)+IF(TODAY()>='Team Schedule'!H3,1.5,0)+IF(TODAY()>='Team Schedule'!H4,1.5,0)4.5 From what u can see here, its a massive formula.... but I am pretty sure that there is a shorter way to key in the formula *Note* 'Team Schedule'!D5D12 and 'Team Schedule'!H2H4 are PH. disregard the 4.5 that is in the formula as it is used for other purposes. Every PH that has passed will credit a 1.5 into the cell.
View Replies!
View Related
Calculating TAT
I have some activities that needs to be done ina specified time Like for example TAT HRShrsACT 14ACT 28ACT 324ACT 42ACT 54 And i have a sheet where in i need to calculate turn around time ActivityStart timeEnd timeTATACT 1ACT 2ACT 3ACT 4ACT 5 Based on time entered in start time and end time the TAT column should calculate the TAT based on the above standards. I also would liuke to exclude weekends(Saturday, sunday). Also the TAT should only be calculated during working hrs between 7:30 PM to 4:30 AM.
View Replies!
View Related
Calculating % GP Anyone
im running a new bar in Leeds and im trying to create a spreadsheet which will tell me how much to charge per drink to gain a 80% GP on products, ive got as far as listing the products and prices and calculating how much every measure (25ml) will cost. In the past ive used formulas where I can input the price i charge and it will calculate the GP (=SUM(D10/E10)*100100) for example but id like to be able to type in the GP I want and for it to tell me the price i need, im not sure if tis possible but it will be a massive time saver.
View Replies!
View Related
Calculating Months
I input a month as a start date, and then want the next x number of cells to increment by one month based on the start date. I thought this would be easy using the preset DATE function but I can't get the thing to do what I want.
View Replies!
View Related
Calculating Using Time
Firstly, i'd like cell G5 to show the amount of time worked i.e diff between E6 & F6. Secondly I'd like help with the formula for cells AF6 & AG6 which would require AB6AC6 divided by the frames, every time I try it I get a messed up answer.
View Replies!
View Related
Calculating Cells ..
I have the workbook set to Auto Calc the Cells, which seems to be a bad idea, because with the amount of formula in the workbook, it slows the whole thing down and makes it impossible to do anything within it. Is there a way that I can speed this process up, using some code when I run certain sections? There is a copy of what I am doing, so you can see the amount of formula.
View Replies!
View Related
Calculating Cost Per Second
I'm trying to make a worksheet where I can calculate the cost of a mobile postpaid subscription. It is charged per minute and the cost differs depending on which of the 2 available networks the customer is calling to. The first 20 minutes are free, not depending on network. Edit: Charges to network A is 1,79, per minute after the first 20 minutes are spent. Charges to network B is 2,29, per minute after the first 20 minutes are spent. To sum up: 1. The customer makes a call. 2. If there there are available free minutes, these should be spent first. 3. The customer is charged per minute, depending on network called.
View Replies!
View Related
Calculating Median
I have a list of hotel IDs in one column of my spreadsheet. In the next few columns, I have ratings that customers gave the hotel for cleanliness, location, room, etc. What I need to do is calculate the median of all the ratings for each specific hotel, in a separate column. An example of what I have: ...
View Replies!
View Related
Calculating Time In
=INDEX({"$5.00","$3.00","You Were Late"},MATCH(L17,{7:00am,7:05am,7:06am},1)) i have this formula in excel...this formula sanrv1f posted to help with another question (values changed) but i thought i would work with what i wanted but i get a (Value) error so what im trying to do is base on the system time is if the person typed in if they were early 6:45am to 7am they get Extra $5 on top of the $5 they get for being on time. 6:55am gets $5 7:00am they get $5.00 for being on time if they type 7:01am to 7:05am they get $3.00 if they type 7:06 to 7:10am would return You Were Late after 7:10am would return No Pay so Ex D4 *answers in E4 * 7am would get $5 7:03am am would get $3 7:07am would get You Were Late 7:11am Would get No Pay if they were on time 5 times (ie 5days in a row) they would get a bonus of $20 i tried factoring this in could not do it :/
View Replies!
View Related
Calculating A Median Age
I have a list of ages 1065 and then different number of participants associated with each age, ie: 10  5,071, 11  6,069, 12  8,465, etc. to age 65. I am try to calculate the median age of all participants.
View Replies!
View Related
Calculating The Ballots That Come In
I am on an auto show board and want to come up with a way of calculating the ballots that come in, using excel. Ballot points are accrued as follows: 5 points for 1st place, 3 points for 2nd place, 1 point for 3rd place. With about 25 different categories (classes) and I am estimating participants at around 100.where I can in the automotive field knowing there is nothing besides personal gain.
View Replies!
View Related
Calculating The Overtime
We are guaranteed (right now) a 10 hour day. (we are on 4  10 hour shifts). So if we work 9 hours say on Monday, we get 9 hours of straight time and an hour of short work week (approx 80% of pay). Now if we work 11 hours on Tuesday (which they can do unfortunately) I get 11 hours of straight time and no overtime. We have to make up for the short work week hour. So a less complicated explanation would be if I dont work more than 40 hours per week, no overtime no matter what I actually worked per day. Seems pretty simple but what I want and need to do is to calculate it per day. Mon 10 Hours Tues 11 Hours Wed 9.5 Hours Thurs 10 hours I should get: Mon 0 overtime Tues .5 overtime Wed 0 overtime Thurs 0 overtime I got it to the point where If the day where we get short work week is first and overtime after that, it works. But if we work overtime first then get short work week later in the week, it wont calculate it. I know why it wont work now but I dont know how to make it work. LOL Here's a link to the file.
View Replies!
View Related
Calculating Days ..
What I would like to do is insert a formula into column E that would calculate the number of days from when the count is at 50 to when the next count is i.e. for part 6689841 the time between when the count was 50 and when it was at 20, so the difference between 21/01/2008 and 08/08/2008 in the above example. It does not matter what the count number is (it could be any number except 50), I'm only concerned with every time the count is at 50 how long it is until the next count and to show the result in days.
View Replies!
View Related
Copying But Not Calculating
I'm copying a formula and pasting it to a group of cells. It copies correctly, but I DO NOT want it to calculate anything until other input cells are filled in the columns for the rows that I have pasted the formula. I would like it to show "nothing" until other data is inputted.
View Replies!
View Related
Cells Not Calculating?
I am having a problem with some of the cells in a spreadsheet not calculating. For example, in column A, all the cells have formulas that pull data from another sheet. When I enter the formula, only the formula shows (not the result). I can't figure out what is wrong as the formulas in column B work fine (the results are showing, but not using the same data that column A is). I am thinking it may have something to do with the data being used to calculate the formulas but not sure. How can I resolve this?
View Replies!
View Related
Calculating Rise In CPI
In order to compute and forecast an estimate of the triennial rent adjustment, i need to compute the rise in the cpi (table) from the lease date (cell c3) and the present date (cell a2) or the closest publishe date existing in the table. The % rise is easy to calculate present index / opening index 1 = % rise in cpi. I update the index monthly when onthe bls publish it.
View Replies!
View Related
Calculating Rsquared
I'd like to calculate the rsqaured of the S&P 500 performance in an election year to see if that has any effect on the market's performance. The data I have is below. The election years start in 1928 and are every 4 greater than that. How would I set it up to use the rsq forumla? S&P 500 Index 192612% 192737% 192844% 19298% 193025% 193143% 19328% 193354% 19341%
View Replies!
View Related
