This may sound complicated so bear with me!
I am looking for a formula that counts the number of times a trend occurs in a row.
Here is an example
Home Away Result Trend England 2 3 Germany LOSS 2 Netherlands 2 1 England LOSS 2 Spain 2 2 England DRAW 1 England 2 0 Russia WIN 2 England 3 1 Belgium WIN 2 Italy 4 2 England LOSS 1
In the trend column is where I want the formula to be. So if a team goes on run of 2 Losses, then 2 would be beside both matches, the same Wins and Draws.
Thanks
Last edited by Hypernova; 12-16-2013 at 02:25 AM.
This seems overly complicated but it works.
Data Range
Enter this array formula** in F2:
=COUNTIF(E2:INDEX(E2:E8,MATCH(TRUE,E2:E8<>E2,0)),E2)
Enter this array formula** in F3 and copy down as needed:
=IF(E3=E2,F2,COUNTIF(E3:INDEX(E3:E$8,MATCH(TRUE,E3:E$8<>E3,0)),E3))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Note how each formula references E8 as the end of the range. This is intentional.
Last edited by Tony Valko; 12-12-2013 at 05:28 PM.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Amazing Work !
Can I ask why E8 is intentional?
We have to find the last (bottom-most) cell where the current trend ends. For example:
Win
Win
Loss
For the 1st instance of Win the trend ends at Loss. However, if all the entries are the same:
Win
Win
Win
[empty cell E8]
The trend doesn't end until the empty cell that is outside of the data range.
How about extending to the formula to:
WIN or DRAW in Column G
LOSS or DRAW in Column H.
I don't understand what you're asking?
I would also like to have a trend for WIN/DRAW only and also LOSS/DRAW only:
Home Away Result Win/Draw Trend Loss/Draw Trend England 2 3 Germany LOSS 3 Netherlands 2 1 England LOSS 3 Spain 2 2 England DRAW 3 3 England 2 0 Russia WIN 3 England 3 1 Belgium WIN 3 Italy 4 2 England LOSS 1
Sorry about taking so long to get back to you. I've been really busy the last couple of days.
Ok, try this...
Data Range
This array formula** entered in F2:
=IF(AND(N(F1),E2<>"Loss"),F1,IF(E2<>"loss",COUNTIF(E2:INDEX(E2:E$7,MIN(IF(E2:E$7="loss",ROW(E2:E$7)-ROW(E2)))),"<>loss"),""))
This array formula** entered in G2:
=IF(AND(N(G1),E2<>"Win"),G1,IF(E2<>"Win",COUNTIF(E2:INDEX(E2:E$7,MIN(IF(E2:E$7="Win",ROW(E2:E$7)-ROW(E2)))),"<>Win"),""))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Select F2:G2 and copy down as needed.
Last edited by Tony Valko; 12-18-2013 at 02:14 PM.
I still have a few more formula's to go through, if you have any more patience to assist me!
Here is a sample table:
HOME/AWAY H A Trend HOME Trend AWAY Trend HOME 1 HOME 2 HOME 0 AWAY 4 HOME 0 AWAY 3 AWAY 3 AWAY 0
What I want is 3 formulas: Trend, HOME Trend and AWAY Trend.
When a team plays at HOME, their score is H, when they play AWAY, their score is A.
Every time their score is 1 or greater, that starts a positive trend, but if their score is 0, it starts a negative trend.
Last edited by Hypernova; 12-19-2013 at 05:13 PM.
Just looking at that data I have no idea what logic is being used to get the results you have posted.
The logic is to see how many consecutive games that a team goes in scoring or not scoring a goal per match in terms in all games, home games and away games.
Can you explain how you arrive at those results?
Trend is the combination both column B and C
When B or C =>1, it starts a positive trend, when B or C=0, it starts a negative trend.
1 2
2 2
0 -1
4 1
0 -1
3 2
3 2
0 -1
HOME Trend is just column B
When B =>1, is starts a positive trend, when B =0, it starts a negative trend.
1 2
2 2
0 -2
0 -2
AWAY Trend is just column C
When C =>1, is starts a positive trend, when C =0, it starts a negative trend.
4 3
3 3
3 3
0 -1
Hope that helps.
Well, I hate to admit it but I'm stumped!
Not sure how you'd do this, especially the Home/Away trends with the blank rows between the results.
If someone else has a solution I'd be interested in seeing it.
Hi,
I understand your description about positive and negative trends, though I can't see where you've got the actual values from, i.e. the Home Trend of 2 in the first two rows, or all the Away Trends of 3.
Regards
This is where the results are coming from:
Data Range
E2 = count of B2:B3
E3 = count of B2:B3
E4 = -count of B4, B6
E6 = -count of B4, B6
F5 = count of C5, C7:C8
F7 = count of C5, C7:C8
F8 = count of C5, C7:C8
F9 = -count of C9
Good luck!
For Home Trend, IF A="HOME", calculate the consecutive order. So I want to ignore the AWAY values in this condition, if you take the away values, that leaves:Originally Posted by XOR LX
1
2
0
0
Because the value of 1 and 2 are =>1, they create a trend of 2, so 2 is applied to both. Its the same logic for Away Trend.
--------------------------------------------------------------------------------------------------------------
OK, I have modified my data slightly, so that the score is one column rather than 2 like so:
H/A SCORE Trend H/Trend A/Trend HOME HOME HOME AWAY HOME AWAY AWAY AWAY
Does that make it easier?
Last edited by Hypernova; 12-20-2013 at 02:50 PM.
Tony, Hypernova:
Just to say that I really appreciate your lucid explanations.
And also to apologise for wasting your time, since, although I don't often find myself completely bamboozled by a post, and having tried in vain to understand what is required, I can honestly say that I still have absolutely no idea what is being asked for.
Regards
To give a very simple example
We have the following table:
Number Trend 1 2 1 2 2 1 3 2 3 2 1 2 1 2
As we can the see;
1 consecutively occurs 2 times
2 consecutively occurs 1 time
3 consecutively occurs 2 times
1 consecutively occurs 2 times
On that basis, the formula applies the amount of times each instance of a number occurs consecutively.
This array formula is in B2, and copied down:
=IF(A1=A2,B1,MATCH(FALSE,$A2:$A$8=A2,0)-1)
Understand ?
I think I can solve this but the resultant formulas would a mile long.
I'm just suffering from "logic cramps" on how to do this efficiently.
I'll try it again Saturday when I have more time.
I thought you wanted the counts of consecutive numbers >0 and the negative count of consecutive zeros?
Data Range
I do, I am trying to explain it to XOR LX.
Ok, I have a formula for Trend, but not Home Trend and Away Trend, just to let users know.
I am getting tired of this aswell, Trust Me !!!
This is the current situation.
when
(In Column C): A=HOME and B=>1 Positive Trend, B=0 Negative Trend
and vice versa, when
(In Column D): A=AWAY and B=>1 Positive Trend, B=0 Negative Trend
H/A SCORE H/Trend A/Trend HOME HOME HOME AWAY HOME AWAY AWAY AWAY
Thanks
This would be interesting to see the finished product. I was looking in to making something like this for Rugby league but have no idea where to start.
Please share once finished please.
All the Best,
Marshall
Glad to know I am not the only one !
This is the best I could come up with that works 100% of the time.
Assuming this is your data:
Data Range
We have to extract the Home data and the Away data to their own tables.
Data Range
Enter this array formula** in H2 and copy down to H9:
=IFERROR(SIGN(INDEX(C:C,SMALL(IF(A$2:A$9=G$1,ROW(A$2:A$9)),ROWS(H$2:H2)))),"")
Enter this array formula** in I2:
=IF(H2="","",COUNTIF(H2:INDEX(H2:H9,MATCH(TRUE,H2:H9<>H2,0)),H2)*SIGN(H2))
Enter this array formula** in I3 and copy down to I9:
=IF(H3="","",IF(H3=H2,I2,COUNTIF(H3:INDEX(H3:H$9,MATCH(TRUE,H3:H$9<>H3,0)),H3)*SIGN(H3)))
Enter this array formula** in L2 and copy down to L9:
=IFERROR(SIGN(INDEX(C:C,SMALL(IF(A$2:A$9=K$1,ROW(A$2:A$9)),ROWS(L$2:L2)))),"")
Enter this array formula** in M2:
=IF(L2="","",COUNTIF(L2:INDEX(L2:L9,MATCH(TRUE,L2:L9<>L2,0)),L2)*SIGN(L2))
Enter this array formula** in M3 and copy down to M9:
=IF(L3="","",IF(L3=L2,M2,COUNTIF(L3:INDEX(L3:L$9,MATCH(TRUE,L3:L$9<>L3,0)),L3)*SIGN(L3)))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Enter this formula in D2 and copy down to D9:
=IF(A2="home",INDEX(I$2:I$9,COUNTIF(A$2:A2,"home")),"")
Enter this formula in E2 and copy down to E9:
=IF(A2="away",INDEX(M$2:M$9,COUNTIF(A$2:A2,"away")),"")
Here's the file with this implemented:
Trend(A).xlsx
Very Good Tony !!
I'm impressed
You're welcome. Thanks for the feedback!
Hi again,
Do you think that you can do this similarly but with W/D/L?
H/A W/D/L H: W or D H: L or D A: W or D A: L or D H W 2 H D 2 2 A L 2 H L 2 A D 2 2 A W 2 H W 1
I have been blocked from the site the last few days and have just got the issue resolved.
I'll take a look at this tomorrow when I have more time.
Hi,
Just bumping this again, anyone able to solve the problem below I have
Thanks
Even a little help is appreciated
I have to admit that at this point I'm totally lost on this. It's been a while since we last played with this so I've lost all train of thought as to what you're trying to accomplish.
Its in relation to football/soccer, teams play at home and away, what I am trying to achieve is to find out the teams home form and away form. The table above deals with 2 situations. 1) How long a team goes on an unbeaten run [ie WIN and DRAW Combined] at Home and Away 2) How long a team goes on an unwinnable run [ie LOSS and DRAW Combined] at Home and Away.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks