Pedro
asked on
Show only teams and values equal to or greater than 2
Hi everyone,
To clarify, in the attached file, sheet named 'Current Game 2', the teams are in blocks on the left and right side. (i.e. OAK = B5:I8; KC = J5:Q8), etc. The corresponding values from each block are culled and put into the corresponding cell in X:AA.
Here we compare the 'ER-' (F8 and N8) to each other if a there is a value of 2 or greater then the team goes in X6 and the values of F8:G8 OR N8:O8 in Y6. Since that standard is not reached in those cells they remain blank.
That standard is not reached until the 'MIN' table (B33). So then, X13 = MIN (the team with the score) and Y13 = "(2) - 6" the values of F36 and G36. (i.e. the '-' between is not a minus but a "separator", negative values are shown in brackets like the number 2 here.)
Now getting to 'SO-' comparison, we see that PIT (J9) has a value of 2 or greather in the 'SO-' column (i.e. the value in P12). That said, the values shown are:
'PIT' in Z7 and 5-1 in 'AA7'. (The '-' is a separator as above.)
The same would be done for the rest of the teams down the line like was done for the 'ERA' section.
FYI: the two green sections are the ones that are being referenced here, and have been filled out by hand to show the expected results.
Hope that clarifies things.
EE-gamestats-ER-SO.xlsm
To clarify, in the attached file, sheet named 'Current Game 2', the teams are in blocks on the left and right side. (i.e. OAK = B5:I8; KC = J5:Q8), etc. The corresponding values from each block are culled and put into the corresponding cell in X:AA.
Here we compare the 'ER-' (F8 and N8) to each other if a there is a value of 2 or greater then the team goes in X6 and the values of F8:G8 OR N8:O8 in Y6. Since that standard is not reached in those cells they remain blank.
That standard is not reached until the 'MIN' table (B33). So then, X13 = MIN (the team with the score) and Y13 = "(2) - 6" the values of F36 and G36. (i.e. the '-' between is not a minus but a "separator", negative values are shown in brackets like the number 2 here.)
Now getting to 'SO-' comparison, we see that PIT (J9) has a value of 2 or greather in the 'SO-' column (i.e. the value in P12). That said, the values shown are:
'PIT' in Z7 and 5-1 in 'AA7'. (The '-' is a separator as above.)
The same would be done for the rest of the teams down the line like was done for the 'ERA' section.
FYI: the two green sections are the ones that are being referenced here, and have been filled out by hand to show the expected results.
Hope that clarifies things.
EE-gamestats-ER-SO.xlsm
Martin Liss
You say "...if a there is a value of 2 or greater then the team goes in X6...". Can both teams be 2 or greater and if so what do we do?
Also you say "...Y13 = "(2) - 6"..." but in the workbook Y13 is "2-6". Which is correct? And do you want spacing between the values?
Thirdly what does "...the team with the score..." mean? In other words what cell(s) are we looking at?
I've got most of this working and hopefully these are the last questions.
How do OAK and STL get in Z18 and Z19?
Which two cells are the source for the values in AA7?
How do OAK and STL get in Z18 and Z19?
Which two cells are the source for the values in AA7?
ASKER
Martinliss,
To answer your questions...
Can both teams be 2 or greater and if so what do we do?
The simple answer is no. If you look at the formula in cells F8 and N8 you will see "=SUM(F7-N7)" and "=SUM(N7-F7)", thus I do not believe that both can be two or greather at the same values?
Also you say "...Y13 = "(2) - 6"..." but in the workbook Y13 is "2-6". Which is correct? And do you want spacing between the values?
Spacing is not necessary and to clarify those numbers taken frrom cells F36:G36, so the numbers as they are would work. It may be that I mistakedly did not put "(2)-6" as intended.
Thirdly what does "...the team with the score..." mean? In other words what cell(s) are we looking at?
the teams are in blocks on the left and right side. (i.e. OAK = B5:I8; KC = J5:Q8), etc. The corresponding values from each block are culled and put into the corresponding cell in X:AA. You will notice those same teams in cells AB6 and AI6. This may be easier to follow since those the teams in these lines would correspond directly to the row the teams to the left should be in.
That said, also look at the formulas in V6:W21. Those formulas correspond directly to the teams that would show up in X:AA. (Please do not change these formulas)
To clarify it futher,
OAK = B5:I8; KC = J5:Q8, that data is compared that the team that meets the criteria would go in X6:AA6, (i.e. there will only be one or none at all).
Then down to the next,
SF =B9:I12; PIT = J9:Q12, that data is compared that the team that meets the criteria would go in X7:AA7, (i.e. there will only be one or none at all), etc, etc.
To answer your questions...
Can both teams be 2 or greater and if so what do we do?
The simple answer is no. If you look at the formula in cells F8 and N8 you will see "=SUM(F7-N7)" and "=SUM(N7-F7)", thus I do not believe that both can be two or greather at the same values?
Also you say "...Y13 = "(2) - 6"..." but in the workbook Y13 is "2-6". Which is correct? And do you want spacing between the values?
Spacing is not necessary and to clarify those numbers taken frrom cells F36:G36, so the numbers as they are would work. It may be that I mistakedly did not put "(2)-6" as intended.
Thirdly what does "...the team with the score..." mean? In other words what cell(s) are we looking at?
the teams are in blocks on the left and right side. (i.e. OAK = B5:I8; KC = J5:Q8), etc. The corresponding values from each block are culled and put into the corresponding cell in X:AA. You will notice those same teams in cells AB6 and AI6. This may be easier to follow since those the teams in these lines would correspond directly to the row the teams to the left should be in.
That said, also look at the formulas in V6:W21. Those formulas correspond directly to the teams that would show up in X:AA. (Please do not change these formulas)
To clarify it futher,
OAK = B5:I8; KC = J5:Q8, that data is compared that the team that meets the criteria would go in X6:AA6, (i.e. there will only be one or none at all).
Then down to the next,
SF =B9:I12; PIT = J9:Q12, that data is compared that the team that meets the criteria would go in X7:AA7, (i.e. there will only be one or none at all), etc, etc.
I'm going out shortly for the evening and when I get back I'll take a look at your responses and let you know if I have any followup questions, but before I do that I want to mention that I'm doing this via a macro rather than formulas. Is that OK?
I apologize but I still don't understand what the expression "...the team with the score..." means. In practical terms however in my code I'm putting MIN in X13 because F6 is less than N6. Had N6 been less than F6 I would put DET in X13. Is that correct?
ASKER
Martinliss,
Please look at the expected results on the table. That means I put what should result when the code is in each respective cell. I think it is the best way to explain it.
Please look at the expected results on the table. That means I put what should result when the code is in each respective cell. I think it is the best way to explain it.
ASKER
BTW, I prefer formulas because I may need to move the table later
Please look at the expected results on the table. That means I put what should result when the code is in each respective cell. I think it is the best way to explain it. For the ER 2+ comparison there's only one example, and that's not enough for me to determine how it should work. I would think that
in my code I'm putting MIN in X13 because F6 is less than N6. Had N6 been less than F6 I would put DET in X13. Is that correct?should be a pretty simple question for you to answer but if you don't want to then there's nothing I can do but to step away from this and leave it for someone smarter than I am to figure out what you mean.
ASKER
Martin,
The number in f6 or n6 should 2 or greater than 2. If that is the case then the team goes in one cell and the result goes in another cell. The examples are manually put in but I need results using formulas.
That said, since you want to step out of this, perhaps the professor or goflow, who helped in a similar problem can find a solution for this.
The number in f6 or n6 should 2 or greater than 2. If that is the case then the team goes in one cell and the result goes in another cell. The examples are manually put in but I need results using formulas.
That said, since you want to step out of this, perhaps the professor or goflow, who helped in a similar problem can find a solution for this.
ASKER
I believe part of the confusion may occur because there two separate queries in this thread. Therefore, I am splitting the thread into two.
This query will pertain to the ER2+ cells (colored light green, in the Current Game 2 sheet).
A separate thread will be opened for the SO2+ thread. Accordingly, the attached excel file has been modified highlighting ER cells with light green color to make it easier to identify which cells have source data (F:G; N:0) and which cells are to contain results based on that data (X:Y).
In cells X:Y only "MIN" meets the criteria and thus are the only cells filled in manually. (i.e. Data taken from F36:G36 with the team corresponding to cell B33).
EE-gamestats-ER.xlsm
This query will pertain to the ER2+ cells (colored light green, in the Current Game 2 sheet).
A separate thread will be opened for the SO2+ thread. Accordingly, the attached excel file has been modified highlighting ER cells with light green color to make it easier to identify which cells have source data (F:G; N:0) and which cells are to contain results based on that data (X:Y).
In cells X:Y only "MIN" meets the criteria and thus are the only cells filled in manually. (i.e. Data taken from F36:G36 with the team corresponding to cell B33).
EE-gamestats-ER.xlsm
Hi
Is this what you want ?
put this formula in cell X6 and drag down
=IF(OR(ISERROR(ABS(ROUND(I
and this one in Y6 and drag down
=IF(OR(ISERROR(ABS(ROUND(I
Again in your attached workbook I had similar issue where formula would not show results so I deleted Col XY and recreated them so it would work.
Chk the attached workbook.
gowflow
EE-gamestats-ER-V02.xlsm
Is this what you want ?
put this formula in cell X6 and drag down
=IF(OR(ISERROR(ABS(ROUND(I
and this one in Y6 and drag down
=IF(OR(ISERROR(ABS(ROUND(I
Again in your attached workbook I had similar issue where formula would not show results so I deleted Col XY and recreated them so it would work.
Chk the attached workbook.
gowflow
EE-gamestats-ER-V02.xlsm
ASKER
Goflow,
Looks like it works as intenteded, except that CWS and LAA should not appear since CWS has a value of greater than -2 in cell N48 (the 'ER-' value) and LAA has a value greater than -2 in F64 (the 'ER-' value). Note that only the bottomost value of the "ER-" column is used, if its value is two or less then the value of both "ER" cells are put in as the values.
The 'MIN' is done correctly. The others should be blank in this example.
Looks like it works as intenteded, except that CWS and LAA should not appear since CWS has a value of greater than -2 in cell N48 (the 'ER-' value) and LAA has a value greater than -2 in F64 (the 'ER-' value). Note that only the bottomost value of the "ER-" column is used, if its value is two or less then the value of both "ER" cells are put in as the values.
The 'MIN' is done correctly. The others should be blank in this example.
ASKER
Goflow,
I think I made a mistake at first and put a value of two or greater. Instead it should be a value negative 2 or less in this case. Team "MIN" is the only one that meets this criteria in the sheet.
I think I made a mistake at first and put a value of two or greater. Instead it should be a value negative 2 or less in this case. Team "MIN" is the only one that meets this criteria in the sheet.
Please advise what cell should we test values for as it is not clear neither in this case nor in SO. Be specific as the first >1 solution was a cell - an other that the result should be > 1 now you need to be specific.
gowflow
gowflow
ASKER
Goflow,
Sorry about any confusion, but it may be due to the fact that two sections were addressed here, that is the reason for splitting them.
To clarify the ER 2+ section...
NOTE: -2 or less means -2 or -3 or -4 or -5 or -6 or -7, etc, etc. This means -1, 0, 1, 2, 3, etc, etc. are not put into the X:Y cells.
OAK's 'ER-' bottomost cell is F8 and KC's bottomost cell is N8. The cell that contains any value of -2 or less should be put into the right. (i.e. -2 is shown in brackets as '(2)' in these cells).
SF's 'ER-' bottomost cell is F12 and PIT's bottomost cell is N12. The cell that contains any value of -2 or less should be put into the right. (i.e. -2 is shown in brackets as '(2)' in these cells).
DET's 'ER-' bottomost cell is F16 and KC's bottomost cell is N16. The cell that contains any value of -2 or less should be put into the right. (i.e. -2 is shown in brackets as '(2)' in these cells).
Etc, etc. on down to MIN which qualifies to be put into the table on the right.
MIN's 'ER-' bottomost cell is F36 and KC's bottomost cell is N36. The cell that contains any value of -2 or less should be put into the right. (i.e. -2 is shown in brackets as '(2)' in these cells). In this case MIN has -2 shown as "(2)" and DET has 2 and thus MIN qualifies.
That is why X13 says "MIN" and Y13 says "2-6" the values in both ER- and ER+ with a dash to separate the values.
Hope that helps.
P.S. I think if we can get this formula correct a simple tweak will work for the SO2+ which is addressed separately. It may be best to focus on this one first then the other one.
Sorry about any confusion, but it may be due to the fact that two sections were addressed here, that is the reason for splitting them.
To clarify the ER 2+ section...
NOTE: -2 or less means -2 or -3 or -4 or -5 or -6 or -7, etc, etc. This means -1, 0, 1, 2, 3, etc, etc. are not put into the X:Y cells.
OAK's 'ER-' bottomost cell is F8 and KC's bottomost cell is N8. The cell that contains any value of -2 or less should be put into the right. (i.e. -2 is shown in brackets as '(2)' in these cells).
SF's 'ER-' bottomost cell is F12 and PIT's bottomost cell is N12. The cell that contains any value of -2 or less should be put into the right. (i.e. -2 is shown in brackets as '(2)' in these cells).
DET's 'ER-' bottomost cell is F16 and KC's bottomost cell is N16. The cell that contains any value of -2 or less should be put into the right. (i.e. -2 is shown in brackets as '(2)' in these cells).
Etc, etc. on down to MIN which qualifies to be put into the table on the right.
MIN's 'ER-' bottomost cell is F36 and KC's bottomost cell is N36. The cell that contains any value of -2 or less should be put into the right. (i.e. -2 is shown in brackets as '(2)' in these cells). In this case MIN has -2 shown as "(2)" and DET has 2 and thus MIN qualifies.
That is why X13 says "MIN" and Y13 says "2-6" the values in both ER- and ER+ with a dash to separate the values.
Hope that helps.
P.S. I think if we can get this formula correct a simple tweak will work for the SO2+ which is addressed separately. It may be best to focus on this one first then the other one.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Goflow,
Looks like the formulas in the file attached above work as intended.
FYI: the first formula above is the same as the bottom, I think you meant to put as the first the one below. This one I got from your attached file and it works as intended.
=IF(OR(ISERROR(ABS(ROUND(I
Looks like the formulas in the file attached above work as intended.
FYI: the first formula above is the same as the bottom, I think you meant to put as the first the one below. This one I got from your attached file and it works as intended.
=IF(OR(ISERROR(ABS(ROUND(I
In X6 oyu are correct I got messup with copy paste as so many formulas but whatever you hv in the workbook is the final and correct one.
Sorry for that
gowflow
Sorry for that
gowflow
ASKER
Excellent! Thank you. Note formulas are the same in the text but correct in the file.
Glad I could help. Let me know if you need more help on this issue.
gowflow
gowflow
ASKER
Goflow,
I'd like to finish the W/L column on the same sheet. Perhaps you can help. Use the link below to get there.
https://www.experts-exchange.com/questions/28532801/Show-Team-with-Wins-Losses-data.html
I'd like to finish the W/L column on the same sheet. Perhaps you can help. Use the link below to get there.
https://www.experts-exchange.com/questions/28532801/Show-Team-with-Wins-Losses-data.html