Sign in to follow this  
Followers 0
McCordRm

Microsoft Excel help

10 posts in this topic

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.


Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.