• 0

Microsoft Excel help

Asked by McCordRm,

 Share

Question

Grand Master

McCordRm

Posted
Posted

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.


Link to comment
Share on other sites

9 answers to this question

Recommended Posts

  • 0
Proficient

UseLess

Posted
Posted

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.net.au/~vinciguerra/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.

Link to comment
Share on other sites
  • 0
Grand Master

McCordRm

Posted
  • Author
Posted

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. 

Link to comment
Share on other sites
  • 0
Proficient

UseLess

Posted
Posted

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?

Link to comment
Share on other sites
  • 0
Grand Master

McCordRm

Posted
  • Author
Posted

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.

Link to comment
Share on other sites
  • 0
Proficient

UseLess

Posted
Posted

Ok - my second attempt.

 

http://members.iinet.net.au/~vinciguerra/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!

Link to comment
Share on other sites
  • 0
Grand Master

McCordRm

Posted
  • Author
Posted

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.

Link to comment
Share on other sites
  • 0
Grand Master

McCordRm

Posted
  • Author
Posted

Ok - my second attempt.

 

http://members.iinet.net.au/~vinciguerra/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)

Link to comment
Share on other sites
This topic is now closed to further replies.
 Share
Go to question listing