Show only data that has a value equal to or greater than 1

Hi everyone,

The attached file in the 'Current Game 2' sheet Rows V:W contain a formula(s) that will show data from the table next to it.

However, I would like to only show data when there is value in 'W' => than 1.

In the attached table only DET (V13:W13), KC (V16:W16), STL (V18:W18), and SEA (V20:W20) should show data, because the 'W' column value is equal to or greater than 1. The other items that currently show data should not show data since the value in the 'W' column is less than 1.

Hope someone can help.
EE-gamestats-ERA.xlsm
Pedrov664Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

ProfessorJimJamMicrosoft Excel ExpertCommented:
SEE THE ATTACHED FILE.
EE-gamestats-ERA.xlsm
0
Pedrov664Author Commented:
Professor,

I see no difference in the formulas or the results.

The idea is to only show DET (V13:W13), KC (V16:W16), STL (V18:W18), and SEA (V20:W20) in the results of V:W nothing else. In other words, all other cells would be blank since they do not meet the criteria.
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
I MUST HAVE attached wrong file. check this one please and revert.
Copy-of-EE-gamestats-ERA.xlsm
0
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

gowflowCommented:
Well we need here not only verify that it is greater than 1 but also get rid of these errors of DIVO etc...
Here are the formulas:

Col V put in V6 the below formula 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))>1,ABS(ROUND(INDIRECT("E" & 4*ROW(E5)-15+2)-INDIRECT("M" &4*ROW(M5)-15+2),2))>1),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)),""))

Col W put this in W6 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))>1,ABS(ROUND(INDIRECT("E" & 4*ROW(E5)-15+2)-INDIRECT("M" &4*ROW(M5)-15+2),2))>1),ABS(ROUND(INDIRECT("E" & 4*ROW(E5)-15)-INDIRECT("M" &4*ROW(M5)-15),2))&" - "&ABS(ROUND(INDIRECT("E" & 4*ROW(E5)-15+2)-INDIRECT("M" &4*ROW(M5)-15+2),2)),""))

please check the below file
gowflow
EE-gamestats-ERA-V01.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
Pedrov664Author Commented:
Professor,

Looks like your formula works,

Goflow,

Good Job on the details. I greatly appreciate the fact that you cleaned up and got rid of those errors. It helps a lot.

P.S. Would it be possible to use the same formula for ER and SO section?
0
gowflowCommented:
What is ER and SO sections ?? What should it get there ?
gowflow
0
Pedrov664Author Commented:
Goflow,

The teams remain the same, except 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", the minus 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.

Hope that clarifies things.
0
gowflowCommented:
Well quite complex I should say but for sure to answer your question if you think that you can simply drag the formula from W6 to X6 to give you above result the answer is a definite NO !

Now if you want to get these filled I guess this could be object of a new question as for sure I read your post but to be honest it is in no way clear to me and need to dissect all this and have several questions.

Let me know your decision.
gowlfow
0
Pedrov664Author Commented:
Goflow,

I agree and thus have posted another question with a clarifying section in green.

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

Hope that helps
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
Yes,
And now please don't leave this thread in limbo and mark it as accepted , I am happy to split the points with gowflow as assistited solution
0
Pedrov664Author Commented:
Professor,

I would agree, but I would like to know if Goglow also agrees. Since both contributed to the thread both should agree to split the points.

Best regards,
Pedro
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
It is not a big deal , accept the solution that has helped you, even if you give the whole points to gowflow.
0
gowflowCommented:
Sorry just saw these comments. We as Experts do not have a say in points issue it is solely the asker's decision to attribute points as he deem appropriate.

The Experts participating in a question have always the possibility to ask for moderator's assistance case they feel that points have been attributed inadequately explaining their point of view. In this case it is moderator's action that will prevail.

Hope above clarify this issue.
gowflow
0
Pedrov664Author Commented:
I believe both contributed to this thread and thus I will accept multiple solutions and assign equal values to each.

P.S. Since both have solved this thread I believe it will be easier for you to solve two others and are:

1) focuses on the 'SO' section
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28531708.html

2) focuses on the 'ER' section
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28531302.html#a40361825
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
VB Script

From novice to tech pro — start learning today.