General Betting
There is currently 1 person viewing this thread.
10 Aug 14
14:28
Joined:
Date Joined:
16 Sep 06
Hi, I have a set of football results - W, D, L - is there a formula I can use to give me streaks - eg what's the maximum wins and losses between draws?
Thanks very much
Thanks very much
sort by:
Show
per page
Replies: 4
|
By:
Are you looking for streaks between draws so wins and losses together or just separate winning/losing streaks? You can find the winning/losing streaks using the following array formula
=MAX(FREQUENCY(IF(A1:A100="W",ROW(A1:A100)),IF(A1:A100"W",ROW(A1:A100)))) to enter array formulas you need to paste in the above but rather than just pressing enter you need to hold control and shift then enter. Obviously change the W to L for losing streaks, or even amend it to not equal to D if you want combined W/L streaks |
|
By:
Just noticed the forum strips out characters as usual and missed out the not equal to part. Seems it doesn't like to show the pointy brackets. Hopefully this will show up correctly, just remove the space between the pointy bracket in the IF(A1:A100< >"W",ROW(A1:A100) bit
here's the Win streak =MAX(FREQUENCY(IF(A1:A100="W",ROW(A1:A100)),IF(A1:A100< >"W",ROW(A1:A100)))) losing streak =MAX(FREQUENCY(IF(A1:A100="L",ROW(A1:A100)),IF(A1:A100< >"L",ROW(A1:A100)))) Here's the combined W and L streaks, remember this is just checking the data is not equal to D so it will count blank spaces so you'd want to set your range limits A1:A100 to cover just the data range =MAX(FREQUENCY(IF(A1:A100< >"D",ROW(A1:A100)),IF(A1:A100="D",ROW(A1:A100)))) |
|
By:
Thank you. I'm trying to find the longest streak without a draw.
that formula doesn't seem to quite work for me, but thank you for taking the time - you've helped my understanding and I'm slowly getting there! |
|
By:
When you say it doesn't work what do you mean? Is it giving an error? incorrect results? If it gives a #value error that mean you haven't entered it a an "array formula" by pressing and holding control and shift then pressing enter after putting the formula in the cell. If it's entered correctly it should be giving you some number even if it's only 100 or 0
This would be the formula, but you need to take out the space between the greater and less than signs < > , if I don't put a space the forum strips it out. You also need the adjust the A1:A100 parts to cover where ever your W,D,L data is on the spreadsheet i.e. D20:D80 whatever range it's in. =MAX(FREQUENCY(IF(A1:A100< >"D",ROW(A1:A100)),IF(A1:A100="D",ROW(A1:A100)))) Here's a link about arrays formulas they're very using in excel for trawling thru data if you want to avoid using Visual Basic http://office.microsoft.com/en-gb/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx |