Calculating team winning-losing streak

Hey lastdroidkiller and welcome to the forum,

I worked awhile on that problem and think my suggestion was rejected early on. Some people want their data to stay the same without really using the power of Excel.

In your example data here are the problems.

1. You have Away teams and Home teams instead of just a column of Teams.
This listing of your data makes is very hard for Excel to deal with.
2. You have not calculated wins, losses or draws (yet)
You've asked a question about win streaks and loss streaks but don't have that data yet.
3. Excel needs data in a table where each column needs a title.
Your column C has no title and is completely blank. This keeps your data from being a table. Excel works best with tables of data.

It will take me about 20 minutes to format your data so Excel can deal with it and give you my answer. I'll edit this answer with my workbook in a bit.

WorldCup Soccer Table Format.xlsx

I'm sorry that my data doesn't look exactly what you started with, but Excel needs tables of data to do its work correctly.

You can sort the table in my attached by match number or date or team. If you copy the Streak column and past it back on itself using values only (remove the formulas) it can be sorted also.

They call me an Excel Guru and this is how I'd do this problem. If you have other questions based on my format of the data, please keep asking.

Note - it took me about a half hour to reformat your data to make it work with Excel. Take a look and study the data format. I think you will be surprised with how much more you can do with the data in this format.

Hey lastdroidkiller,

After reading you question a second time, you asked for a teams "current" win/loss streak. My Pivot Table show the Maximum Win and Loss streaks. Let me know if you read this and need to know how to sort the data to find this number. That was the question that Ziggy asked and I thought your question was the same.

Hey Lastdroidkiller,

I have some well spent time working on your problem. It would be nice to let me know you actually looked at my answer.

I watched a great TED talk yesterday and feel like I'm a giver. When you don't let me help and only take, it diminishes my effort. You might like it at:
https://www.bing.com/videos/search?q...3929&FORM=VIRE

Great! Let me know if you have any questions..
Sorry - I'm off to a baseball game and won't see your reply until later this evening.

I hope you can see how I redid your tables so Excel can deal with it better. (5pm west coast time)

Hey lastdroidkiller,

I'm back and looking for you to tell me if my answer was appropriate for solving your problem. I have spent a lot of time, trying to help you. Perhaps you found a better answer on one of those other forums, that you had to search for??

Really sad that you haven't said if my work and answer were what you wanted.

There is another thread that has been running recently that is similar - I don't know if it will help you at all: https://www.excelforum.com/excel-for...-interval.html

OK, well it's a monster!

I have had to change your table into a proper Excel table so that the ranges in the formula will expand as you add rows. I have also had to add two helper columns. Hope it's what you're after.

The formula, which references the two helper columns, is here (array formula confirmed with CTRL+SHIFT+ENTER):

Please Login or Register  to view this content.

There was one small error - I have just updated the workbook. You'll have to download this new one for it to work properly as you add rows.

Once I finalize some layouts for different sports/teams, I'll be posting them for others to use.

It is incredible, yes, and my main reason for being here is helping people - did you notice how much reputation I've received over the years?

Funny, but when I opened your file, Ziggy's fingerprints were all over it ...

Originally Posted by lastdroidkiller

Waiting to see if the OP there is interested in my workbook. If so, I'll be working on that also.

Once I finalize some layouts for different sports/teams, I'll be posting them for others to use.

Well, I do hope that you will not take credit for all the work that went into it, Isaac.

By the way, make sure you have the second version I posted - there was a small error in the first.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

I take no credit for it at all.

I think that crediting those who contributed would be appropriate, yes, and that is indeed what others do with something complex like this. The problem is, you won't be able to explain how it all works, so providing links in the file back to where you got the solution will help others, should they wish to find out. Use the URL of this thread and my user name for this part of your workbook, and credit others in a similar way for the other bits, although they could now be simplified with the helper columns I added for you.

As I said, please mark the thread as solved, and don't forget to thank those who helped you, for they have all spent a considerable amount of their own time on your behalf.

Hey Isaac,

I guess I didn't understand the "Streak" as being the last game played. In other questions it was always the longest win, loss or tie streak. Sorry I didn't help with your problem. Good luck with it.

BTW - I did see that monster formula that Ali gave you. I'm not smart enough to produce something that long. Although, I do believe formatting the data into a better Excel Table where you have a single column for Team Name and another with a Home or Away in it will help with your calculations. You will need to post each game played on two rows using this method but Excel can handle it much easier.

I created it in three parts:

1. the section that works out what the result of the latest game for that team was;
2. the section that works out the last game before 1 above when the result was different;
3. the section that counts the streak between 2 and 1 above.

You're welcome. I'll look forward to the show and tell.

I will have to modify your formula to suit another situation though.
The formula works well for Wins-Losses-Draws
But in the case of the NHL (National Hockey League), one must account for Overtime Wins/Losses and Shootout Wins/Losses

Well, that's going to be for you to work out. I have given you the fundamentals: you will learn a lot by unpicking my formula into its three components and working it out from there.

Of course, had you not thrown a hissy fit when you first asked about this, and not had to try to pose as someone else with a similar requirement, you might have got the answer you wanted before now. You are very lucky that I and others here do not bear grudges.

Good luck with your endeavours - do let us know how you get on.

Hey Isaac,

When I get the time, I'm going to see what Ali did by using the Evaluate Dialog that is built into excel. If you haven't seen it, look at:
http://www.myonlinetraininghub.com/e...e-formula-tool

Originally Posted by AliGW

Well, that's going to be for you to work out. I have given you the fundamentals: you will learn a lot by unpicking my formula into its three components and working it out from there.

Of course, had you not thrown a hissy fit when you first asked about this, and not had to try to pose as someone else with a similar requirement, you might have got the answer you wanted before now. You are very lucky that I and others here do not bear grudges.

Good luck with your endeavours - do let us know how you get on.

Wow...

Unbelievable. Even after a personal apology there is obviously some serious hard feelings and resentment, otherwise dead dogs would be left lie as they are. Apparently grudges are held.

I'm afraid I have to rescind my previous offer to provide completed workbooks.

The spreadsheets are listed in both threads (this one and the other referred to in the first post) as are the calculations/formulas. Do with them what you want.

As mentioned, I did apologize, I do appreciate the help. What more can I do? Obviously the past will continue to haunt me...

Hi, here's an alternative, based on your first workbook and original requirements (no need for helper columns, and not quite so long ) It might be easier to learn from, and to expand for your new requirements. (Still needs to be array-entered)

Formula:

Please Login or Register  to view this content.

Even after a personal apology ...

Well, I must have missed something - where was that?

My apologies if I have missed something.

Originally Posted by ziggy1971

www.mrexcel.com

Never mind...........

I have just seen it. It arrived in my inbox over there late last night, and I just hadn't seen it until now. Apology accepted.

Originally Posted by lastdroidkiller

Not going down that route again. It's not who I normally am. However, I don't need to be reminded of it over and over.
I'm done with that...

That's good to know. The matter is now at an end, as far as I am concerned. However, I do think it would be good for you to try to unpick one of the formulae you've been offered and try to adapt it to your needs - that's how I've learnt over the years. You can always come back for help if you get stuck, either as Ziggy or Droid, or even Isaac, if you fancy - none of you has been banished from here.

You're going to need to say which values are incorrect and what they should be and exactly what you mean by "both items are thrown off".

Originally Posted by lastdroidkiller

I thought I did...

No, that wasn't a full and clear explanation. If those values are incorrect, what do you expect them to be and why?

Surely you can see the numbers are no longer accurate

Unfortunately, as you are unwilling to give specific numbers (i.e. delete x number of games and the standing in L10 should be this and the streak column should be that), it's not possible for me to work out where things might be going wrong. I know it's obvious to you because you understand the NHL, but I'm coming at this simply from a formula point of view. In addition, the formula you are using is not the one I gave you (it's one that someone else came up with), so I'd have to back engineer that before I could even start. I'm out to work shortly, so I'm afraid I wouldn't have the time for this today, anyway. Maybe somebody else will.

Clearly my sickness has made me completely lack the essentials of the forum. I guess being terminal has the tendency to affect a person that way, but nobody cares about that. All they care about is their green stars for their reputation, getting patted on the back or gloating about their accomplishments.

The problem with an online forum is that people can only judge you on your behaviour and responses here: they have no idea whether they are dealing with a truculent teenager or a sick man. I am very sorry to read of your illness, but I am afraid I resent your attitude. You broke the forum rules, kicked off about it when it was pointed out to you, then apologised (after a fashion) which was accepted and I told you that the matter was at an end, yet still you keep on grumbling. I spent hours of my own time coming up with a solution for you before, as did others, yet you continue to criticise those who offer help and advice. My patience is at an end, I am afraid.

I didn't give an exact number because it doesn't matter how many you remove, the effect is still the same.

I deleted the scores of the last 70 games, as an example.

*comments deleted by admin*

Please keep comments and posts to the question at have, and leave out all personal remarks, they are not needed here and will be deleted.

Thanks

Originally Posted by Hockeyfan1985

I know this one was solved last year, I just wanted to ask why the two formulas will not go in effect till the last game of the season.

Given the noise level in this thread, and it's a year old, I recommend you start a new clean thread with your question and attach a file as appropriate.

I agree. In fact, I am now going to close this thread