Solved

# Show only teams and values equal to or greater than 2

Posted on 2014-10-04
97 Views
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
0
Question by:Pedrov664
• 11
• 7
• 5

LVL 45

Expert Comment

ID: 40361583
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?
0

LVL 45

Expert Comment

ID: 40361593
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?
0

LVL 45

Expert Comment

ID: 40361599
Thirdly what does "...the team with the score..." mean? In other words what cell(s) are we looking at?
0

LVL 45

Expert Comment

ID: 40361629
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?
0

Author Comment

ID: 40361642
Martinliss,

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.
0

LVL 45

Expert Comment

ID: 40361646
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?
0

LVL 45

Expert Comment

ID: 40361664
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?
0

Author Comment

ID: 40361717
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.
0

Author Comment

ID: 40361718
BTW, I prefer formulas because I may need to move the table later
0

LVL 45

Expert Comment

ID: 40361825
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.
0

Author Comment

ID: 40362035
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.
0

Author Comment

ID: 40362645
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
0

LVL 29

Expert Comment

ID: 40363212
Hi

Is this what you want ?

put this formula in cell X6 and drag down
=IF(OR(ISERROR(ABS(ROUND(INDIRECT("E" & 4*ROW(E5)-15)-INDIRECT("M" &4*ROW(M5)-15),2))),ISERROR(ABS(ROUND(INDIRECT("E" & 4*ROW(E5)-15+2)-INDIRECT("M" &4*ROW(M5)-15+2),2)))),"",IF(AND(ABS(ROUND(INDIRECT("E" & 4*ROW(E5)-15)-INDIRECT("M" &4*ROW(M5)-15),2))>2,ABS(ROUND(INDIRECT("E" & 4*ROW(E5)-15+2)-INDIRECT("M" &4*ROW(M5)-15+2),2))>2),IF(INDIRECT("E" & 4*ROW(E5)-15)-INDIRECT("M" &4*ROW(M5)-15)>0,INDIRECT("B" & 4*ROW(B5)-15),INDIRECT("J" & 4*ROW(J5)-15)),""))

and this one in Y6 and drag down
=IF(OR(ISERROR(ABS(ROUND(INDIRECT("E" & 4*ROW(E5)-15)-INDIRECT("M" &4*ROW(M5)-15),2))),ISERROR(ABS(ROUND(INDIRECT("E" & 4*ROW(E5)-15+2)-INDIRECT("M" &4*ROW(M5)-15+2),2)))),"",IF(AND(ABS(ROUND(INDIRECT("E" & 4*ROW(E5)-15)-INDIRECT("M" &4*ROW(M5)-15),2))>2,ABS(ROUND(INDIRECT("E" & 4*ROW(E5)-15+2)-INDIRECT("M" &4*ROW(M5)-15+2),2))>2),ABS(ROUND(INDIRECT("F" & 4*ROW(E5)-15+3),2))&" - "&ABS(ROUND(INDIRECT("G" &4*ROW(M5)-15+3),2)),""))

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
0

Author Comment

ID: 40363472
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.
0

Author Comment

ID: 40363529
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.
0

LVL 29

Expert Comment

ID: 40364051
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
0

Author Comment

ID: 40364139
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.
0

LVL 29

Accepted Solution

gowflow earned 500 total points
ID: 40364193
Again a clear explanation gets you a clear solution.

Please put this formula in X6 and drag it down
=IF(OR(ISERROR(ABS(ROUND(INDIRECT("F" & 4*ROW(E5)-15+3)-INDIRECT("N" &4*ROW(M5)-15+3),2))),ISERROR(ABS(ROUND(INDIRECT("F" & 4*ROW(E5)-15+3)-INDIRECT("N" &4*ROW(M5)-15+3),2)))),"",IF(AND(ABS(ROUND(INDIRECT("F" & 4*ROW(E5)-15+3)-INDIRECT("N" &4*ROW(M5)-15+3),2))>2,ABS(ROUND(INDIRECT("F" & 4*ROW(E5)-15+3)-INDIRECT("N" &4*ROW(M5)-15+3),2))>2),ABS(ROUND(INDIRECT("F" & 4*ROW(E5)-15+3),2))&" - "&ABS(ROUND(INDIRECT("G" &4*ROW(M5)-15+3),2)),""))

Put this formula in Y6 and drag it down
=IF(OR(ISERROR(ABS(ROUND(INDIRECT("F" & 4*ROW(E5)-15+3)-INDIRECT("N" &4*ROW(M5)-15+3),2))),ISERROR(ABS(ROUND(INDIRECT("F" & 4*ROW(E5)-15+3)-INDIRECT("N" &4*ROW(M5)-15+3),2)))),"",IF(AND(ABS(ROUND(INDIRECT("F" & 4*ROW(E5)-15+3)-INDIRECT("N" &4*ROW(M5)-15+3),2))>2,ABS(ROUND(INDIRECT("F" & 4*ROW(E5)-15+3)-INDIRECT("N" &4*ROW(M5)-15+3),2))>2),ABS(ROUND(INDIRECT("F" & 4*ROW(E5)-15+3),2))&" - "&ABS(ROUND(INDIRECT("G" &4*ROW(M5)-15+3),2)),""))

Pls check the attached workbook that also has the correct SO formulas so this workbook is the latest you can use.
gowflow
EE-gamestats-ER-V03.xlsm
0

Author Comment

ID: 40364433
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(INDIRECT("F" & 4*ROW(E5)-15+3)-INDIRECT("N" &4*ROW(M5)-15+3),2))),ISERROR(ABS(ROUND(INDIRECT("F" & 4*ROW(E5)-15+3)-INDIRECT("N" &4*ROW(M5)-15+3),2)))),"",IF(AND(ABS(ROUND(INDIRECT("F" & 4*ROW(E5)-15+3)-INDIRECT("N" &4*ROW(M5)-15+3),2))>2,ABS(ROUND(INDIRECT("F" & 4*ROW(E5)-15+3)-INDIRECT("N" &4*ROW(M5)-15+3),2))>2),IF(INDIRECT("F" & 4*ROW(E5)-15+3)-INDIRECT("N" &4*ROW(M5)-15+3)>0,INDIRECT("J" & 4*ROW(B5)-15),INDIRECT("B" & 4*ROW(J5)-15)),""))
0

LVL 29

Expert Comment

ID: 40364473
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
0

Author Closing Comment

ID: 40364495
Excellent! Thank you. Note formulas are the same in the text but correct in the file.
0

LVL 29

Expert Comment

ID: 40364626
Glad I could help. Let me know if you need more help on this issue.
gowflow
0

Author Comment

ID: 40365921
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.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28532801.html
0

## Featured Post

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I comeâ€¦