Solved

Show only team values equal to or greater than two

Posted on 2014-10-05
7
121 Views
Last Modified: 2014-10-07
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 Z:AA.

   The '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.)

  The same would be done for the rest of the teams down the line.

 FYI: the dark green sections are the ones that are being referenced here, and have been filled out by hand to show the expected results.

Note also that the blanks in Z:AA are blank because the teams do not meet the criteria on having a number of two or greater in the "SO-" section (i.e. H or P). Only one team will have a value of two or greater in those cells)
EE-gamestats-SO.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
  • 4
  • 3
7 Comments
 
LVL 30

Expert Comment

by:gowflow
ID: 40363308
What is the value that should be >2 it is not clear to me for the PIT example is it:
P12 + Q12
or
P12 - Q12
or ... ?

pls clarify this point.
gowflow
0
 

Author Comment

by:Pedrov664
ID: 40363395
Goflow,

J9 is the value to use as the standard. If it is >=2 then "PIT" is the team as is in this case. To clarify the values are not added or subtracted the value in that cell is taken at face value.
0
 

Author Comment

by:Pedrov664
ID: 40363521
Goflow,

To clarify further, I will use the examples in the attached sheet and go over them one by one.

SF:PIT block comparison PIT has a value of 5 and SF -5. In this case PIT's value is greather than two PIT goes on the left side (Z7) and the 'SO' values '5-1' go on the right side (AA7).

BAL:TOR block comparison TOR has a value of 2 and BAL -2. In this case TOR 's  value is greather than two; TOR goes on the left side (Z10) and the 'SO' values '2-0' go on the right side (AA10).

TB:CLE block comparison CLE has a value of 5 and the other team's value is less. In this case CLE 's  value is greather than two; CLE goes on the left side (Z11) and the 'SO' values '5-12' go on the right side (AA11).

The same will apply to all other teams. The other teams that meet this criteria are: DET, CWS, OAK, STL and LAD. All the teams have been put in manually in their respective cells with scores in the "Current Game 2" sheet.
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 30

Accepted Solution

by:
gowflow earned 500 total points
ID: 40364152
ok here it is:

a clear explanation gets you a clear answer and solution.

Put this formula in Cell Z6 and drag it down
=IF(OR(ISERROR(ABS(ROUND(INDIRECT("P" & 4*ROW(E5)-15+3)-INDIRECT("H" &4*ROW(M5)-15+3),2))),ISERROR(ABS(ROUND(INDIRECT("P" & 4*ROW(E5)-15+3)-INDIRECT("H" &4*ROW(M5)-15+3),2)))),"",IF(AND(ABS(ROUND(INDIRECT("P" & 4*ROW(E5)-15+3)-INDIRECT("H" &4*ROW(M5)-15+3),2))>2,ABS(ROUND(INDIRECT("P" & 4*ROW(E5)-15+3)-INDIRECT("H" &4*ROW(M5)-15+3),2))>2),IF(INDIRECT("P" & 4*ROW(E5)-15+3)-INDIRECT("H" &4*ROW(M5)-15+3)>0,INDIRECT("J" & 4*ROW(B5)-15),INDIRECT("B" & 4*ROW(J5)-15)),""))

Put this formula in cell AA6 and drag it down
=IF(OR(ISERROR(ABS(ROUND(INDIRECT("P" & 4*ROW(E5)-15+3)-INDIRECT("H" &4*ROW(M5)-15+3),2))),ISERROR(ABS(ROUND(INDIRECT("P" & 4*ROW(E5)-15+3)-INDIRECT("H" &4*ROW(M5)-15+3),2)))),"",IF(AND(ABS(ROUND(INDIRECT("P" & 4*ROW(E5)-15+3)-INDIRECT("H" &4*ROW(M5)-15+3),2))>2,ABS(ROUND(INDIRECT("P" & 4*ROW(E5)-15+3)-INDIRECT("H" &4*ROW(M5)-15+3),2))>2),ABS(ROUND(INDIRECT("P" & 4*ROW(E5)-15+3),2))&" - "&ABS(ROUND(INDIRECT("Q" &4*ROW(M5)-15+3),2)),""))

Pls see attached file. BTW the formula in ER 2+ not yet corrected pending your reply to the other question.
gowlfow
EE-gamestats-SO-V02.xlsm
0
 

Author Closing Comment

by:Pedrov664
ID: 40364498
Goflow,

Good Work! Thank you again.
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40364627
Glad I could help. Let me know if you need more help on this issue.
gowflow
0
 

Author Comment

by:Pedrov664
ID: 40365918
Goflow,

Perhaps you'd like to help me finish the chart in the link below,

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

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

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…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

728 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