Solved

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

Posted on 2014-10-03
14
113 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 31

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 31

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 31

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 31

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

635 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