Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


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:
Totalling A Win Loss Column
I have two columns for a Home and an Away win-loss records for a team over a few seasons. The win-loss stats in each column are in the form 24-13 (2-two digit numbers with a hyphen in between). How can I get a total at the bottom of the column for all the win-losses?

View Replies!   View Related
Find In The Win, Loss, Draw Columns When There Is No Entry
I m trying it out I find in the Win, Loss, Draw columns when there is no entry the formula leaves a 1 when there should be a 0 in the Draw column.

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 A0-3
Team B0-2
Team C2-0
Team D1-2
Team E2-1
Team F2-1
Team G0-2
Team H3-0
Team I1-1
Team J1-1
Team K2-1

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 H3-01
Team C2-02
Team E2-13
Team F2-13
Team K2-13
Team I1-16
Team J1-16
Team D1-27
Team B0-29
Team G0-210
Team A0-311

View Replies!   View Related
Calculating The Average Gain & Loss For Numbers In One Column?
I wanted to know how I can calculate the average gain and average loss for data that is inputted into one column? For example;

Column A

Average Gain ??
Average Loss ??

View Replies!   View Related
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= 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
Combinations - Win Or Draw
I'm creating a spreadsheet regarding football teams and want to lis thte combinations of results for each team.

Specifically, if the team will win or draw.

So, I have 6 teams, each team can either win or draw.

I want to display the combinations of team 1 winning AND team 2 winning AND team 3 winning etc.

View Replies!   View Related
Entire Column Losing Formulas (2007, Win XP)
Whenever a particular file is saved, and then reopened it says "Error Data Lost" and the entire column that used to have the formulas now has =#NA.

Its only happening in 1 particular column. Anyone know a specific reason this might happen or is something in my code just jacked up?

View Replies!   View Related
Scoring System. Different Points For Win, Lose Or Tie
Have used excel for a while but never the need to go past SUM or AVG, etc.

Im trying to set up a point scoring sytem for 3 teams in various events. the highest scoring team would get 2 points, 2nd place 1, 3rd place 0. If 2 teams tied for first they both get 2. If they tie for second they both get 1. I spent 30 minutes before I came up with

=IF(AND(B3>B4, B3>B5),2,0)
=IF(OR(B3=B4, B3=B5),1)

Spent another 15 trying to figure out how to put the 2 together. Finally realized a)Im missing a whole bunch of formula, b)There is a lot more to excel than I thought I knew.

B3,B4,B5 are the teams scores. Looking for any help at this point. Even a push in the right direction.

View Replies!   View Related
Calculate The Weighted Average Of The Win Rate Based On Volume Of Calls
I have 3 sets of data for two different groups:

Group 1 - Inbound
- Total volume
- Gross adds
- Win rate (gross adds/total volume)

Group 2 - Outbound
- Total volume
- Gross adds
- Win rate (gross adds/total volume)

I need to calculate the weighted average of the win rate based on volume of calls. Is there any way to do that?

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 Not Calculating When Greater Then Zero
Currently I am using the following formula ....

View Replies!   View Related
Formula- Calculating A Cell
calculating a cell. It has to reference a chart, so it has to validate three answers before it knows what to input.

example CHART ...

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


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
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.

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
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: 5-Oct-09

Project end date:09-Oct-09

If i have bank leave on 06-Oct-09 means.

My project end date:12-Oct-09

Note:Bank holidays are fixed date example(every year bank holiday 06-oct-09,02-Dec-09,25-Dec-09)

Please dont need a macro.

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.


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

But I enter that formula and excel doesn't do any calculating, it just displays "T1-5"
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 Not Calculating Except After Using Text To Columns
In the last column of this workbook, you'll see a cell that says "=D2". Whenever I type a formula into this worksheet, it doesn't calculate.

If I select that cell and do text to columns (making sure to remove any separations if it tries to split it), then the formula calculates.

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
Calculating Text To A Formula (Range)
ON 135
NY 122
PA 117
WV 50
VA 126
NC 25
VA 35
MD 110

My issue: If ON I need to times (x) the miles (column C) by .0285 to equal D If NY I need to times (x) the miles (column C) by .0125 to equal D

View Replies!   View Related
Formula For Calculating Capitalized Expenses
I have the following information: Capitalized Expenses TOTAL, Date of Implementation, and Life Cycle as 36-months.

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 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
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%

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 $75-124.99 than add $75
If price is $125-174.99 than add $100
and so on til $7000

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
Formatting Dates To Won-loss Values
I currently copy/paste sports data from the web into MS Excel. One of the columns pasted contains "Won-Loss", which appears as '1-0' on the web. However, once the data is pasted, Excel identifies this field as a date and changes the value of '1-0' to 'Jan-'00. I would like to insert a new column and convert the field back to text (i.e. Jan-00 to 1-0). I have attempted several formulas, however no luck as of yet.

View Replies!   View Related
Find The Loss/gain Difference Between An Initial Value
I am looking for a simple loss/gain formula for my students. They are trying to find the loss/gain difference between an initial value, and the current value. They are using a monetary value, not date/time.

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
Formula For Calculating Largest Square To Fit In A Quadrilateral
I know this ain't going to be easy if at all do-able, 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 For Calculating A Ratio Using The Data In The Pivot Table.
I canít seem to work out the formula for calculating a ratio using the data in the pivot table.

Iíve added a column next to the pivot table to work out the ratio between to columns.


But what if there is no data in cell (B11), I want to return a ď ď (blank space)Ö but it returns a #DIV/0!

View Replies!   View Related
Averagif/averageifs :: To Compute The Realised Profit Or Loss
How to compute the realised Profit or Loss for each of the shares transacted for the year ended 31 December 2008 based on average cost of each share.

using AVERAGEIF, if appropriate. [Pls refer to the attachment.]

View Replies!   View Related
To Calculate That Loss Or Gain As A Percentage Of Their Original Weight
I run a slimming club and would like to use Excel to keep records of members weights, losses, gains etc. What I would like to do is enter their starting weight into a cell then each week when they are weighed enter a loss or gain in an adjacent cell. I would then like Excel to calculate that loss or gain as a percentage of their original weight. Is this possible?

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
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

'Team Schedule'!D5-D12 and 'Team Schedule'!H2-H4 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
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
VBA To Calculate Profit & Loss Of Each Race Bet
I had a member make the MACRO in the attached spreadsheet some time ago, when i run it on the spreadsheet a runtime mismatch error occurs. I cant find the mismatch.

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)*100-100) 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 AB6-AC6 divided by the frames, every time I try it I get a messed up answer.

View Replies!   View Related
Calculating TAT
I have some activities that needs to be done ina specified time

Like for example


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 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.

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 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
Copyright © 2005-08, All rights reserved