« ProjectProject: Coin Flipping »

Project - Learning Excel

  01/11/19 23:24, by Dylan

For my final project, I have created a representation of my excel knowledge. I have been attending the excel portions of each class throughout the quarter and I have gained 100 times the knowledge that I had before about excel. At the beginning of this class, I didn't even know what excel was or how useful of a tool google sheets can be. I can now confidently write simple functions and macros. To demonstrate what I have learned, I re-did the penny flipping exercise from way at the start of this class. But instead of flipping actual pennies, I made a program that will generate thousands of random numbers in just a few seconds. I made two versions that are essentially the same except that one of them does two steps in one (generating the random numbers and identifying them as either heads or tails). In my sheets, each row of ten random numbers (from 0-1) across the first ten cells is a set of ten penny flips. I made it so that any number above 0.50 is a tail (or "T"). The next column past the random numbers counts how many tail for sheet two and heads for sheet one were in each set of ten "flips." Then the column to the right of that counts how many 0s, 1s, 2s, 3s, 4s, 5s, 6s, 7s, 8s, 9s, and 10s are in the previous column. Finally, I made a bar graph of each sheet to show the results. Sheet one only goes 1001, but sheet two goes all the way to 2000, so sheet two gives a better overall representation of what to expect.

Here is the link: https://docs.google.com/spreadsheets/d/1LtC1Ezur1p6P_405kq-qiUD4ADIfI_kM21nl4iS-GCM/edit?usp=sharing

The third sheet (that is labeled "Pascal's Triangle") contains a macro that I created. I recorded the macro as I made the pascals triangle in the sheet, then saved it. So in the future, whenever I just happen to need a pascals triangle, I have got a macro that I can use to easily put one into a sheet :-P . I had a lot of fun making this and experimenting with it. The reason that it is not the typical shape of Pascal's triangle is because the cells in google sheets are not set up like bricks, but instead are all lined up with each other (and I couldn't figure out how to change this). So each number is the sum of the the number above it and the one above and to the right.

You can access the three different sheets by clicking on them where they are located near the bottom left of the page.

Sorry if this post is a little confusing to understand... I had a lot of fun with this project though!

For those who are interested, I will copy and past my macro here so you can try it out:

function _1() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('T1').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('S2').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('R3').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('Q4').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('P5').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('O6').activate();
spreadsheet.getCurrentCell().setValue('1');
};

function myFunction() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('T1').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('T2').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('U2').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('S3').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('U3').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('T3').activate();
spreadsheet.getCurrentCell().setFormula('=T2+U2');
spreadsheet.getRange('S4').activate();
spreadsheet.getCurrentCell().setFormula('=S3+T3');
spreadsheet.getRange('U4').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('U5').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('U6').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('U7').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('U8').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('U9').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('U10').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('U11').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('U12').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('U13').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('U14').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('U15').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('U16').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('U17').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('U18').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('U19').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('T4').activate();
spreadsheet.getCurrentCell().setFormula('=T3+U3');
spreadsheet.getRange('R4').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('Q5').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('P6').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('R5').activate();
spreadsheet.getCurrentCell().setFormula('=R4+S4');
spreadsheet.getRange('S5').activate();
spreadsheet.getCurrentCell().setFormula('=S4+T4');
spreadsheet.getRange('T5').activate();
spreadsheet.getCurrentCell().setFormula('=T4+U4');
spreadsheet.getRange('Q6').activate();
spreadsheet.getCurrentCell().setFormula('=Q5+R5');
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('Q6:T6'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('O7').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('N8').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('M9').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('L10').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('K11').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('J12').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('I13').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('H14').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('G15').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('E16').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('D17').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('D16:D18').activate();
spreadsheet.setCurrentCell(spreadsheet.getRange('D18'));
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('D16:E16').activate();
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getRange('F16').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('E17').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('D18').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('C19').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('Q6').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('Q6:Q14'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('Q7').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('Q7:T7'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('Q8').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('Q8:T8'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('O8:Q8'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('Q7').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('P7:Q7'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('Q9').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('N9:Q9'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('N9:Q9').activate();
spreadsheet.setCurrentCell(spreadsheet.getRange('Q9'));
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('N9:T9'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('N9:T9').activate();
spreadsheet.setCurrentCell(spreadsheet.getRange('Q9'));
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('N9:T19'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('N19').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('D19:N19'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('N18').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('E18:N18'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('N17').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('F17:N17'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('N16').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('G16:N16'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('N15').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('H15:N15'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('N14').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('I14:N14'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('N13').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('J13:N13'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('N12').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('K12:N12'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('N11').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('L11:N11'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('N10').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('M10:N10'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('B20').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getActiveRangeList().setHorizontalAlignment('left');
spreadsheet.getRange('D19').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('D19:D20'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('D20').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('C20:D20'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('D20:T20'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('A21').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getActiveRangeList().setHorizontalAlignment('left');
spreadsheet.getRange('C20').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('C20:C21'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('C21').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('B21:C21'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('C21:T21'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('U20').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('U21').activate();
spreadsheet.getCurrentCell().setValue('1');
spreadsheet.getRange('U22').activate();
};

14 comments

Comment from: [Member]

Really good!

10/10.

01/12/19 @ 12:07
Comment from: [Member]

10/10

01/12/19 @ 13:03
Comment from: [Member]

10/10

01/13/19 @ 11:20
Comment from: [Member]

Nice work! I give it a 10/10.

01/13/19 @ 12:37
Comment from: [Member]

10/10

01/13/19 @ 13:18
Comment from: [Member]

Crazy! :)

10/10

01/13/19 @ 15:24
Comment from: [Member]

That’s really good!
10/10

01/13/19 @ 20:06
Comment from: [Member]

10/10

01/14/19 @ 10:23
Comment from: [Member]

10/10 it’s very obvious to me you put lots of quality work and go above standards on your projects. Great job !

01/14/19 @ 14:13
Comment from: [Member]

Excellent job, Dylan. Way to apply yourself.
10/10

01/14/19 @ 14:30
Comment from: [Member]

10/10!

01/14/19 @ 14:39
Comment from: [Member]

10/10

01/15/19 @ 22:05
Comment from: [Member]
Cayleigh

Great! 10/10

01/16/19 @ 11:42
Comment from: [Member]

10/10

01/18/19 @ 08:28


Form is loading...

April 2019
Sun Mon Tue Wed Thu Fri Sat
 << <   > >>
  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        

Search

Random photo

Second Guesses

  XML Feeds

Community CMS

©2016 Laurel Charter School