# 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
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Older than dirtCommented:
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
Older than dirtCommented:
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
Older than dirtCommented:
Thirdly what does "...the team with the score..." mean? In other words what cell(s) are we looking at?
0
Older than dirtCommented:
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 Commented:
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
Older than dirtCommented:
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
Older than dirtCommented:
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 Commented:
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 Commented:
BTW, I prefer formulas because I may need to move the table later
0
Older than dirtCommented:
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 Commented:
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 Commented:
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
Commented:
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 Commented:
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 Commented:
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
Commented:
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 Commented:
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
Commented:
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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
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
Commented:
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 Commented:
Excellent! Thank you. Note formulas are the same in the text but correct in the file.
0
Commented:
Glad I could help. Let me know if you need more help on this issue.
gowflow
0
Author Commented:
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
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.