Solved

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

Posted on 2014-10-03
14
107 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 25

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 25

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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 25

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 25

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
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…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now