Using ExcelPrev TopicNext Topic

• Mr-B 216

  

  Ohio
  United States
  Member #179,976
  February 15, 2017
  1,283 Posts
  Offline
  Feb 26, 2020, 11:09 am

  ▲▼

  1. Power is Functions, Formulas, and Macros.

  2. Basics are Sum button and/or changing cell formats.

  3. Furmulas and Funtions such as:
  =COUNTIF($A$1:$C$10,E1)
  =MOD(A1+A2,10)
  =IF(AND(A1>3,A1<8),"Yes","No")

  4. Use it all is all the above plus Macros.

  What goes around comes around.
• Mr-B 216

  

  Ohio
  United States
  Member #179,976
  February 15, 2017
  1,283 Posts
  Offline
  Feb 26, 2020, 11:20 am

  ▲▼

  I rarely use Excel, but when I do, I use it all except for the macros.

  What goes around comes around.
• phileight

  

  United States
  Member #41,845
  June 23, 2006
  713 Posts
  Offline
  Mar 1, 2020, 5:23 am

  ▲▼

  macros unleash the real power of excel.  if you can write a complex formula macros are easier to write, edit or adapt
• Mr-B 216

  

  Ohio
  United States
  Member #179,976
  February 15, 2017
  1,283 Posts
  Offline
  Mar 1, 2020, 11:28 am

  ▲▼
  In response to phileight

  Quote: Originally posted by phileight on Mar 1, 2020

  macros unleash the real power of excel.  if you can write a complex formula macros are easier to write, edit or adapt

  I somewhat agree, but for me, I get better performance writing code and creating standalone programs. I find it faster both in terms of creation and performance.

  What goes around comes around.
• AllenB

  

  California
  United States
  Member #167,122
  June 27, 2015
  1,635 Posts
  Offline
  Mar 9, 2020, 3:16 pm

  ▲▼

  It all starts with the number of times an Option is Drawn and the Number of Options from which it is Drawn.  That History is a listing of all Options drawn in each Draw in Each Game.  That is a Grid and perfect for a Spread Sheet. 

  Excel is a Spread Sheet Program.  Maintaining an updated Historical Grid serves as a platform from which to look back.  The look back increases the number of columns in the grid.  Excel has a pretty good set of functions to analyze and group the data.  Excel maintains Statistical Data. 

  One of the more powerful Functions in Excel is the Indirect Function.  This function allows for its Address Range to be based on a formula. 

  Macros for me are a repetitions Filter Settings “On”, “Off”,  ”Option Values”=>Filter Sequences.  After setting the Games Number, the Macro runs and logs another Grid of data on the performance of the Filter Sequence over the specified number of games. 

  This is Basic Stuff really.  Maybe a bit eccentric from a “Break Down” perspective with a One Trick Pony that says Next and proceeds to log the result. 

  Excel is Giant Grid.  Finding What If in that Grid is What it does.  Macros, at least my Steppers,  usually come into play when the number of columns in the history that are needed to generate a Data Idea is too large to fit in the Grid and or takes too long to calculate in cell formulas.  Not to mention unstable. 

  Regarding Macros, 

  Can anyone tell me or share what a macro should do beyond my baby steps. 

  What Grid are you looking at beyond the numbers themselves?  What Column of numbers are you focused on?  Where does the grid come from?  If the macro sets up the basic grid formulas why? And what’s the difference?  Every time we open up our file, it recalculates all of the formulas.  My Step Macros record the Values of the formulas in the game History to a summarized a set of fixed Values.  Next is the How?  Excel can calculate a large number of running formulas in a pretty large grid.  Cell Formulas can be made to respond to a change in a filter Value in another cell and be analyzed historically when the grid recalculates.  You don’t need a Macro for any of it. 

  I guess I like to set things in piles right there on my screen.  Then I like to step back and watch the piles move when I change a Value somewhere that generates a web of If Then statements dispersed thru out a grid.  A Macro just lets me stop typing or clicking the mouse while if creates a fixed value log of a summary of the data Block when a Filter Value was applied.  What else can a Macro really do? 

  I would really like to know.
• Greenfox

  

  United States
  Member #107,239
  March 4, 2011
  1,192 Posts
  Offline
  Mar 21, 2020, 2:24 pm

  ▲▼

  I use all of it. In steps. I build what I need with functions and formulas so I know what it's doing and why. 10 books with 10 different purposes. A main book as a control to compile everything and give me all my data in one spot and 31 days worth of sheets to give me a months worth of pick and play. 9 other books that feed from then back to the control book. Those 9 books track and let me know what is going on and what to look for only. I use as much basic formulas as possible in all of it to help with calculation time and accuracy. I think accuracy is more important than time. Some basics just won't dig down in there like I need it to in some points so you have to get into the more powerful functions. The 10 books to use is about a gig's worth of data and all opened at once calculates a draw change in, what seems like forever, but is actually less than a minute. Simplicity is so much easier to use and that makes it easier to push with less power. Adding a more powerful function on something that doesn't need it can cause problems. Kind of like picking up a vehicle that fell on someone. Just need to get it up enough to get them out from under it. No need in flipping the whole thing over and destroying it also.

  The only macros used is for testing. None is used to run the deal. Once a draw is put into the mix, I know how many hits/wins/options/percentage or whatever,  for the entire month and the game at hand. I use the macros to run through each month for every draw to compile all in one spot for whatever I'm testing, so I know what needs to be done in that area. I make a change physically, make a macro and run it through and see how that affected it. Running through what happened by hand just takes so long where the macro will run in it seconds. If testing full history with the changes without a macro, good luck. You're going to be a while. I'd be behind 5 years without the macros just in time and the ideas would have already left me. I couldn't do this without excel or programming and I don't know programming other than excel. I wish I did, but seeing it in excel has really helped me understand how it works more than I would if I had programmed it. Although I know programmed in a language would make it so much smoother and faster.

  Knowing percentages made a world of difference also. Working this with pen and paper, having to figure out percentages, oh, my. I'd be 10 years behind where I am.

  Only problem I've seen with macros is using them in a .xlsm format. They have corrupted or lost library on me so many times I can't count. I found out that switching them to the .xlsb format has not gave me any problems. Especially with such large files. Even with just formulas and function ran books that are large, that format has been much much more stable. It also makes the file a little smaller in size. The macros have been like the invention of the pulley for me with what I've been doing. Other than back testing and forward testing, they aren't used by me, but have been crucial in that process.

  You can't steal second and keep your foot on FIRST!!!

  “Strength does not come from winning. Your struggles develop your strengths.
  When you go through hardships and decide not to surrender, that is strength”.

  -Arnold (Ahnald) Schwarzenegger-
• db101

  

  United States
  Member #202,920
  December 14, 2019
  900 Posts
  Offline
  Mar 22, 2020, 6:13 pm

  ▲▼
  In response to Greenfox

  Quote: Originally posted by Greenfox on Mar 21, 2020

  I use all of it. In steps. I build what I need with functions and formulas so I know what it's doing and why. 10 books with 10 different purposes. A main book as a control to compile everything and give me all my data in one spot and 31 days worth of sheets to give me a months worth of pick and play. 9 other books that feed from then back to the control book. Those 9 books track and let me know what is going on and what to look for only. I use as much basic formulas as possible in all of it to help with calculation time and accuracy. I think accuracy is more important than time. Some basics just won't dig down in there like I need it to in some points so you have to get into the more powerful functions. The 10 books to use is about a gig's worth of data and all opened at once calculates a draw change in, what seems like forever, but is actually less than a minute. Simplicity is so much easier to use and that makes it easier to push with less power. Adding a more powerful function on something that doesn't need it can cause problems. Kind of like picking up a vehicle that fell on someone. Just need to get it up enough to get them out from under it. No need in flipping the whole thing over and destroying it also.

  The only macros used is for testing. None is used to run the deal. Once a draw is put into the mix, I know how many hits/wins/options/percentage or whatever,  for the entire month and the game at hand. I use the macros to run through each month for every draw to compile all in one spot for whatever I'm testing, so I know what needs to be done in that area. I make a change physically, make a macro and run it through and see how that affected it. Running through what happened by hand just takes so long where the macro will run in it seconds. If testing full history with the changes without a macro, good luck. You're going to be a while. I'd be behind 5 years without the macros just in time and the ideas would have already left me. I couldn't do this without excel or programming and I don't know programming other than excel. I wish I did, but seeing it in excel has really helped me understand how it works more than I would if I had programmed it. Although I know programmed in a language would make it so much smoother and faster.

  Knowing percentages made a world of difference also. Working this with pen and paper, having to figure out percentages, oh, my. I'd be 10 years behind where I am.

  Only problem I've seen with macros is using them in a .xlsm format. They have corrupted or lost library on me so many times I can't count. I found out that switching them to the .xlsb format has not gave me any problems. Especially with such large files. Even with just formulas and function ran books that are large, that format has been much much more stable. It also makes the file a little smaller in size. The macros have been like the invention of the pulley for me with what I've been doing. Other than back testing and forward testing, they aren't used by me, but have been crucial in that process.

  What I do with a macro-enabled Excel sheet is copy-paste the source code of the macro into a txt document and save it in the same folder as the Excel sheet, and save it with the same name as the Excel sheet, but with "_macro1," "_macro2," etc. appended to the end. That way if the sheets don't save properly with the macro, I still have the code I can insert on the fly. I have not really had this problem since I upgraded to the latest version of Excel, though.
• Greenfox

  

  United States
  Member #107,239
  March 4, 2011
  1,192 Posts
  Offline
  Mar 23, 2020, 12:20 pm

  ▲▼
  In response to db101

  Quote: Originally posted by db101 on Mar 22, 2020

  What I do with a macro-enabled Excel sheet is copy-paste the source code of the macro into a txt document and save it in the same folder as the Excel sheet, and save it with the same name as the Excel sheet, but with "_macro1," "_macro2," etc. appended to the end. That way if the sheets don't save properly with the macro, I still have the code I can insert on the fly. I have not really had this problem since I upgraded to the latest version of Excel, though.

  Yes. I do that same thing. It's a good tip to put out there for sure. I really liked some of the new stuff from the new version from the new version, but it seemed to go into the not responding mode more on me. I had a lot of trouble with Windows 10 and the new version of Excel for some reason. Finally got 10 going right, but had to revert back to 2016. It's actually ran a lot faster and the not responding has cut way down. I guess machines play a difference with a lot of it though.

  I also keep backups of backups with both Excel files and the txt docs. That way I can keep the code original, then have others to edit on.

  You can't steal second and keep your foot on FIRST!!!

  “Strength does not come from winning. Your struggles develop your strengths.
  When you go through hardships and decide not to surrender, that is strength”.

  -Arnold (Ahnald) Schwarzenegger-