Solved

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

Posted on 2014-10-03
14
111 Views
Last Modified: 2014-10-05
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
0
Comment
Question by:Pedrov664
  • 6
  • 4
  • 4
14 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40359668
SEE THE ATTACHED FILE.
EE-gamestats-ERA.xlsm
0
 

Author Comment

by:Pedrov664
ID: 40359680
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
 
LVL 26

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 250 total points
ID: 40359704
I MUST HAVE attached wrong file. check this one please and revert.
Copy-of-EE-gamestats-ERA.xlsm
0
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
LVL 29

Accepted Solution

by:
gowflow earned 250 total points
ID: 40359722
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
 

Author Comment

by:Pedrov664
ID: 40361239
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40361259
What is ER and SO sections ?? What should it get there ?
gowflow
0
 

Author Comment

by:Pedrov664
ID: 40361299
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40361430
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
 

Author Comment

by:Pedrov664
ID: 40361488
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
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40361553
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
 

Author Comment

by:Pedrov664
ID: 40361643
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
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40361663
It is not a big deal , accept the solution that has helped you, even if you give the whole points to gowflow.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40361829
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
 

Author Comment

by:Pedrov664
ID: 40362663
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question