5. Summary Statistics in Excel VBA (Visual Basic for Applications)
VBA PLAYLIST: • Learn Excel VBA (Visual Basic for App...
This fifth clip in this VBA video series shows how to leverage summary statistics in Visual Basic for Applications (VBA) in Microsoft Excel. Learn how to write to multiple Microsoft Excel sheets using functions such as Sum, Variance, and Average.
hello guys my name is car and actually in this video I will show you guys that how you can use Visual Basic to create some summary statistics so before going to start actually I will be using this workbook and actually this book will have three sheets that I have created for you guys so actually the sheet one have some data in it and some variables for which we have data so it's a hypothetical data what what is that based on this data I want some summary statistic so I've created a template for you guys that I really like to create through Visual Basic so we have these so I want some statuses for three these three variables price mpg and rep 78 so actually this variables are related to car data as I said this that is hypothetical so you don't have to be worried that what is this price and mpg so I can go back to my sheet one which have the data I also have this variable price mpg and rep 7 here and for these variables I want to have some prices variance in prices and average of prices so what I will be doing I will be using a excellent building function of some variance and average in Visual Basic and then I will be assigning the final output to each cell but I will be creating exactly this same table in sheet to actually this should have just created to show you guy that what I am going to do so the main thing that I will be doing through VBA will be exactly the same thing but in sheet 2 and more this video I will also talk about some other functions very useful function that you will be able to use in Visual Basic to improve your coding in Libya so now whatever we do have an take back to visual basic so we I am already in the Developer tab and then I will go to listen so after that I will be creating my module so every same module so now I think whatever do I will create but I will try to make some of prices and then then I will try to assign that computation or that value to a cell so I will go back my excel sheet and sheet once so what I want to do I want to create the sum of these prices so as you can see the prices is starting from row number two and it going up to row number 75 after that I have black data so I will go to my reveal so I will be creating a macro for this so I will be saying sup and this macro I will be calling it you can call it anything you like I'm calling it test and then I will be using I will be the signing I will be saying that cells and the cell will be the first row and for the column index that I want to select is M you can I'm just arbitrarily selecting this cell so that I will be able to assign the computation value so the first thing that I want to do is that I want to if I go to VBA so he thing go to excel so what I'm trying to say to envy be that go to first row and then select this cell and and then I'm saying it I'm trying to say that do the sum of all these prices in my data so if you guys remember that if you want to do it in Excel what you guys will do I will say equal to sum and then in bracket and then you will select all the all the all third rows for that column and then you wrap it close and then enter and then you can see this is the sound that we'll be showing this n is showing the sum of prices for this data but now I want to do the same thing to visual basic so you will be thinking that why not I will be just use this formula and copy and then paste it here
paste it here so actually you can do the same thing but make sure that when you copy of formula from Excel put it in inverted commas in what it calls and now if I play this macro if we go back you can see now I have the same sum of price in m1 and this is the n1 that I have just created for you guys to show what I'm trying to do ya like to discuss one thing that actually I'm having three sheets in in Excel in this workbook then how we have decided to select the cell which is in the sheet 1 why Excel haven't selected this m1 which is in sheet 2 or in the result tab m1 but m1 means the column m and Row 1 so why actually selecting the cell sheet in sheet 1 actually we when you are not mentioning about any sheet then VBA will automatically decide that it will be a sheet 1 so it kind of a sheet 1 is a kind of a default sheet so but if you want to say that I don't want to put this F no more to paste my output result in sheet 1 then you can also select that in which sheet you want to