LinkBack URL
About LinkBacks
I am attaching two workbooks that one of the forums experts helped me put up sometime back. I need help to understand how the constants -2 and -4 are arrived at in the two workbooks.
Thanks in advance.
Team Form.xlsx
TeamForm2.xlsx
I am attaching two workbooks that one of the forums experts helped me put up sometime back. I need help to understand how the constants -2 and -4 are arrived at in the two workbooks.
Thanks in advance.
Team Form.xlsx
TeamForm2.xlsx
Last edited by AliGW; 04-30-2017 at 03:44 AM.
OnditiGK
There's a lot of stuff going on here. Would you care to tell us where to look???
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Thank you for your reply Glen.
In the 1st workbook (Team form), check columns DI to DM, rows 5 to 24 for -2.
In the 2nd workbook (Team form2), check columns X to AB, rows 8 to 27 for -4.
A very good question! Open BEFORE and check the results for Burnley. They're wrong. Now open AFTER and check the results for Burnley. they're correct.
Last edited by Glenn Kennedy; 04-29-2017 at 03:56 PM.
=IFERROR(INDEX(ROW($D$4:$D$75),LARGE(IF((($CT5=$F$4:$F$75)+($CT5=$I$4:$I$75)),ROW($D$4:$D$75)-ROW($D$4)+1,""),$AD$29-COLUMNS($D:D)+1)),"")
in "Team Form"
The highlighted part in the above formula is the "standard" way of returning the row number when using INDEX with SMALL/LARGE.
Given the above formula , the 5th game for Arsenal is row 32 BUT it is 29 in Column D of the table so get get the result of 29 the above we need to subtract 3 so the formula becomes
=IFERROR(INDEX(ROW($D$4:$D$75),LARGE(IF((($CT5=$F$4:$F$75)+($CT5=$I$4:$I$75)),ROW($D$4:$D$75)-ROW($D$4)+1-3,""),$AD$29-COLUMNS($D:D)+1)),"")
which results in
=IFERROR(INDEX(ROW($D$4:$D$75),LARGE(IF((($CT5=$F$4:$F$75)+($CT5=$I$4:$I$75)),ROW($D$4:$D$75)-ROW($D$4)-2
,""),$AD$29-COLUMNS($D:D)+1)),"")
The 29 is used as an index for the various INDEX matches used in Columns DC onward.
The value of -4 in the other file is there because the table starts 2 rows further down but the principle is the same.
I got lost there Glen. What exactly do you mean?
The attachments dropped off. Try now.
INDEX works perfectly efficiently with whole-column references, so the to-ing amd fro-ing of adding and suntracting rows to make things start at 1 is a waste of time.
Last edited by Glenn Kennedy; 04-29-2017 at 04:00 PM.
Glen, thank you for your input. John,thank you so much I now understand how the constants are derived. I am attaching another workbook (See below) in which I need to return the last 5 match results (Win,Draw, Loss) for when a team is playing at home and away. Expected results for man city are above the home and away tables. The interval is to be tied to cell L2 so that it can be changed without altering the formula.
Home_Away Form.xlsx
Last edited by gko_87; 04-30-2017 at 03:33 AM.
In L6, entered as an array and copied across/down:
=INDEX($G:$G,LARGE(IF($C$6:$C$334=$K6,ROW($C$6:$C$334)),$L$2-(COLUMNS($L:L)-1)))
and in R6, similarly treated:
=INDEX($H:$H,LARGE(IF($F$6:$F$334=$K6,ROW($F$6:$F$334)),$L$2-(COLUMNS($R:R)-1)))
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.
You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
Thank you for your quick response Glen. Is it possible to to calculate overrall team form in the workbook below without using helper columns?
Team Form.xlsx
All you need to do is embed the formulae from the helper columns into the form formulae at the three places where the cells are referenced, and then confirm as array formulae (+CSE), so DC5 goes from this:
=IF(INDEX($F$4:$F$3000,DI5)=$CT5,INDEX($J$4:$J$3000,DI5),INDEX($K$4:$K$3000,DI5))
to this:
=IF(INDEX($F$4:$F$3000,IFERROR(INDEX(ROW($D$4:$D$75),LARGE(IF((($CT5=$F$4:$F$75)+($CT5=$I$4:$I$75)),ROW($D$4:$D$75)-ROW($D$4)-2,""),$AD$29-COLUMNS($D:D)+1)),""))=$CT5,INDEX($J$4:$J$3000,IFERROR(INDEX(ROW($D$4:$D$75),LARGE(IF((($CT5=$F$4:$F$75)+($CT5=$I$4:$I$75)),ROW($D$4:$D$75)-ROW($D$4)-2,""),$AD$29-COLUMNS($D:D)+1)),"")),INDEX($K$4:$K$3000,IFERROR(INDEX(ROW($D$4:$D$75),LARGE(IF((($CT5=$F$4:$F$75)+($CT5=$I$4:$I$75)),ROW($D$4:$D$75)-ROW($D$4)-2,""),$AD$29-COLUMNS($D:D)+1)),"")))
and so on.
Last edited by AliGW; 04-30-2017 at 04:38 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Would you be kind enough to do that in the workbook AliGW,if its not too much trouble?
You really ought to try yourself first, otherwise you will always be reliant on others, but see the attached.
I just tried it and it worked really great. Dragged across and downwards. I will now compare my workbook with yours. Thanks.
You're welcome.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Just out of interest why would you NOT want to use helper columns? If it's merely a question of avoiding clutter then just hide them.Originally Posted by Onditi
Helper columns are extremely useful and efficient, particularly where various elements of a compound formula repeat the same calculation.
They're also much easier to read and debug. Once you add functions together in a single cell compound formula then it's all too easy to lose track of what it's doing and it becomes very hard to quickly read and understand.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
I am attaching another workbook with a drop down in cell G2. Expected results are just above Team form Table.
Required:
Is there a way to have an all-in-one table that is dependent on the drop-down in cell G2 such that when:
1. When G2=Home the results corresponding to Home above the table displayed.
2. When G2=Away the results corresponding to Away above the table are displayed.
3. When G2=Home/Away, the results corresponding to Home/Away above the table are displayed.
The interval is still 5 to be tied to cell C2.
All-in-one.xlsx
Thanks in advance.
Would you clarify what results you expect to see and where. I assume you want to see the table in L8:P27 to be populated with stuff and that what you show in L2:P4 is just an example for Man City.
In addition explain how you get the series of 5 W/D/L characters from the original data.
@Richard,
What is required for each team is the last 5 results for home games, last 5 results for away games and last 5 results for home/away i.e last 5 games whether home or away.
The results in the sample table are earliest to latest.
Previous posts on this subject should supply )or have the basis of) the answers to this: it is just one of many variations of presentation of results previously requested.
Hi John,
That's precisely what I'm trying to reconcile.
The sample results say that the earliest (i.e. starting at the top of the table) 5 Home results for Man City are D W D D W, yet the first 5 listed are W W W , and then undated D D
Obviously I'm clearly missing something.
Filter on Machester City Home) and you will find LAST 5 results are in rows 281, 247 ,276 288 and 316 which give the results in the Man City sample table,
Formula for HOME results:
=INDEX($G$8:$G$1000,LARGE(IF($C$8:$C$1000=$K8,ROW($A$8:$A$1000)-ROW($A$8)+1,""),6-COLUMNS($L:L)))
Formula for AWAY results
=INDEX($H$8:$H$1000,LARGE(IF($F$8:$F$1000=$K8,ROW($A$8:$A$1000)-ROW($A$8)+1,""),6-COLUMNS($L:L)))
Both array entered and copied across
FORMULA for last 5 Home/Away is the one given by Ali in an earlier post, modified to reflect the appropriate ranges.
Need to be combined with IF statement (or something!) to reflect drop down choice.
John,thank you for your help. You have explained very well.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Formula for home/Way (based on Ali's previous response)
=IF(INDEX($C$8:$C$3000,IFERROR(INDEX(ROW($A$8:$A$3000),LARGE(IF((($K8=$C$8:$C$3000)+($K8=$F$8:$F$3000)),ROW($A$8:$A$3000)-ROW($A$8)-6,""),$C$2-COLUMNS($A:A)+1)),""))=$K8,INDEX($G$8:$G$3000,IFERROR(INDEX(ROW($A$8:$A$3000),LARGE(IF((($K8=$C$8:$C$3000)+($K8=$F$8:$F$3000)),ROW($A$8:$A$3000)-ROW($A$8)-6,""),$C$2-COLUMNS($A:A)+1)),"")),INDEX($H$8:$H$3000,IFERROR(INDEX(ROW($A$8:$A$3000),LARGE(IF((($K8=$C$8:$C$3000)+($K8=$F$8:$F$3000)),ROW($A$8:$A$3000)-ROW($A$8)-6,""),$C$2-COLUMNS($A:A)+1)),"")))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
and change others ...
INDEX($G$8:$G$1000,LARGE(IF($C$8:$C$1000=$K8,ROW($A$8:$A$1000)-ROW($A$8)+1,""),$C$2-COLUMNS($L:L)+1))
Last edited by JohnTopley; 04-30-2017 at 11:02 AM.
How can the three formulas be combined in one cell in L8 so that when G2=HOME, we have the result in L2, when G2=AWAY, we have the result in L3 and when G2=HOME/AWAY, we have the result in L4?
In L8:
=IF($G$2="HOME",INDEX($G$8:$G$1000,LARGE(IF($C$8:$C$1000=$K8,ROW($A$8:$A$1000)-ROW($A$8)+1,""),6-COLUMNS($L:L))),IF($G$2="AWAY",INDEX($H$8:$H$1000,LARGE(IF($C$8:$C$1000=$K8,ROW($A$8:$A$1000)-ROW($A$8)+1,""),6-COLUMNS($L:L))),IF(INDEX($C$8:$C$3000,IFERROR(INDEX(ROW($A$8:$A$3000),LARGE(IF((($K8=$C$8:$C$3000)+($K8=$F$8:$F$3000)),ROW($A$8:$A$3000)-ROW($A$8)-6,""),$C$2-COLUMNS($A:A)+1)),""))=$K8,INDEX($G$8:$G$3000,IFERROR(INDEX(ROW($A$8:$A$3000),LARGE(IF((($K8=$C$8:$C$3000)+($K8=$F$8:$F$3000)),ROW($A$8:$A$3000)-ROW($A$8)-6,""),$C$2-COLUMNS($A:A)+1)),"")),INDEX($H$8:$H$3000,IFERROR(INDEX(ROW($A$8:$A$3000),LARGE(IF((($K8=$C$8:$C$3000)+($K8=$F$8:$F$3000)),ROW($A$8:$A$3000)-ROW($A$8)-6,""),$C$2-COLUMNS($A:A)+1)),"")))))
confirmed with C+S+E.
Are you really incapable of trying to nest these formulae yourself???
Last edited by AliGW; 05-01-2017 at 01:18 AM. Reason: Amended to fix reference to G2.
When Pasted into L8 and dragged across and downwards, the formula picks only the results for home/away for all teams. Nothing changes when the drop down options in G2 are changed.
It works perfectly here. I'm away from my computer now and can't attach the file, but the principle is simple: it's two nested IF statements. Did you enter it with CSE?
I did.I only get results for Home/Away only. But when I change G2 to either home or away, the results for Home/Away do not change.
Well, it works fine here, as I've said. I'll post the file here tomorrow morning - can't access it now, sorry.
Will wait to check your file. Thanks. Its 12 midnight in East Africa. Good night.
Here is my file Ali. Changing drop down menu does not update the table.
All-in-one.xlsx
Attached as promised and working perfectly.
This is great Ali! Thank you so much! God Bless.
You're welcome! Please do now mark the thread as solved.
Ali, I had earlier marked my last thread as solved but after going through the results, your last attachment, I realized that I had provided wrong "Expected Results" when G2=AWAY (I provided D-L-D-D-L instead of W-W-D-L-W). This resulted to wrong results being populated for all teams when G2=AWAY. Kindly assist me in adjusting this.
Also, help me understand how you arrived at the constant "6" as bolded below: When should the constant be positive and when should it be negative?
=IF($G$2="HOME",INDEX($G$8:$G$1000,LARGE(IF($C$8:$C$1000=$K8,ROW($A$8:$A$1000)-ROW($A$8)+1,""),6-COLUMNS($L:L))),IF($G$2="AWAY",INDEX($H$8:$H$1000,LARGE(IF($C$8:$C$1000=$K8,ROW($A$8:$A$1000)-ROW($A$8)+1,""),6-COLUMNS($L:L))),IF(INDEX($C$8:$C$3000,IFERROR(INDEX(ROW($A$8:$A$3000),LARGE(IF((($K8=$C$8:$C$3000)+($K8=$F$8:$F$3000)),ROW($A$8:$A$3000)-ROW($A$8)-6,""),$C$2-COLUMNS($A:A)+1)),""))=$K8,INDEX($G$8:$G$3000,IFERROR(INDEX(ROW($A$8:$A$3000),LARGE(IF((($K8=$C$8:$C$3000)+($K8=$F$8:$F$3000)),ROW($A$8:$A$3000)-ROW($A$8)-6,""),$C$2-COLUMNS($A:A)+1)),"")),INDEX($H$8:$H$3000,IFERROR(INDEX(ROW($A$8:$A$3000),LARGE(IF((($K8=$C$8:$C$3000)+($K8=$F$8:$F$3000)),ROW($A$8:$A$3000)-ROW($A$8)-6,""),$C$2-COLUMNS($A:A)+1)),"")))))
Please refer to your last attachment.
Copy of Team Form without Helper Columns #2 AliGW.xlsx
=IF($G$2="HOME",INDEX($G$8:$G$1000,LARGE(IF($C$8:$C$1000=$K8,ROW($A$8:$A$1000)-ROW($A$8)+1,""),6-COLUMNS($L:L))),IF($G$2="AWAY",INDEX($H$8:$H$1000,LARGE(IF($F$8:$F$1000=$K8,ROW($A$8:$A$1000)-ROW($A$8)+1,""),6-COLUMNS($L:L))),IF(INDEX($C$8:$C$3000,IFERROR(INDEX(ROW($A$8:$A$3000),LARGE(IF((($K8=$C$8:$C$3000)+($K8=$F$8:$F$3000)),ROW($A$8:$A$3000)-ROW($A$8)-6,""),$C$2-COLUMNS($A:A)+1)),""))=$K8,INDEX($G$8:$G$3000,IFERROR(INDEX(ROW($A$8:$A$3000),LARGE(IF((($K8=$C$8:$C$3000)+($K8=$F$8:$F$3000)),ROW($A$8:$A$3000)-ROW($A$8)-6,""),$C$2-COLUMNS($A:A)+1)),"")),INDEX($H$8:$H$3000,IFERROR(INDEX(ROW($A$8:$A$3000),LARGE(IF((($K8=$C$8:$C$3000)+($K8=$F$8:$F$3000)),ROW($A$8:$A$3000)-ROW($A$8)-6,""),$C$2-COLUMNS($A:A)+1)),"")))))
We need to look at column F ("Away") not C ("Home")
Checking formulae in the "sample" table would have identified the error (possibly my typo originally): as previously commented, you must try and understand the formulae provided rather than constantly depend on the forum to assist with every problem.
Last edited by JohnTopley; 05-03-2017 at 04:35 AM.
Interesting Home/Away record for a set duration. Will add it to my workbook, if that's OK.
Thanks John! You are allowed lastdroidkiller,we are all here to learn.
This thread is already marked as solved but I do not know what I am missing. I have spent the past to hours trying to look at this. Results are correct when N2=Home and when N2=Away, but they are wrong when N2=Overall. Expected results are above the table.
Template.xlsx
If you continue to change the rows where the data starts you will continue to have problems: Change the 6 in the formula to 4.
Please do NOT change the layout again.
Just did and this is what I am getting:
Template.xlsx
Try
=IF($N$2="HOME",INDEX($H$6:$H$5000,LARGE(IF($D$6:$D$5000=$M6,ROW($B$6:$B$5000)-ROW($B$6)+1,""),6-COLUMNS($V:V))),IF($N$2="AWAY",INDEX($I$6:$I$5000,LARGE(IF($G$6:$G$5000=$M6,ROW($B$6:$B$5000)-ROW($B$6)+1,""),6-COLUMNS($V:V))),IF(INDEX($D$6:$D$5000,IFERROR(INDEX(ROW($B$6:$B$5000),LARGE(IF((($M6=$D$6:$D$5000)+($M6=$G$6:$G$5000)),ROW($B$6:$B$5000)-ROW($B$6)-4,""),$K$2-COLUMNS($B:B)+1)),""))=$M6,INDEX($H$6:$H$5000,IFERROR(INDEX(ROW($B$6:$B$5000),LARGE(IF((($M6=$D$6:$D$5000)+($M6=$G$6:$G$5000)),ROW($B$6:$B$5000)-ROW($B$6)-4,""),$K$2-COLUMNS($B:B)+1)),"")),INDEX($I$6:$I$5000,IFERROR(INDEX(ROW($B$6:$B$5000),LARGE(IF((($M6=$D$6:$D$5000)+($M6=$G$6:$G$5000)),ROW($B$6:$B$5000)-ROW($B$6)-4,""),$K$2-COLUMNS($B:B)+1)),"")))))
Thanks John! That worked.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks