I would like to use a formula, maybe index match, that would get me the team's opponent for the week. I wouldn't know where the team plays, so knowing which column to look up wouldn't be known. I've tried to use index match with an or in between, but I get #N/A. I can't use an IF statement because I don't know what to compare. Please help.
Your layout does not lend itself to analysis - can it be changed?
What do you have in mind for the analysis anyway? I was thinking that you would have a drop-down in a second sheet by which you could select a team, and below that a list of week numbers, and the opponents and location (home or away) would be populated. It would need to cater for Byes as well.
Hope this helps.
Pete
That's a good idea, but this sheet is for the year 2017, and I would like to use this as a template for the other years from 2002 to 2019, so a second sheet would not be feasible.
Okay, you tell me how you envisage this looking, and try to do a mock-up and attach that.
Pete
Most of what I have took a lot of thought, I think. There's already 53 columns and over a hundred rows. What did you have in mind?? I was looking for a formula for the opponents because then all I'd have to enter is the schedules on the top half. The other formulas already work. Like I already said before, I was thinking of using it as a template for the other years.
What do you want to see and where do you want to see it? I cannot follow what you want from a sheet that contains some data, but no indication of your expected results. Please amend your sheet.
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
I'm going to chip in here and agree with Pete: the layout is not at all conducive to analysis NOR to creating a template that you can carry forward easily.
That's not a good enough reason to stick with what you've got ...Most of what I have took a lot of thought, I think.
... and this is a very good reason why you should change it!!!There's already 53 columns and over a hundred rows.
You have made the rookie mistake of getting confused between what should be a report - output (tables of team-specific data and analysis) and what should be source data - input (a normalised data format with one line of data for each 'transaction'). Please don't take this criticism personally: most of us, me included, have been there, done that and got the T-shirt. Once you get the distinction between the two, you will make better progress.
My advice: two worksheets per year, one for the INPUT (normalised) data and one for the OUTPUT (team stats and analyses).
INPUT comes first, then OUTPUT (using INPUT as its source).
As you have Excel 2016, you may be able to use PowerQuery (Get & Transform) for some if not all of the output sheet.
Last edited by AliGW; 08-27-2019 at 03:56 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.
Does anyone here have any ideas on how my information should look?? I have no clue. I just wanted help with formulas.
See post #6 if you don't want to change, but still need help with formulae.
What I have right now is that I have to type the Opponents manually in the OPP columns. What I would like to do is to find a way to get those OPPS from each week's schedule so I would have to type the schedules in the spreadsheet in the top portion of my sheet. Is there a way I could do that please? For the weekly scores I used a couple of index/match formulae within an IF statement and it worked out.
To be able to do that, there needs to be a source table: where is the source list? How would Excel know which opponent to allocate and when? Without parameters, we can't help you build an automated system.
You still haven't shown us what you want to achieve - sorry.
I have had a play with this. You won't recognise it.
Instead of having 17 (maybe more???) data tables, you now have one, coherent list.
Instead of dozens of resource-heavy results tables, you now have one. You can select the team from the drop-down box (orange).
Is this what you had in mind??
I don't know what I had in mind. All I was looking for is a formula to get the OPP column instead of typing in each OPP for each team. That's quite a change. However, it's a long list of games on the left. I'd have to scroll a long way down the list to enter the scores each week during the season. Also to do the other seasons I have to still do, that is a lot of work. I also have a custom list entered in Excel. Could I use that instead of the column of Teams on the right for the drop down list?
Bluntly, your previous data structure was a bit of a dog's breakfast. To have produced a formula to quickly do what you wanted, with the previous layout, would have been nigh-on impossible.
Excel is BUILT to deal with data arranged in columns. It moans and groans if you try to do anything else.
In the long term, you will find it much easier to keep your raw data tidy in ONE coherent block. Choose to do otherwise at your own risk.
Is your custom list manually entered, or does it exist on another sheet? I think it has to PHYSICALLY occupy cells - somewhere.
There's a section in settings that let you create custom lists (i.e. -- Days of the week, Months of the year) in Options - Advanced - General - Custom Lists. I create one with the abbreviations of the teams in a certain order based on division.
Having looked at the workbook attached to your first post again, I think we've all been misinterpreting what you want to do.
The way I see it now, your sheet is in two parts, with the upper part relating to the fixtures (i.e. above row 44) and the lower part relating to your analysis. You have a team CHI (shown in the cell starting in cell B47 and covering up to F47), and in the cells B50:B66 you show the opponents of that team on a weekly basis, derived from the fixtures tables. However, it seems to me that you are deriving the opponents manually, and what you want from us is a way to do this automatically for the team in B47 (then in G47, L47 and so on).
Is that correct?
Pete
We have a winner!!! That is correct. :D
Well, I've not done anything yet, and it is getting a bit late here now (2:00am), so it's time for bed. But, with a clearer understanding of what you want to achieve I'll take a more detailed look tomorrow.
Pete
I had a ah-ha moment this morning. How about changing references in your spreadsheet to reflect the results in my spreadsheet? Right now I'm having problems getting it to work though. I would have to change the ranges to each week for each line, but they would be the same for each team, except for the team name.
I worked out the formula for the Opponents column. Thanks a lot for your help.
You've tried to send me a Private Message 4 times last night. Is it related to this thread?
Pete
It is. In post 18, you said that you'd look into redesigning my football scores spreadsheet and get back to me. I haven't heard from you. I also thought that your private messages were full. Thanks for writing me back. I almost gave up, not that it matters much. I'm still figuring out different ways to do football spreadsheets.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks