Basic Excel - How to plot Dozens/Columns/Color Automatically

[MattyMattz]

Hey gang,
thought I would share a bit of excel know-how with anyone that's looking for some basics.  In the following blurb, I explain how to set up an excel sheet to automatically tell you what dozen, column and color the number you enter is part of... enjoy:


- Start off with a new Excel Workbook.  Once opened you should have 3 sheets (tabs) at the bottom of the page.
- Click on Sheet2.
- In cell A1 - type "Numbers"
- In cell B1 - type "Dozen"
- In cell C1 - type "Column"
- In cell D1 - type "Color"

For now, your table should look like this:

[table=,]
Numbers,,,Dozen,,,Column,,,Color
[/table]


- Under the title "Numbers" enter 1 to 36 downwards

[table=,]
Numbers,,,Dozen,,,Column,,,Color
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[/table]

(*Don't worry about the zero for now)

- Now under "Dozen" type in the appropriate Dozen relating to the number on the left.  Do the same for the Columns and Colors.
- Now your table should look like this:

[table=,]
Number,Dozen,Column,Color
1,1,1,Red
2,1,2,Black
3,1,3,Red
4,1,1,Black
5,1,2,Red
6,1,3,Black
7,1,1,Red
8,1,2,Black
9,1,3,Red
10,1,1,Black
11,1,2,Black
12,1,3,Red
13,2,1,Black
14,2,2,Red
15,2,3,Black
16,2,1,Red
17,2,2,Black
18,2,3,Red
19,2,1,Red
20,2,2,Black
21,2,3,Red
22,2,1,Black
23,2,2,Red
24,2,3,Black
25,3,1,Red
26,3,2,Black
27,3,3,Red
28,3,1,Black
29,3,2,Black
30,3,3,Red
31,3,1,Black
32,3,2,Red
33,3,3,Black
34,3,1,Red
35,3,2,Black
36,3,3,Red
[/table]

- Okay, now save your work.
- Go back to Sheet1 (this is were you'll be entering your test numbers)
- In Cell A1,B1,C1,D1 type in the same titles as you did on Sheet2.
- So your Sheet1 should look like this:

[table=,]
Numbers,,,Dozen,,,Column,,,Color
[/table]

- Now comes the fun part...
- We'll start with the Dozen column.
- In cell B2 (still on Sheet1) type in the following:

=IF(A2="","",IF(A2=0,0,VLOOKUP(A2,Sheet2!$A$1:$D$37,2,FALSE)))

- After this, all you need to do is "autofill" the rest of the column to complete the task.
- click once on cell B2
 - in the bottom right corner you'll see a little black box
- you need to click and drag it down to say row 200 (just stay in that column though).  Once your down to around row 200, just let go of the mouse (so don't drag anymore)
- now save it
 - now we need to do the columns... same idea
- in cell c2 type the following:

=IF(A2="","",IF(A2=0,0,VLOOKUP(A2,Sheet2!$A$1:$C$37,3,FALSE)))

 - now, just auto fill again down to row 200
 - okay - so you want another column that tell you red or black (or green)
- now type in the following in cell d2:

  =IF(A2="","",IF(A2=0,"Green",(VLOOKUP(A2,Sheet2!$A$1:$D$37,4,FALSE))))

 - then autofill again to row 200 (and save it!)

You're all done!  Now you should have something that looks like this:

[table=,]
Number,Dozen,Column,Color
1,1,1,Red
2,1,2,Black
3,1,3,Red
4,1,1,Black
0,0,0,Green
17,2,2,Black
24,2,3,Black
36,3,3,Red
30,3,3,Red
30,3,3,Red
30,3,3,Red
14,2,2,Red
15,2,3,Black
19,2,1,Red
1,1,1,Red
5,1,2,Red
31,3,1,Black
30,3,3,Red
27,3,3,Red
26,3,2,Black
0,0,0,Green
0,0,0,Green
[/table]

Hope someone can gain some use from this.

Cheers,
Matt

[ChickenDinner]

Brilliant! Thanks Matt! [smiley=thumbsup.gif]

[MattyMattz]

Your welcome guys - glad I can be of some use
Feel free to ask questions. 

Cheers,
Matt

[NoBody]

Thanks matt.

Always helpfull and generous in sharing your knowlege.

Thanks again!!

[keith4444]

Nice one Matt!

[TwoCatSam]

Matt

Yes, that would be excellent.  I'd love to be able to graph a few things. 

May I join the class?  I'll shower....

Sam

[MattyMattz]

Yep - no problem gang.  I'll type it up asap.  Just having some problems with adding/inserting pics, but I'll figure it out... I've done it before.  I'll need a table first... If no one posts one with some data, then I'll just make a basic one with bankroll and spins.

Cheers,

Matt

[MattyMattz]

Sure guys,
graphs are pretty simple actually.  What I'll need first is a table that we want to graph... we'll start with a basic table of spins and Bankroll.

[table=,]
#Spins,Bankroll
10,15
17,25
22,27
36,61
49,41
71,72
77,77
87,88
101,100
[/table]

So now we have a table that we want to graph...
- Highlight all the data you want included in your graph/chart.  In this case cells A1 to B10 (Include the titles as well).
- Click "Insert" at the top of the page (between "View" and "Format")
- Select "chart"
- from here you should have this on your screen:

[attach=1]

(I hope the picture shows)... more to come...

Matt

[MattyMattz]

Okay so from here on in it's open to possibilities.  I'm going to assume that we'll want to use a line graph, as it's the one mostly used around here.  So choose the type of graph you want (line in this case).  Then you'll have several options on the right... just pick on.  I'm going to pick the first option... it's the most basic, and it's all we really need.

[attach=1]

- After selecting the line graph you want, click "Next"  You'll see the following:

[attach=2]

- Click "Next" again... you'll get this:


[attach=3]

More to come...

[MattyMattz]

Okay, so now we need to add some titles to our graph (don't worry about the numbers and layout yet... we'll get to that).
- Fill in the first 3 fields (Chart Title, Category (x) axis, Value (y) axis).  I have filled them out in the example:

[attach=1]

- Click "Next" again.
- Now you have the choice to either insert your chart on your current worksheet, or as a new sheet.  Up to you.  I've selected "As Object in" for this example. 
- Click "Finish" - you should see this...

[attach=2]

- Now I'm sure your looking at the graph and saying... somethings not right.  Your correct, we're still not done.  All we've done so far is entered data into our chart.  Now we need to set the parameters of the X and Y axis (remember, X axis is horizontal, so along the bottom).

In this example I want to show the BR increasing over the course of spins.  So first off we want to remove the Spins Line on the graph. 

- Right click on the graph and then select "Source Data"

[attach=3]

you'll get this:

[attach=4]

- Under "Series" click once on #Spins, then click on Remove.
- Now at the bottom of this window, you'll see "Category (X) axis Label".  This is were we will input our spin numbers to be shown across the bottom of our chart. 
- Click on the little box with a red arrow in it : (see pic...)

[attach=5]

- once you click on it, you'll need to select your spins, so A2 to A10 (don't include the title this time).  After its highlighted, click Enter.
- Now click "OK"

- Voila, a nice little graph to show how much money you made.  Oh, we can make this easier or harder, depending on the table.  Feel free to ask away.

[attach=6]

Cheers,
Matt

[Herb]

Matty is a great programmer.

[Herb]

-