Excel - Can I Use It To Automate Math Tasks? 2

[maybeimaleo]

I'm not very fluent in formulas and the like, but I have been hoping to automate some tasks for a long time. I just do not know where to begin.


I calculate scores for a free fantasy formula1 grand prix and, quite frankly, I am tired of doing it manually!

This is how the spreadsheet is setup:

It lists the drivers, manufacturers, and engines and who scored points for a given GP. Below this is a list of all of the teams and their selections (two drivers, one chassis [aka - mfg's], an engine, and a bonus GP, where all points are doubled).

What I want to do is this:

Type in the drivers, chassis, and engine in a cell, then put the points in a different cell, for example:
cell a,1: vettel cell b,1: 25
cell a,2: webber cell b,2: 18
So on and so forth for drivers, chassis, and engine.
Then I want to write code that will take the driver's/chassis'/engine's name and basically do a find/replace - find the drivers name in a,1 and replace it with the contents of b,1.
Once that is done I want to add the numbers for each team, doubling the number for anyone with a bonus GP, then sorting all teams but the total points column.

I would also like to enumerate the teams (1,2,3,4,4,4 if tied, etc..) and eventually I would like to find a way to take this sheet and add it will others so that I could automate the addition , sorting, and enumerating the overall standings.

Can anyone help steer my in the right direction?
I am sorry that this is long, but any help in the right direction would be a HUGE help for me.

Thanks!

 

[SkipVought]

Set up your sheet like a table with heading names in Row 1.

Do not include multiple data items in one cell.
[tt]
A B C D
DriverID drivers chassis engine
1 vettel 25
2 webber 18
[/tt]

Then I want to write code that will take the driver's/chassis'/engine's name and basically do a find/replace - find the drivers name in a,1 and replace it with the contents of b,1

Could you explain by use of an example, the before and after.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zelgar]

There's several ways of doing this. One way is a macro, but a simpler method is by creating named lists and using vlookup function.

One sheet of the spreadsheet will contain all of your data and the other will be your Output/Ranking Sheet.

First, group all of the data relating to a specific subject and name the range (e.g., Range A1:B30 is driver information with col A being driver's name and col B is their points value). Name the range drivers (highlight area and type "driver" in name box -next to formula bar)

After you've gotten all of your data entered, go to the Output Sheet. For each record enter the drivers name (you can get fancy using Data Validation if you wnat to restrict the names to only the ones you used before). Using Vlookup, you can have the spreadsheet automatically fill in the point value from your other sheet. (e.g., VLOOKUP(B24,skills2,2,FALSE) would look for information from Column 2 of the skills2 named range and look for an exact match to the value contained in Cell b24.)

The only thing you'd have to manually do is add info about GP points and do a sort on the totals.

 

[zelgar]

I'm trying to attach a file. If this works, the attached is an example of what I'm talking about.

 

[zelgar]

Drat, it didn't work. I'll try to upoload the file later.

 

[maybeimaleo]

Then I want to write code that will take the driver's/chassis'/engine's name and basically do a find/replace - find the drivers name in a,1 and replace it with the contents of b,1"

What I mean is this:
There are many worksheets in the workbook, one for each GP and one for the overall standings. All of them are labeled. The Canadian Grand Prix was held this past Sunday, so it is the worksheet I would be using.
I think the best way to go about it is to manually type in all of the info - drivers who finished 1st through 10th, such as:
columnA colB colC colD colE colF
Driver PTS Chassis PTS Engine PTS
Vettel 25 RedBull 25 Renault 25
Webber 18 MercedesGP 18 Mercedes 18
Rosberg 15 McLaren 15 Ferrari 15
Button 12 FerrariMfg 12 Cosworth 12
Alonso 10 STR 10
Buemi 8 Sauber 8
Perez 6 TeamLotus 6
Massa 4 HRT 4
Trulli 2 Williams 2
Liuzzi 1 ForceIndia 1

Below this is currently the teams and their selections:
teamname drver1 drvr2 3rd drvr chass eng bonusgp
team1 massa buemi fisichella STR ferrari italy

Manually, I do a find/replace. I would look for vettel and replace his name with a 25, so on and so forth until the teams looked like this:
teamname drver1 drvr2 3rd drvr chass eng total bonusgp
team1 4 8 fisichella 10 15 italy
(don't worry about the 3rd driver)
I would then select the cell under the total column, click on the sum button, highlight all of the fields except teamname and bonusgp, and click enter.
I then extend the sum function to all of the teams. Once the totals are complete I looked for anyone who chose Canada as their bonus GP. I add a *2 to any team who chose that GP.
Then I sort descending, insert a column next to A, freeing it up so I can rank the teams manually.
Once done I copy the results to a website.

I want to find a way in which all I have to do is input the numbers for drivers, chassis, and engine; then let Excel do the rest.

 

[SkipVought]

unfortunately, you have merely posted a bunch or UNRELATED data, rather than a series of examples that can be used together.

Below this is currently the teams and their selections:
teamname drver1 drvr2 3rd drvr chass eng bonusgp
team1 massa buemi fisichella STR ferrari italy

In order to make use of this data, it needs to be normalized like this...
[tt]
teamname drver
team1 massa
team1 buemi
team1 fisichella
[/tt]
and another table for the team car configuration
[tt]
teamname chass eng bonusgp
team1 STR ferrari italy
[/tt]




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[maybeimaleo]

Well no, it isn't unrelated, I was just showing everyone how the data is currently formatted.

zelgar, very good posts, but how do I add everything up? DO I just do that manually?
I ask because if some of the vlookups return a value of #N/A they screw up my autosum. I was hoping to automate that somehow.

 

[maybeimaleo]

SkipVought - how would I use the data once it is in the format you propose? I am curious to know what your idea is!

Okay, I have setup one team in the way you mentioned.

 

[SkipVought]

I will post a spreadsheet solution in an hour or so.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[maybeimaleo]

cool, thanks!

 

[SkipVought]

here are the essential elements. All column data ranges are Named Ranges, based on the heading values.

1. The table of driver points
[tt]

Driver DPTS CPTS EPTS
Vettel 25 25 25
Webber 18 18 18
Rosberg 15 15 15
Button 12 12 12
Alonso 10 10
Buemi 8 8
Perez 6 6
Massa 4 4
Trulli 2 2
Liuzzi 1 1
fisichella 1 1
[/tt]
2. The Driver Team Table
[tt]
DRV_Team DRV_Driver
team1 massa
team1 buemi
team1 fisichella
team2 Button
team2 Webber
team2 Liuzzi
[/tt]
3. The Car Oarameters table
[tt]

CAR_Team CAR_Chassis CAR_Engine CAR BonusGrp
team1 STR ferrari Italy
team2 HRT Renault France
[/tt]
4. The result table in A1 on sheet2
[tt]
1 2 3
DPTS DPTS DPTS CPTS EPTS
Team Driver1 Driver2 Driver3 Chassis Engine Total BonusGrp
team1 4 8 1 1 0 14 Italy
team2 12 18 1 1 0 32 France
[/tt]
there are three formulas.
the first for driver points
[tt]
=INDEX(INDIRECT(B$2),MATCH(INDEX(DRV_Driver,MATCH($A4,DRV_Team,0)+B$1-1,1),Driver,0),1)
[/tt]
the second sums the points
[tt]
=SUM(B4:F4)
[/tt]
the third returns the BonusGrp value for each team
[tt]
=INDEX(INDIRECT("CAR_"&H$3),MATCH($A4,CAR_Team,0),1)
[/tt]
If my understanding of the data relationships is not correct, please advise.

I would also request that if that is the case, then you post a complete set of consistent data along with the necessary explanation.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zelgar]

Hopefully this works. The 1st sheet contains the raw data, modify as needed. To add drivers, chassis and engines, Insert rows in between the 1st and last as the named cells will automatically be for all of the different types.

The Output sheet has drop-down for each type of data (e.g., driver, chassis and engine) by use of Data Validation.

Hopefully this helps (and the attachment works)

 

[maybeimaleo]

cool, zlegar, thank you!

skipvought - I will copy yours once I get home and try it out. I may end up using both! Thanks!


How easy would it be, after I sort all of teh team by points (largest to smallest) to then rank them?

 

[SkipVought]

A simple sort, I would think.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[zelgar]

You might want to highlight the following cells in the Data Sheet.
cells---------Associated Name
Cells A1:B7......Drivers
Cells A9:B15.....Chassis
Cells A17:B23....Engine

If you highlight these cells (maybe a different color for each type) it will be easier to know if you've correctly inserted the addital drivers, chassis and engines correctly. As long as you insert the rows in the middle of the cells in the Named areas, the Named fields will automatically expand (e.g., if you decided to add a few more drivers, if you selected rows 2 to 6 and Insert Rows, the Named Drivers would be now associated with Cells A1:B12 and the Chassis and Engine cells would be moved as well.

For Ranking, you can sort by Column P or you can add the following code
Cell Q2 would be =Rank(P2,P$2$5) and copy down

 

[maybeimaleo]

Stupid question SkipVought - where do I place the formulas?

 

[SkipVought]

4. The result table in A1 on sheet2
[tt]
1 2 3
DPTS DPTS DPTS CPTS EPTS
Team Driver1 Driver2 Driver3 Chassis Engine Total BonusGrp
team1 4 8 1 1 0 14 Italy
team2 12 18 1 1 0 32 France

[/tt]
in the cell adjacent to team1, below Driver1, paste the first formula and then copy and paste into the remainder teams & thru column F. The second into G, the third into H.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[maybeimaleo]

zelgar - how do I calculate the bonus gp?
Anyone who choose the gp that was just completed gets their points doubled.

I don't have that problem right now because no one chose the European GP but I will have this problem when the British GP is up in two weeks.

 

[zelgar]

I wasn't certain how the GP points were awarded. In my spreadsheet, I set up 10 cols that you could enter GP points (Cols F-O). Any values entered into these cells will automatically be summed and doubled in the the total points column (Col P)