Using Average Function - Cell Location Of Last Number In Column
Feb 6, 2014
I have a formula in Cell A1. It tells me the cell location of the last number in column E. So, for example, I input the number 44 in cell e85, the value for cell A1 becomes $E$85.
Let's say I now put the value 33 in cell e86, now the value for A1 becomes $E$86.
I want to calculate the average of the values in column E, all the way from E1 to whatever is in A1.
Here's what I need: =AVERAGE(E1:<whatever is in A1, like $E$85>)
How do I write the AVERAGE formula to make this work?
View 6 Replies
ADVERTISEMENT
Jul 17, 2008
I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place.
Is someone aware of a function which will target a relative location instead of an exact cell?
View 13 Replies
View Related
Feb 5, 2014
I am working on an excel master marksheet which will be distributed to many teachers to fill in their students marks. How can I enter an average function that can work out the mean for those different classes. For example I'd like the main average function to be like this =average(d5:d50) but if a teacher has thirty students in his list the mean will be only limited to the marks of those thirty students. Same thing I want for the mode function and the median function.
View 7 Replies
View Related
Feb 14, 2014
This time I have a situation like this:
Column A - Dates
Column B - Equipment Type
Column C - Load Start Time
Column D - Load End Time
I need to do some statistical analysis (average, mode, medium, st dev) on the load times, which is load start time - load end time. As far as I know, I can't subtract ranges (column d i column c) within Averageifs function. I assume I would need to use an array function for this.
View 11 Replies
View Related
Jan 19, 2013
I'm stuck on the final piece of my macro project. I've attached a workbook with two sheets: sheet 1 is what I currently have, and sheet 2 is what I'd like my report to look like when complete.
On Sheet 1:
- column B is called Supervisor Name
- Columns D-O are months of the year, with either a Yes or No in each cell.
I'd like to write a macro that will:
- Take all unique values in Supervisor Name column, and paste these Above the current table.
- For each Supervisor, and each month, I'd like it to calculate, as a %, the number of Yes mentions in each month divided by the total cells (Yes/(Yes+No)).
Sheet 2 contains the output, in the format I'd like to see it.
Sheet 2:
Supervisor Name
October
November
December
Supervisor 1
8.3%
[Code] .......
View 7 Replies
View Related
Dec 5, 2011
I have a column of numbers that are derived with a formula. I need to Average only the ones that either have a Positive or Negative number, ignoring blanks or zero.
I have tried Search but couldn't find anything that address both blank and zero.
Sheet2
K610.00%624.76%632.53%6418.75%65666.38%6768-4.00%6970-5.84%710.86%
Excel tables to the web : [URL] .......
With this small sample, the answer should be 3.35% according to Excel when I choose just those neg and pos cells.
View 9 Replies
View Related
Oct 13, 2009
On sheet 1 I have a list of 1000 firstnames
On sheet 2 I have a list of 1000 emails,
I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.
View 5 Replies
View Related
May 21, 2009
Increment all cells located in Column H up by one (the one above it) cell Except for the header cell (since its the header)(H1),
H2 will always be a blank due to the make up of my database so that will not cause the header to be re-written.
Column H is not an entirely filled column and some cells are empty (if this info was needed in optimizing the code),
Im seeking the quickest and easiest way of implementing this since the database is very large.
View 2 Replies
View Related
Jun 16, 2014
I have data in E6-E67 on Sheet 1. Based on the date in A2 on that sheet, I need to paste to a column in Sheet 2. In excel, I am able to get the cell location through vlookup and get the correct column number/cell reference. When it gets to the paste location, I am stumped on how to format that line of code? Do I need sometime of variable? I tried to use the address/lookup code but it does not work.
I have excel 2013.
View 4 Replies
View Related
Jan 24, 2014
I am trying to come up with the most efficient way to copy data to multiple sheets within the same Excel workbook. The original data exists within one column on a summary sheet (could have up to 500 individual entries). I want to copy each individual entry to a unique sheet (that already exists), but in the exact same cell location within each sheet. I would only want to copy the original data value and not any formatting. Is there an efficient way to do this?
In my example spreadsheet, the original data is on the SUMMARY sheet. Sheets A through J would be the target sheets, with cell B2 as the target location for each of those sheets. My example shows the result of a manual copy paste value process, but I am hoping to automate that.
View 14 Replies
View Related
May 1, 2008
sumif problem but it wont work with a countif or average if.
Column A has various names and Column B has amounts, what I need is to count the number of occurances "John Smith" has an amount in Column B. The previous formula I tried was
=sumif(A:A,"John Smith",B:B) but with either countif or averageif it errors too many arguements.
I wasn't sure if Dcount or an array would be suitable but have not used them before.
Pivot tables I'm sure will be the future with this but haven't got to the foot of that mountain yet.
View 4 Replies
View Related
May 5, 2006
Trying to set this code to position the input box over the target cell so a user has to fill in the box before proceeding. Is this possible?
ElseIf Target.Value = "Plate" Then
Add = InputBox("Please Enter # of sides to be Painted. If Not Required Enter 1", "Paint Sq.Ft.", "1", "c", "Target")
If Add = "2" Then
Target.Offset(, 19).FormulaR1C1 = "=RC[-9]*2"
Else
Target.Offset(, 19).FormulaR1C1 = "=RC[-9]"
End If
I Know input box movement is set as twips but can i just somehow specify the intersection between row c and target as it's position?
View 4 Replies
View Related
Jan 23, 2007
TotHCInv.Value = WorksheetFunction. Sum(KRInv, PBLInv, CRInv, PVInv)
If i >= 34 Then CPSCtphRMA.Value = WorksheetFunction.Average("G" & (i - 30) & ":G" & i)
The first line runs properly, but the second line bugs out with the error message "Unable to get the Average property of the WorksheetFunction class". I can simply do the math, but I thought that using the worksheet function would be easier than summing and dividing. I'm curious, though, as to why I can't seem to use the Average function.
View 3 Replies
View Related
May 10, 2006
I need to get the average of every 6th cell in a column, excluding errors --- three times.
The first average should be every 6th cell beginning with z4, the second every 6th cell beginning with z6, and the third every 6th cell beginning with z8.
Is there an easy way to achieve these 3 averages?
View 9 Replies
View Related
May 28, 2008
I have a database with over 100 products listed across the first row.
Column a has a list of over 500 projects. Across each project various columns are marked with a number depending on how many of each products are being used on that project.
For Example
A B C D E etc.
Products --> X Y Z AA
Proj 1 2 3
Proj 2 1 4 5
Proj 3 2 4
etc.
I want to be able to create a report for any given product.
The report could look like,
Product Z
Proj 1 3
Proj 3 2
So I need to lookup the product code across row 1 and determine the column number and then INDEX down that column and find all non blank cells and read the project names from column A.
I am familiar with formulas with INDEX and V/H LOOKUP functions. I am not very good with VBA codes.
View 14 Replies
View Related
Oct 8, 2009
I would like to know if it is possible for the output from a match function to be correlated to a location? The output I receive is just a number. I would like to use this number as the row, but I would like to hyperlink it into a formula so that I can create a macro for this process.
In essence I would like for a number in one cell to become a location in another cell.
View 9 Replies
View Related
Jun 14, 2013
E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).
E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.
I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.
View 14 Replies
View Related
May 27, 2013
i'm running out an option, i must use offset or index function but there are problem found: 1.Offset function cannot use a dynamic height or number of columns and the result are #NAME?, here is the code
VB:
Private Sub CommandButton1_Click()Dim k As Variant
Dim b As Variant
Range(Me.RefEdit1).Name = "item"
[Code]......
View 3 Replies
View Related
Jan 11, 2014
I am trying to save an excel sheet to .csv format with the following macro:
[Code] ......
The following part of the code needs to be modified so that the commas appear correctly in .csv file even for null values for any given column.
[Code] .....
I have attached the Sample Sheets. Source sheet and the Result sheet. The Result sheet doesn't provide the required result. Some data are missing and unwanted commas have been added. How to correct the logic in the above piece of code.
Attached File :
Result Sheet.csv‎
Source.xlsm‎
View 14 Replies
View Related
Sep 5, 2009
I have a line in my VBA code that i need to change the location of Sheet1 to another workbooks. Instead of Match at Sheet1 at the same workbook I need to match with sheet1 at another workbook called ("Master.xls). The Original Line:
View 3 Replies
View Related
Aug 5, 2009
First off, I hope one of you will end my suffering . I have been googling this thing every which way and haven't found my answer to what seems like a simple question.
What formula will find the last cell with data in a column (in say column 'B') and average backwards 30 data points? The last cell changes constantly, but I need the last 30 averaged consistently.
View 13 Replies
View Related
Mar 7, 2014
We have one excel file for monitoring of action items generated by the management after the study. As since there were around 3000+ rows has been generated since in the beginning of 1990's till to-date. So I was thinking of instead of getting the result through filter manually, I want to create a formula that will count of how many has been closed this year and this month out of the total numbers of action items.
Is it possible to use the COUNTIF function formula to count the number of items in column A, and date of column B, and closed in column C.
In below, we can see that there were 4 items under Revalidation has been closed this month and the total number of closed this year is 6.
TYPE
MTD Closing Date
Status[code]......
View 9 Replies
View Related
Sep 25, 2008
ColumnA Column B ColumnC (Yes/No)
2121 345766
23423 6456546
4234 6456456
7567 64566456
76756 654645
67567 3344534
76575 34534534
756756 45345
7657 534545
756767 4534534
756756 435345
Hello,
What I am trying to do is
Search the number written at ColumnB in ColumnA and if found write YES to column C
if not write No
View 9 Replies
View Related
Mar 31, 2014
A1 cell: 9
A2 cell: 6
A3 cell: 2
A4 cell: 4
i want to know location number of min value in range[A1:A4].
the result: 3
View 7 Replies
View Related
May 15, 2014
I need code for a search box function, that returns the information recorded in a cell for example, "Barcelona" or "London" etc), instead of the location of the cells.
I will need to narrow it down to search only the information in the following columns:
Sheet2
I2:J10932
I am totally new to VBA coding and have stumbled my way through a few things, but everything I have searched for so far has had at least one error when transposing to Excel.
I am running Excel 2007.
View 3 Replies
View Related
Aug 5, 2014
I am trying to use some vba match function code to return the column number of the matching date. The date will be stored in a date variable.
Every time I run this code I normally get a match error even though the date is in the worksheet and the variable matches that date.
See below:
[Code] .....
View 5 Replies
View Related
Jun 12, 2007
I have a lots of number arranged in a column. I want to take log of each number (on the base 2) and show the result in the adjacent column. I want this to be in a macro and the results to be displayed all at a time (I dont want to drag the cursor down to get log values for number corresponding to each row).
View 6 Replies
View Related
Mar 25, 2008
I want to create a user defined function to search a column of data for a part number.
If it exists I want to have a the UDf returna "fail" otherwise "pass"
Here is the code I was trying to use
Function firstpass(SN As String) As String
ws = Worksheets("Defects")
c = ""
With ws.Range("a1:a9999")
Set c = .Find(SN, LookIn:=xlValues, lookat:=xlWhole)
End With
If Not c Is Nothing Then
firstpass = "Pass"
Else
firstpass = "Fail"
End If
End Function
This function only returns a "#value" and I don't quite know how to troubleshoot it.
View 9 Replies
View Related
Jun 2, 2014
I have a spreadsheet where I track resources needed on a project. Each resource that is selected in the resource column has a corresponding % avg and an average number of months where the resource is used. % Avg and # of Months are found in a different worksheet.
What I am looking to do is, for example: if resource A will be on the project starting in May 2015 and assigned at 30% for 7 months, I want to populate 30% starting in the colunm labeled May 2015 and copy the 30% so that a total of 7 months are populated. Also, if any of the values change (i.e: the Resource Name or the Start Month) I want to erase all the percentages that were entered in the row. How can I achieve that with a VBA code? I have formulas that retrieve the # of Months and the % Avg already.
These are the columns that I have in my spreadsheet:
Resource NameStart MonthJan-15Feb-15Mar-15Apr-15May-15Jun-15Jul-15Aug-15Sep-15
View 2 Replies
View Related
Feb 18, 2010
TPR, DISPLAY and FEATURE columns generate a rating based off of an IF function. In the Executed column, I need TPR, FEATURE, DISPLAY to be averaged together...BUT....In I want the average only include columns where there are numbers. For example in row one the eqn would be (1+3+2)/3, but in row 2 the eqn would be (1+1)/2...can I state an average function within an if function? Or what would be the best way to create an eqn for this?? I have thousands of rows to complete and doing it manually is not an option.
0- Did not meet expectations
1- Below expectations
2- Met expectations
3- Exceeded expectationsTPRDISPLAYFEATUREExecuted?Effective?Comments132Coming off of a Dec promotion113111111221
View 9 Replies
View Related
