Solved

Show Team with Wins/Losses data

Posted on 2014-10-07
4
127 Views
Last Modified: 2014-10-07
Hi everyone,

On the attached file on the page "Current Game 2" sheet, I would like to see the teams wins losses as follows:

S:T shows team with most wins

S = team name (taken from 'B' or 'J' row)
T = value on the bottomost wins column (taken from 'C' or 'K')

U:V shows team with least losses

U= team name (taken from 'B' or 'J' row)
V = value on the bottomost loss column (taken from 'D' or 'L')

In case any value is "Zero" then neither team is put in. That is both have the same value of zero.


For illustration purposes,

Cell C8 = 1 thus S6 = 'OAK' and T6 = 1
Cell L8 = (1) thus U6= 'KC' and V6 = (1). NOTE: '(1)' = negative one. This denotes less losses.

Cell C12 = 1 thus S7 = 'SF' and T7 = 1
Cell L12 = (1) thus U7= 'PIT' and V7 = (1). NOTE: '(1)' = negative one. This denotes less losses.

Cell C16 = 1 thus S8 = 'DET' and T8 = 1
Cell C16 = (1) thus U8= 'DET' and V8 = (1). NOTE: '(1)' = negative one. This denotes less losses.

Cell C20 = 2 thus S9 = 'LAA' and T9 = 2
Cell C20 = (1) thus U9= 'LAA' and V9 = (1). NOTE: '(1)' = negative one. This denotes less losses.

etc, etc,

When the values are 'zero' then no team or score is put in the cell as occurs in S12, S13, T12, T13 for the ATL:PHI and MIN:DET teams.

NOTE: Cells S6:V13 have been filled in manually to illustrate what the code should do.
EE-gamestats-W-L.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
  • 2
4 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40366713
not sure what exactly you want.


you mentioned that you want S:T shows team with most wins  then as per your data the most wins do not match with the manually filled codes as per your "NOTE: Cells S6:V13 have been filled in manually to illustrate what the code should do."
0
 
LVL 30

Accepted Solution

by:
gowflow earned 500 total points
ID: 40366724
Well I am not 100% sure If it is correct as some values do not correspond to your example however you can try to check them out.

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

Put this in Cell T6 and drag it down
=IF(OR(ISERROR(ABS(ROUND(INDIRECT("C" & 4*ROW(E5)-15+3)-INDIRECT("K" &4*ROW(M5)-15+3),2))),ISERROR(ABS(ROUND(INDIRECT("C" & 4*ROW(E5)-15+3)-INDIRECT("K" &4*ROW(M5)-15+3),2)))),"",IF(AND(ABS(ROUND(INDIRECT("C" & 4*ROW(E5)-15+3)-INDIRECT("K" &4*ROW(M5)-15+3),2))>0,ABS(ROUND(INDIRECT("C" & 4*ROW(E5)-15+3)-INDIRECT("K" &4*ROW(M5)-15+3),2))>0),IF(INDIRECT("C" & 4*ROW(E5)-15+3)-INDIRECT("K" &4*ROW(M5)-15+3)>0,INDIRECT("C" & 4*ROW(B5)-15+3),INDIRECT("K" & 4*ROW(J5)-15+3)),""))

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

Put this in Cell V6 and drag it down
=IF(OR(ISERROR(ABS(ROUND(INDIRECT("D" & 4*ROW(E5)-15+3)-INDIRECT("L" &4*ROW(M5)-15+3),2))),ISERROR(ABS(ROUND(INDIRECT("D" & 4*ROW(E5)-15+3)-INDIRECT("L" &4*ROW(M5)-15+3),2)))),"",IF(AND(ABS(ROUND(INDIRECT("D" & 4*ROW(E5)-15+3)-INDIRECT("L" &4*ROW(M5)-15+3),2))>0,ABS(ROUND(INDIRECT("D" & 4*ROW(E5)-15+3)-INDIRECT("L" &4*ROW(M5)-15+3),2))>0),IF(INDIRECT("D" & 4*ROW(E5)-15+3)-INDIRECT("L" &4*ROW(M5)-15+3)>0,INDIRECT("L" & 4*ROW(B5)-15+3),INDIRECT("D" & 4*ROW(J5)-15+3)),""))

Pls chk the attached workbook.
gowflow
EE-gamestats-W-L-V01.xlsm
0
 

Author Closing Comment

by:Pedrov664
ID: 40367413
Goflow,

That is it!

It looks like I posted a workbok then made changes to it before I posted the examples. But, the results I got match the expected results based on the table data.

Thanks Again!
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40367697
Your welcome. I will keep this question monitored case you need more help in this subject pls let me know by putting a link in here.
gowflow
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

752 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