building a spreadsheet to track instructors' classes' test scores. I have 3 basic variables: 1.) The instructor's name 2.) The course taught, and 3.) The classes' scores for that particular course. The 3 courses each have 3 different variations of the end-of-course test (9- total tests.) On the last page of the excel spreadsheet, I have a block for each of the instructors. I've been trying (with no success) to keep a running average of each instructor's class test average for the 3 courses. The part that is stumping me is associating the instructor's name with their class average, and then averaging all of their grades on that page together; the main reason being that this document is living, and new grades are entered almost daily. I tried using variations of the count function with no luck, other than to tell me how many times a particular instructor has tested in a particular test. Please help, I have the feeling that the function I am looking for is way-above my skill-level in Excel.
I can't figure out a formula that will match either 1,2,or0 in column K and offset to corrosponding value in column B. Then average of all values that came up.
how to accomplish this. VBA is acceptable, but formula is prefered.
In the attached spreadsheet, I want to do a running average of the "To" column (column G) for each company throughout the 8 year period. The only problem is I want to do it with replacement based on column D, meaning if the same brokerage firm comes out with a new announcement, it will replace the value of the old one in the running average calculation.
Here is an illustration of what I mean:
Looking at Company A between row 2 and 12, the value for Consensus in row 2 should be the average of Column G in row 2, row 3 is the average of Column G in row 3...row 11 is the average of Column G in row 2 to row 11, but row 12 will be the average of Column G in row 2 to 12 be excluding column 5's value because both column 5 and 12 are issued by the same brokerage.
I want excel to replace new recommendations by brokerages over the old ones in the running average calculation.
I wonder if it is possible to formulate a formula in which average values are recalculated after every added record.
For example: Record 1 shows an amount of 200 so the average value after 1 record is (200/1) 200. Record 2 shows an amount of 250 so the average value after 2 records is ((200+250)/2) 225. Record 3 shows an amount of 210 so the average value after 3 records is ((200+250+210)/3) 220.
I don't want to calculate all averages manually and most probably there must be a formula that is able to recalculate the average value after another record is added to the file.
I would like to keep an average for 12 months.Each number entered for a month would be averaged with the total months with an entry until all 12 months had entries. I am using excel 2007
I need to do the following and can't figure it out. How do I create a running average that will only calculate the averages in % each month. Example: Opt 1 for Jan, Feb, Mar =1 each= 3 total = 100%; OPt 2 for Jan, Feb, Mar =1,0,1= 2 = 66%; Opt 3 for Jan, Feb, Mar = 0, 0, 1 = 1 total = 33%. My problems is I want monthly running average that shows the yearly percentage up to date but only for the months there is a value 1 or 0. How can this be done because the way I have it now, those % are being divided by 12 and that isn't the correct %
I have sales numbers which is in the form of a running total dollar amount and I would like to keep track of the average increase in dollars.
So basiclly if the Jan total is $100 in column a, the Feb total is $150 in column b, and the March total is $200 in column c. I would like a formula that will tell me that the average increase in dollars is $50.
I've got data being scraped from a site, putting 1 new workbook in a folder each day
each workbook has 40 sheets in it.
i need to run 5 modules in sequence on a sheet then loop to the next sheet and run the same 5 modules.
ive writen all the modules, and can loop them through the sheets in sequence but i cant work out how to loop them through the each workbook in the folder..
is there an easy way to do this or can it not be done because it would need access to the folder that holds all the wordbooks which lives outside of excel on the desktop ?
Is there a formula that would allow you to take the average of all values within a range but not count the zero values? I thought something like this might work but it's not. Neither one worked.
I am trying to run multiple applications. I can run one application i.e.
Application.Run (Sheets("Sheet2").Range("A8").Value) but not multiple i.e Application.Run (Sheets("Sheet2").Range("A8").Value) & Application.Run (Sheets("Sheet2").Range("A9").Value) at the same time.
I have also tried Application.Run (Sheets("Sheet2").Range("A8:A9").Value) but to no avail.
I have some code which I want to run on all apart from one sheet called 'List'. At the moment I am selecting each sheet before running code for the specified sheet. This doesn't allow for any sheet name changes etc. or additional sheets. Is there a quicker way of doing this rather than having a sub to manually select each sheet?
Here is an example of my code for a button on the sheet called 'List'. The only thing Sheet1, Sheet2 does in this code is select that sheet.
What I would like to do is for each worksheet run "descriptive statistics" in data analysis on the data in column Q, which varies in length, and then copy the "mean, median, SD, and skewness" in the results down columns T:W to the last row based off of a count in Column Q. Finally, calculate the "zscore" in column S based off of the formula in cell S2 in the attached workbook.
I am trying to run the same test on multiple cells, to get the column letters of cells while there are less than 78 columns in use (these column letters will later be used for the Range().Select work involving copy & paste-ing into a word document). I originally thought I could use GoTo statements in conjunction with variables but, having researched it and coming across this, I think I need to find another approach.
At present I have a long section of code that looks like this (I am writing and testing at present so what i make will be put into a larger macro):
VB: Sub FindColumnLetters() Dim i01Feb As String, i02March As String, i03April As String Dim NumbTemp As Integer, NumbTemp2 As Integer
[Code]....
However VBA didn't like the use of variables with the GoTo function and there are not decent ways around. how I can do the original but in less space? FYI the codes above are repeated for 3 columns, where I am actually doing this for ~20 column refs so the space it takes is HUGE at present.
I have the following code, I have it running to just work on Sheet 2 but I would like it to be able to run on mulitple sheets, not all but a range like from Sheet2:Sheet30.
I am running a spreadsheet to keep track of workshop technician times. The code I am using I have copied and modified from this forum works really well but I need the timer to go in minus time instead of stopping at 00:00:00. It work be really great if the timer cell changed colour it identify that the Technician had run over the allotted time.
I am seeking code that will go to Sheet 4 (named "Extract"), copy a1:a5 and return to the active cell the macro was launched from and copy the values in.
The challenge I am having is how to get the code to return back to the cell it was launched from since I want the flexibility to use this macro in multiple tabs/sheets to copy the info into ANY selected cell within ANY sheet. As it is now, I have to go change the sheet name each time for the then active sheet.
I've attempted to insert a generic worksheet reference, however I end up with a debug issue.
I would like to have a running percentage calculated in cell P3 for the attached file. Cells C3, E3, G3, I3, K3, M3 show the number of correct picks. Cells D3, F3, H3, J3, L3, N3 multiply the number of correct picks by a given value in row 1. For each round there are a possible 32 points. I would like to use some sort of IF Statement to have a running winning percentage calculated for each round where points are achieved.
Example: Round 1 - #picks correct / 32 possible points Round 2 - # picks correct from Round 1 + # picks correct Round 2 / 64 possible points Round 3 - so on until round 6 with total picks correct overall / 192 possible points
Have list of employees and their dependents organized into the following columns: Employee ID (unique to employee), Relationship Code (E=employee, S=Spouse, D=child), andBirthDate. Would like to add a column called Sequence Code which counts the family members (employee= 0, S=1, oldest D=2, next oldest D = 3, and so on).
Employee ID RelationshipCode BirthDate Sequence Code 100006641 E 2/5/75 0 100006641 S 1/2/75 1 100006641 D 3/9/89 2 100005555 E 2/5/77 0 100005555 D 1/2/89 2 100005555 D 3/9/88 1
Note that the list is sorted by Employee ID, but not Birthdate. Can this be done with a formula in the Sequence Code column? I really suck at VBA
how to run my Macro over multiple (about 60) .xlsx files. I have been looking around, but haven't quite got anything working yet.
I have all the files in the same folder with similar names (i.e. Subject_01.xlsx, Subject_02.xlsx, etc.).
What I'd like is:
*open Subject_01 *run script (some calculations, copy/pasting, my script for this is working fine) *save file under a different name (i.e. Subject_01_processed.xlsx) *close file *again for Subject_02, Subject_03, etc.
Additionally, I have a second question: after I have the processed .xlsx files, I'd like to copy some cells from each .xlsx file (e.g. A1-H1) to a new file. There, I'd like the data from Subject_01 to for example be in cells A1-H1, from Subject_02 in cells A2-H2, etc.
I need to run a very simple macro (filter by date, copy, and paste) on every worksheet in a workbook, and then place that data into a summary page at the "end" of the workbook. Most other posts had to do with some kind of formatting that would work in succession on the sheets linearly from left to right (for lack of a better way to put it). My problem, however, is that the data would need to be pasted into the summary worksheet, and then somehow the macro would come back to the next sheet (after the one it just did) and do it all over again, so on and so forth. The worksheets are arranged alphabetically, but other than that there is no sort of easily identifiable progression between them (i.e. Sheet1, Sheet 2, etc.).
I have 3 seperate macros (macro1, macro2, macro3) that have to be run in order. Is it possible to create a macro to run them in order and what would the code look like? I will have it assigned to a button on the worksheet.
I would like my macro to go to a certain folder- in this case N:Aexeo ClientsJabre2008Excel Diet Run, open each workbook therein ( to this end I have found the first part of the code below on another thread) and perform the ExcelDietMacro (also below). I am missing something though as nothing is happening, would anyone know from a quick glance what is wrong? Should I have these as 2 seperate Sub End Subs or combined into one? I know that the Excel Diet is correct thanks to the original designer and Rory on the forum for getting it adjusted to my needs.
Also each workbook within the folder will have the same password to open, is it possible to insert some code in the macro to do this automatically?
Sub LoopFiles() Dim MyFileName, MyPath As String Dim MyBook As Workbook MyPath = "N:Aexeo ClientsJabre2008Excel Diet Run" MyFileName = Dir(MyPath & "*.xls") Do Until MyFileName = "" Workbooks.Open MyPath & MyFileName Set MyBook = ActiveWorkbook Application.Run "ExcelDietMacro" MyBook.Save MyBook.Close MyFileName = Dir Loop End Sub
Sub ExcelDietMacro() ' ' ExcelDietMacro Macro.........................
I am trying to find a way to set up a macro that will allow me to pull in data - create the Pivot table - delete the table - then pull in fresh data (of a different row length - same number of columns) and create another Pivot table. I have tried to manipulate recorded code to no avail. Here is what I am starting with:
Const lngLastPossRow As Long = 65536 Range(Cells(1, 1), Cells(Cells(lngLastPossRow, 1).End(xlUp).Row, 24)).Name = "Data"
While it creates the first pivot table fine, after deleting it and starting again it wants to create the next sequence (PivotTable3) which crashes the macro. I must close the file and reopen to run it again.
I have a list of values on my spreadsheet in a similiar order to this:
17.91 16.59 15.00 14.86 13.56 12.17 11.01 0.0
I use the average function to work out an average for all the values, but since the value of 0.0 is showing i should not include this as an average. the range of values above (8) will always be the same for the month - so i need to find a way of omitting the 0 value without keep changing the formula
I have a data table with all my data in it. I want to be able to filter the data, and have a column automatically calculate a running (or aggregate) sum of the filtered (visible) information in a particular column of the table.
If you glance at the attached sample spreadsheet (in 2nd post!), you will quickly see what I mean. I am searching for a formula for column L that will caculate a running sum for column K. In column T, you can see the results that I'm looking to reproduce in column L. Each cell in column T simply adds the K column value for that row to the previous value in column T (the cell above). However -- to my knowledge, and as in this case -- the formula for column T can only be copied down the column AFTER the table has been filtered.
1- Open enclosed file. 2- Run Macro1. 3- Wait some time completing running Macro1. 4- Run Macro2. 5- Wait some time completing running Macro2. 6- Look MessageBox to see that what is Macro2 running time. (Note: My Macro2 running time is 06 minutes and 49 seconds)
My question is how can I decrease Macro2 running time ?
I am open all ideas like copy data to NotePad and paste again excel etc.
