I have a spreadsheet,3 columns are shown in the attached.The first column gives the date of the event,the second column the time and the third a rating.I want to put the average rating for each event in each cell in column 4.
View 9 RepliesI have a UserForm with a Text Box, I populate that Text Box with a number (say 5) and then the following code runs:
Code:
Private Sub tbOverrideMokWh_Change()
Application.EnableEvents = False: Application.ScreenUpdating = False
With tbOverrideMokWh
[Code]....
After the Sub is run 1 time, it runs again. Why? I've disabled Events?
I am aware that I can use single changing events in worksheet change events. For instance, if column 1, or A is changed, do something. This is only a single If statement, i.e. either the condition is true, or not. What I am not sure is if I can use two changing events, i.e. two conditions. For e.g. I would like if Column A value is X and Column B is "Active", action it, but only if two conditions are true.
For.e,g. The below syntax does not work. If it is only column A, it does work, but I want both A and B to be true, then copy and paste the target does not anything.
VB:
If Target.Column = 1 Then
If Target.Column = 2 Then
If Not Intersect(Target, Range("A2:A" & Rows.Count)) Is Nothing Then
If Not Intersect(Target, Range("B2:B" & Rows.Count)) Is Nothing Then
If Target.Value = "X" And Target.Value = "Active" Then
How can I do an average in Excel 2003 when it's not a range but I also don't want to count a certain cell when there's no data or zero? I have attached the spreadsheet.
View 6 Replies View Relatedsome times i choice (Initialize , active, open, click
Workbook_BeforeSave)
and so on
tell me as the differences and usage of these strings in order to improve our(my) programming skills.
I'm trying to average data for the past 3 years. My spreadsheet is setup like this.
Year, NameID, Salary, Average Salary
I have the spreadsheet sorted by NameID so most people in the database will have 3 entries right after another. How do I determine an average salary for the person across all 3 years?
I need the average salary to display across from the most recent date if that is possible as well i.e. if the latest salary date is from 2006, I would prefer that is entered into the row corresponding to 2006 and leave the 2004/2005 rows blank.
Also, some people might only have 2 years of data so keep that in mind as well.
Column A has approximately 50,000 rows with unique property parcel numbers. Column B has numbers that represent an elevation point on the parcel. The parcels are not level. The elevation of a parcel varies depending where one measures. So,...I have many rows with the same parcel number in column A but the adjacent column B shows a different elevation. Is there any way to:
1) remove all of the duplicate column A parcel number rows so I have just one column A parcel number row.
2) get the average of all the column B elevations to show up on the same one row?
I should only have approximately 15,000 rows if I could get rid of the duplicates and get the average elevation. I have version 2007 but I have to share with others who have version 2003. I don't know if that makes a difference but I thought I should mention it
I have received a task to do for school and I tried my heart out but I can't seem to figure this out. It's really simple but I can't seem to do it... I'm not very good with excel.
The excel file is attached .....
I have a spread sheet and it has a column in it with a drop down menu. The words in the menu are Hot, Medium and Cold. I want to have it tell me which one if showing up the most.
View 13 Replies View RelatedThe organisation I wish to report on has many cost centres which each contain many people. These people are on many different grades, and each are on differing salaries (even those in the same grade).
One “reporting group” has many cost centres.
There are several reporting groups.
I need to report on average salary per grade / per reporting group.
I have attached some dummy data. The “rep group” tab displays the reporting groups and the cost centre mappings (ie reporting group England contains cost centres 1, 2, 3, 4, 5, 6 and 7). Note in reality the cost centres are not this simple, they are 6 digits and varying ranges.
In the “salaries” sheet each individual is listed along with their cost centre (in column c) and their grade (column d). Their salary is shown in E.
I have a column of data in Excel in the [h].mm.ss format. These values range from a few minutes to hundreds of hours. I need to get the average of these times.
My code is below. I'm getting a type mismatch error on the line that tries to add the time to the total. The line is in red. I've tried using a Integer, Long, Double, and Date as variable types for this particular variable. All with the same result.
I am attempting to find the average units of the last "n" weeks. I wish to input the required number of weeks in cell D20 which will show the average in cell D22. In this particular example, I have manually calculated using the average formula....
View 9 Replies View RelatedI need some help with a formula. I've tried daverage, cant seem to get it to work. below is an example of the spreadsheet.
A B C D E
Date Name Project Score Pass?
5/1 Joe Test 55% No
5/1 Jan Quiz 88% Yes
5/4 Jon Test 100% Yes
5/9 Gary Test 75% Yes
5/11 Joe Quiz 90% Yes
5/18 Mary Test 45% No
Ok, I need a formula that I can execute from a different worksheet than the data is on. I need the formula to give me the average of the scores in column D for the criteria in Column C "Test". I tried daverage=(A1:E7, "Project", C1:C2) I cant get it to work. Do you guys know any other way to do this?
in Sheet1, i have 'part number' listed in column A and 'customer number' in column H. in column P, i have 'customer price' (for that part). so there are multiples of the same part numbers listed in column A.
in Sheet2, i have a summary showing totals for the parts (regardless of the customer), which includes SUMIFs for other columns such as pieces sold, etc. what i am looking to do is get the 'customer price' average by part (in column P/worksheet1) for each part on Sheet2.
Sheet1 sample:
Column A / Column H / Column P
31397 / 1001240 / $6.60
31397 / 1020312 / $6.65
31397 / 40020 / $6.63
31832 / 1047493 / $4.22
31832 / 1035195 / $4.22
31832 / 40017 / $4.40
So for Sheet2 i would like it to be like:
Column A / Column B
31397 / $6.6267
31832 / $4.28
i also need this formula to work if column P has any cells with zero entered.
I'm making up a list of average scores across a season. I have an average column and I need to be able to deduct 10% from this average, then have excel ignore any scores below this new number and make up a new average (kinda like a handicap score).
I have columns for average (BD), 10% of this (BE) "ignore any score lower than"(BF) but Im not sure how to get excel to add up only the scores below the number in BF and make a new average column of only these in BG -
Last column would be to give each person a classification based on cutoffs (for instance 1-10=C Class, 11-20=B Class, 21-30=A Class etc etc). Is there a way to make "if BF=xxxx, make BH A, B, C etc?
I need to average out a set of numbers but the amount of numbers inputed will vary, these number can also be either positive numbers or negative numbers but will never be 0.
How do i do this and I would like this in formula terms?
Say cells a3,b3,c3 were the numbers that I want averaged but there is not always a number inputted in each of these cells. I want the answer to show up in cell a4
i've got a worksheet with 3 columns each declared, called date, place and avspeed
I'm trying to average the avspeed if it falls under certain criteria within the other 2 columns.
The dates are in day format and basically I require the average over the week
If tried using
=AVERAGE(IF(WEEKNUM(date)=45,IF(local=I4,avspeed)))
also declaring it as an array formula but both
also tried the following by summing and then counting the data and dividing one by the other, but so far neither are working
Trying to compute annual change I've always used the formula "(b1-a1)/a1" - where b1 is the current year, a1 is the prior year. I've been asked to compute average annual change over a number of years and thought simply by using the above for each year and then taking the average of all these, I'd be ok. I recently found a formula as follows: "(y2/y1)^(1/n)-1", where y2 is current year, y1 is 1st year I have data, n = the number of changes.
View 7 Replies View RelatedI am trying to write a code where user has to respond to input box option depending on yes or no selection. There are two types of responses to different questions. One needs to respond between 1 and 100 (if yes) and for some other questions needs between 1 and 4 (if yes). A "no" will enter zeo value. But it is not working. I am using two named ranges "VALIDCELLS" (for 1 to 100) and "FREQVALIDCELLSS" (for 1 to 4). Here is the code;
View 6 Replies View RelatedI have early made a macro that will change some of the cells before printing and then changing back again to initional state after the print out.
http://www.excelforum.com/excel-misc...-printing.html
But now I have to do let people also print a copy a regular way, without the macro I made before. But if they go through the regular way iof printing I would like to insert at header in red color, to notify them that this is just a preview and not the way to print. And here we come to the problem ....
why am I unable to create events on my worksheets? They were usually available to me when I selected "Worksheet" on the dropdown in vba.
When I open Excel, only "General" comes up in the drop down menu. What am I missing?
I've created an addin to reformat spreadsheets that I receive in a particular format. What I would like to do is enable events so that whenever a spreadsheet is opened the reformatting procedure is run (this also validates whether the spreadsheet is of the correct format).
I have created a class module with the following code (exactly as the Excel help):
Public WithEvents App As Application
Dim X As New EventClassModule
Sub InitializeApp()
Set X.App = Application
End Sub
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
SortE1Output 'This is my procedure that determines whether the
'spreadsheet is of the correct format and then reformats it
End Sub
"After you run the InitializeApp procedure, the App object in the class module points to the Microsoft Excel Application object, and the event procedures in the class module will run when the events occur."
When I try to make a call to the InitializeApp procedure in the auto_open procedure (in a different non-class module) I get a "compile error: sub or function not defined".
I would like to find the average of a column of data where the rows of data changes where my starting cell is always B53 but the end row of data can change after each simulation. The average function to be displayed at the bottom of the last row of data.
View 4 Replies View RelatedTimeForce
00.2
0.50.5
11
1.52
24
2.54
34
3.54
44
4.54
54
5.54
64
6.51
70.3
for example i have this data I want to average range of some values in force column for which i only know range of values in TIME column..
For Example I want to average the values for which the values in time column are 0 to 1.5 ((0.2+.5+1+2)/4)
I deduct a formula but it is giving error AVERAGE(LOOKUP(0,A:A,B:B):LOOKUP(1.5,A:A,B:B))
I have a column with times displayed in this format "0 day 03 hour 32min"
Is there a formula that allows me to average a column of these times and retain the same format?
I am looking for the correct way to average a group of numbers.
I know the formula but it doeskin work correct.
I am trying to average this numbers below.
Now when I do this I use this formula
=AVERAGE(G1:G17)
and get this result
-33.079
Now the only problem is, with baseball lines +100 and -100 are both the same and there is never and two or one digit lines like
-4
or -88
its always at min -101 or +101
127
163
146
-119
-124
-139
119
-133
-113
-137
-130
-142
-101
-104
114
111
-111
I read this thread which helped me very much. So I was able to link one drop down box to a list, which fills in the adjacent box. Now I need some help getting an average.
Ex. Drop down 1 has a list of names which applies the associated numerical value in box 3. Drop down 2 has a list of the same names with the same associated values, and I need these 2 values averaged and put in box 3.
The problem is that Drop down 2 doesn't always have a name. It may be left blank and so I need drop down 1 to continue applying to box 3 when drop down 2 is empty.
This is what I have for drop down 1 to apply to box 3.
=IF(C2="","",VLOOKUP(C2,Sheet2!A3:B18,2,FALSE))
One of our govt. agencies requires that I compute the 24-month rolling averages of the daily average input to a manufacturing process. In this instance, the agency is demanding the rolling average since 2003, so there are a fair number of data points involved. The process control computer can provide me the daily data (attached file contains 30 months of data), but cannot compute the 24-month rolling average.
View 4 Replies View Relatedi have this table:..............
column one containing ISO3 names of different countries. I was wondering if it's possible to have a macro that when i choosing some countries in different rows, it compute average value of them for any year in seven columns
I have a row of cells containing numbers, some of which are 0, how can i get an average where it averages all cells except the 0, as at the moment it is distorting my results.
View 3 Replies View Related