Jump to content



Photo

Microsoft Excel help

excel 2013

  • Please log in to reply
9 replies to this topic

#1 +McCordRm

McCordRm

    http://richardmccord.com

  • Tech Issues Solved: 1
  • Joined: 06-July 06
  • Location: Dallas, TX

Posted 21 September 2013 - 01:52

Man, I hate programming. I just don't have the mind for it.

I have my Football spreadsheet almost done. On page 1, it runs all my

calculations. Then, for simplicity sake, I have a single Column assigned to each week,

with the various stats divided in sections.

 

                Column A[ Team names]      Column B[Offense Score]

ROW1           Dallas                                        14

ROW2           Denver                                      13

ROW3           Washington                               12

 

                     ColumnA[Team names]         Column B[Defense Score]

ROW4            Dallas                                        6

ROW5            Denver                                      7

ROW6            Washington                               8

 

                    Column A[Team name]         Column B[Special Team Score]

ROW7            Dallas                                        5

ROW8            Denver                                      6

ROW9            Washington                               7

 

On Page 2, I pit them against each other. I have a drop-down list in Columns A and C

to choose names to pit against each other. Like so:

              COLUMN A    COLUMN B   COLUMN C

ROW1        Dallas                VS           Denver

ROW2      <empty>       ANSWER       <empty>

 

I need to have Cell B2 run a Calculation. For example:

Dallas offense (Page1.B1) - Denver Defense + Special Team (Page1.B5+Page1.B8)

 

Obviously, based on the teams selected. If I change a team, it needs to change where it pulls

the data.




#2 UseLess

UseLess

    Neowinian

  • Tech Issues Solved: 1
  • Joined: 24-July 04
  • Location: Australia, West Coast

Posted 21 September 2013 - 02:15

Not a problem to do that!

 

The key will be to use "vlookup" - vertical look up. To help this along, I would change the layout of the first sheet (page) to something like this:
 

                                 Offense Score             Defense Score              Special Team Score

Dallas                                 2                                  3                                        2

Denver                                 4                              2                                           3

Washington                           2                              3                                         4

 

This will make using vlookup much easier (you could put the teams across the top and use hlookup too). It also allows you to add more teams more easily (if you had to?)

 

Without having the actual spreadsheet in front of me I won't be able to be more useful,

 

edit: seeing as you said programming wasn't your thing, I thought you wouldn't really want the "learning experience" as much and would prefer just getting it done =P Take a look at this "example" i did up:

http://members.iinet...ra/example.xlsx

 

I think i got the calculation wrong - i wasn't too sure about that...but it should show you how vlookup works at least :)

 

If you needed to track the scores "game by game" let me know and we can easily make it handle that too.



#3 OP +McCordRm

McCordRm

    http://richardmccord.com

  • Tech Issues Solved: 1
  • Joined: 06-July 06
  • Location: Dallas, TX

Posted 21 September 2013 - 02:31

Interesting. I'll play with that.

One thing: it give a number. I want it to put the name of the winning team.

So, if Dallas' total is higher than Washington it should say Dallas.

 

P.S.

The reason I laid it out that way is because there are 17 Weeks of

games. 



#4 UseLess

UseLess

    Neowinian

  • Tech Issues Solved: 1
  • Joined: 24-July 04
  • Location: Australia, West Coast

Posted 21 September 2013 - 03:57

I need to duck out for lunch, but I'll post something later on. We can make it include the 17 games.

 

I think i might need some background on this sport...I only play hockey so I'm not too sure about all of this =P What does the offense/defense/special score mean? or where does it come from?



#5 OP +McCordRm

McCordRm

    http://richardmccord.com

  • Tech Issues Solved: 1
  • Joined: 06-July 06
  • Location: Dallas, TX

Posted 21 September 2013 - 04:51

I went ahead and changed the format to be in-line with what you suggested.

[Column A]            [Column B]             [Column C]           [Column D]             [Column E]

Team Name        Offense Score     Defense Score      Special Team         Offense Score

Arizona                       5                            6                          7                             8

Arkansas                    8                            7                          6                             5

Baltimore                    1                            2                          3                             4

 

 

Where Columns B-D are the scores for Week 1, and Column E begins Week 2, etc.

 

 

P.S.

As for where the points come from, I'm running equations which give points to each team to

rate how good they are at each thing. Offense is the ability to score, Defense is the ability

to stop the opposing team from scoring, and Special Teams get rated for both.



#6 UseLess

UseLess

    Neowinian

  • Tech Issues Solved: 1
  • Joined: 24-July 04
  • Location: Australia, West Coast

Posted 21 September 2013 - 12:21

Ok - my second attempt.

 

http://members.iinet...a/example2.xlsx

 

Sorry to change the format again for you! With the 17 weeks, it is easier to have them all next to each other. It makes the "hlookup"s easier to use. I assume you wanted the "latest" week scores to show and be used in the calculations? You can change the formulas in the top table to be the SUM (see excel function) of the teams offense/defense/special instead of the latest week (using VLOOKUP).

 

I have updated page 2 also. Did you want to be able to select 2 teams (those that are playing?) and have it do the calculations and show you the winner? I think the calculation I used was wrong. I did: Offense(Team1) - Defense(Team2) + Special(Team1)

 

I can explain it better how everything works, but if you take a look, you should be able to see what I'm trying to do.

 

Let me know how you go!



#7 OP +McCordRm

McCordRm

    http://richardmccord.com

  • Tech Issues Solved: 1
  • Joined: 06-July 06
  • Location: Dallas, TX

Posted 21 September 2013 - 17:25

Wow. That's exactly what I needed. 

I threw it into an IF function to bypass the "display scores" step, but other

than that it's perfect. I can work on the formula to get the output I need.

 

Thanks much! This is awesome.

 

P.S.

You just disproved your Username.



#8 OP +McCordRm

McCordRm

    http://richardmccord.com

  • Tech Issues Solved: 1
  • Joined: 06-July 06
  • Location: Dallas, TX

Posted 21 September 2013 - 20:11

Ok - my second attempt.

 

http://members.iinet...a/example2.xlsx

 

Sorry to change the format again for you! With the 17 weeks, it is easier to have them all next to each other. It makes the "hlookup"s easier to use. I assume you wanted the "latest" week scores to show and be used in the calculations? You can change the formulas in the top table to be the SUM (see excel function) of the teams offense/defense/special instead of the latest week (using VLOOKUP).

 

I have updated page 2 also. Did you want to be able to select 2 teams (those that are playing?) and have it do the calculations and show you the winner? I think the calculation I used was wrong. I did: Offense(Team1) - Defense(Team2) + Special(Team1)

 

I can explain it better how everything works, but if you take a look, you should be able to see what I'm trying to do.

 

Let me know how you go!

OK, here's how I have it laid out on Page1:

 

             A                    B                  C                D               E               F                    G                  H                I     

01.    TEAMS           --------------------WEEK ONE--------------------       ------------------------WEEK TWO-------------------

02.                          Points M       Points A      DEF A       DEF A        Points M       Points A        DEF A       DEF A

03.    Arizona                5                  6                7               8                5                  6                  7                 8

04.    Atlanta                 4                  5                6               7                4                  5                  6                 7

05.    Baltimore             3                  4                5               6                3                  4                  5                 6

 

 

 

And then figuring out the winner on Page2:

           A                    B                    C                 D                       E

01.                            ---WEEK ONE--               ------WEEK TWO-------   

02.    GAME1        Arizona           Atlanta          Atlanta          Baltimore

03.                        ------WINNER--------             -------WINNER-----------

 

 

The Formula for WINNER is:

=IF(SUM(IF(VLOOKUP(D2,Page2!A3:E34,2,FALSE)>VLOOKUP(E2,Page2!A3:E34,3,FALSE),VLOOKUP(E2,Page2!A3:E34,3,FALSE),VLOOKUP(D2,Page2!A3:E34,2,FALSE)),(VLOOKUP(D2,Page2!A3:E34,4,FALSE)-VLOOKUP(E2,Page2!A3:E34,5,FALSE)))>SUM(IF(VLOOKUP(E2,Page2!A3:E34,2,FALSE)>VLOOKUP(D2,Page2!A3:E34,3,FALSE),VLOOKUP(D2,Page2!A3:E34,3,FALSE),VLOOKUP(E2,Page2!A3:E34,2,FALSE)),(VLOOKUP(E2,Page2!A3:E34,4,FALSE)-VLOOKUP(D2,Page2!A3:E34,5,FALSE))),D2,E2)



#9 UseLess

UseLess

    Neowinian

  • Tech Issues Solved: 1
  • Joined: 24-July 04
  • Location: Australia, West Coast

Posted 22 September 2013 - 04:11

Nice work! So it seems you've ended up with something that works for you?



#10 OP +McCordRm

McCordRm

    http://richardmccord.com

  • Tech Issues Solved: 1
  • Joined: 06-July 06
  • Location: Dallas, TX

Posted 22 September 2013 - 05:51

Yep.

I have to adjust the numbers for each Cell, each week, but I just put

the Formula in Notepad and use Find/Replace. Pretty simple.





Click here to login or here to register to remove this ad, it's free!