Excel 2010 :: Average Function With A Variable
Jan 4, 2014
I am working with Excel 2010 and I have a problem that I can't seem to figure out. I am trying to find the first nonzero cell and begin an average function from that cell and down through 29 rows (a total of 30 rows).
Sub average()
FinalRow = Cells(Rows.Count, 3).End(xlUp).Row
For I = 3 To FinalRow
If Cells(I,3).Value 0 Then
'I use the above to identify the first nonzero cell
ActiveCell.FormulaR1C1 = "average(........)
This is where I am stuck, I don't know how to code the first nonzero (which can change with different data) cell and average rows below it.
View 7 Replies
ADVERTISEMENT
Nov 12, 2012
I am trying out VBA to write a macro in order to average every 25 cells in a column. My attempt so far is
Dim myrow1 As Integer
Dim myrow2 As Integer
myrow1 = 2
myrow2 = 25
Range("G2").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R " & myrow1 & " C3:R " & myrow2 & " C3)"
myrow1 = myrow1 + 24
myrow2 = myrow2 + 24
So I am hoping the first ActiveCell.FormulaR1C1 gets read as =Average(C2:C25)
However I just get Run-time error '1004' Method 'FormulaR1C1' of object 'Range' failed.
View 3 Replies
View Related
Jul 23, 2014
I am using Excel 2010 and I am currently trying to find the Avg Min / Avg Max costs for 4 different groups.. Players, Accompanying, Coaches, Medical.
I need it to get the minimum values from 3 different sections ( I am using custom names for them such as PlayerReg for Player Registration costs ) once it gathers the minimum / maximum values from the 3 different sections it needs to add them together to display the Avg Minimum / Maximum for each group.
At the moment I have =MIN(Total_Discount_7_Nights + PlayerReg + FrequentFlierFare) under Minimum /w Frequent Flier although the minimum value it should detect is $948.75 it displays $1115.
For maximum I have
=MAX(Total_Discount_7_Nights + PlayerReg + FrequentFlierFare) under Maximum /w Frequent Flier although once again this value should be $1174.50
Although for some reason it displays $978.75 - which is the minimum cost. The Maximum figures for the other groups (Accompanying, Coaches and Medical) don't even display values Instead it just has #VALUE! in which I'm guessing I have something wrong with it.
View 2 Replies
View Related
Apr 8, 2014
I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:
[Code] ........
When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.
View 14 Replies
View Related
Apr 10, 2014
Basic Excel-2010 functions.
I have given a table with some decimal values like e.g 0.3658 0.358 0.485 0.7863 0.558 0.858 0.985, I want to find the sum and average of these number.
1) I have tried the autosum or auto Function =AVERAGE(A2:A7) but it yields #DIV/0! error
2) when I use =AVERAGEA(A2:A7), it yield ans=0
3) puttin =ROUND(AVERAGEA(H2:H23);3) also yields 0;
View 4 Replies
View Related
May 29, 2014
Data example is displayed below. I need to know how to find the average, not date average, but count. What is the average per day? Example on 2/9/2014 there are 12 entries but on 2/10/2014 there is only 1. Example: For all the lines of data that I have, what is average per day?
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
[code].....
View 4 Replies
View Related
Sep 10, 2012
I have a sequence of 40 columns of data, representing 10 weeks worth of events with 4 values per week
I need to average the first pair for each week, ignoring zeros and blanks, and also separately the 2nd pair of cells, across the whole 40 columns
I am trying to come up with a neater solution which takes into account the zeros, at the moment I have the following which does not, for row 34
=IF($AR34>0,ROUND(AVERAGE(D34:E34,H34:I34,L34:M34,P34:Q34,T34:U34,
X34:Y34,AB34:AC34,AF34:AG34,AJ34:AK34,AN34:AO34)*2,2),0)
I could use SUM, COUNT COUNTIF etc but its gonna get messy
Using EXCEL 2010
View 5 Replies
View Related
Apr 3, 2014
In our sql server table, we have a field of type DATETIME. This field is populated by a vendor product with the elapsed time of an event.
We created a SQL Server view of this column and other columns of info joined together from several tables.
Then we create in Excel 2010 a new spreadsheet with a data connection to the SQL Server, instance, and view that we created.
Excel 2010 displays each of the columns in our view.
The elapsed time column appears as "dd hh:mm:ss". However, when we click on a cell to look at its formatting, Excel lists it as general.
We want to calculate the average time in this column.
We go down below the data, click on a cell, and insert =Average(C2:C17215) and we get an error saying dividing by zero.
We have 17,000+ rows of data, so there is no divide by zero math going on here. Each row in this column has data, so there are no blank cells.
We have spent some time web searching. We then tried various methods of calculating this. Nothing has worked. In most cases, we get the divide by zero. We tried some solutions that mention using an array forumula, but that just changed the error to an invalid value error.
View 1 Replies
View Related
Apr 5, 2012
How I could create a chart (standard column) with an average line in Excel 2010?
View 4 Replies
View Related
Jul 5, 2012
Using Excel 2010.
I am trying to come up with a formula that will return a total average from two columns of dates with criteria. The range will need to cover an entire column as my data is continuously growing and the criteria would have to limit the start date to each month. I have tried
=AVERAGEIFS(DAYS360(A:A,B:B),A:A,">5/1/2012",A:A,"5/1/2012",A:A,"
View 2 Replies
View Related
Jul 9, 2014
Please find the attached 2010 version excel file <For a given date Time Range average .xlsx>.
I want to calculate the average value (Column C) for the given date (Column A) and given time range (Column B).
Values to be plotted in the color cells yellow and light pink / magenta.
Voting Results.xlsx
View 4 Replies
View Related
Aug 25, 2014
Please find the attached MS Excel 2010 file <average set.xlsx>.
There is set of positive set & negative set of values available in the Column A. The values are plotted against Column A in Column B. Light green are positive sets and light yellow are negative sets.
Now I want to calculate the average for the positive set & negative set of values as shown in light majenta in the cells F4:F9. Also all majenta cells to be plotted by formula.
The Column H and Column I also same as like above , but here negative set of numbers starts first.
View 4 Replies
View Related
Dec 4, 2013
1.jpg
I wan to calculate the average for three cells not in Sequence using AVERAGEIF with condition (VALUE >0)
View 5 Replies
View Related
Jan 20, 2012
Excel 2010 Higher than past average formula?
Im using Excel 2010 and want to make a formula that will High light the number when it exceeds the past 30 days average. My information is listed vertically in row F..
View 3 Replies
View Related
Mar 13, 2012
I need to return a value dependent on a few criteria. Type will be selected via a drop down and then it must lookup the same type in the top row and then the value which will be between two values and then return the grade on the far left.
GradeG4M5M6TypeValueGradeA
160016501800M5850CB600700650780900950C7008007809109501100D8009009101040
10001250E90010001040117010501400F100011001170130011001550G>1100>1300>1150
I am using Excel 2010 on Win 7
View 6 Replies
View Related
Apr 2, 2013
I'm trying to pass a variable from Word to Excel. Basically I have a Word document with a plain text content control in it. I'll have users populate this field. I know how to create a reference to that content contol in Word VBA that'll tell me what's in that content control (eg.
Code:
MyWordVar = ActiveDocument.ContentControls(1).Range.Text
).
What I can't figure out is how to pass the value of "MyWordVar" to a variable in Excel.
View 4 Replies
View Related
Dec 3, 2013
Code:
=SUMPRODUCT(--(_NamedRng1=NamedRng2),$B$49:$F$49)
I am using the above formula in my code with two Named Ranges
Code:
Set Rng3 = Range("_NamedRng1").Offset(1, 0)
=SUMPRODUCT(--(_NamedRng1=NamedRng2),rng3)
[/CODE]
I want to set the range $B$49:$F$49 in my code and I have tried the above, but it does not work.
I want to allow for the fact my end user may insert rows so do not want to use $B$49:$F$49
View 2 Replies
View Related
Apr 16, 2014
Excel function or macros for calculating the average of waterSD column where the TT column is less than or equal to 100(red markings). functions like IF TT IS LESS THAN OR EQUAL TO 100, THEN CALCULATE AVERAGE OF WATERSD....
I had done manually on the right side in the attached excel sheet. only those yellow markings.
Because already I have some macro which do this process but it is not accounting for the TT column less than 100. it starts from 200 TT values. but i need to include 100 TT also. That's is where now we have yellow markings. I will provide you the macro code if you can edit that where it will start calculating form 100TT value, it will be really great. The code is,
[Code] .....
Attached File : average for watersd OF 100TT.xlsx
View 3 Replies
View Related
Jan 23, 2013
I've defined a number of variables in my spreadsheet. When I want to use one I type "=variablename" and 'm presented with a list menu.jpg
In the above example, I have several variables starting with the word "Harvard" (a town, not the university :-)
How do I select one of these without having to double click with the mouse.
Up and down arrows allow me to highlight an item but I cannot find a keystroke that allows me to select the highlighted item. Instead I have to lift my hand off the keyboard and use the mouse.
Excel 2010
View 2 Replies
View Related
Jun 10, 2013
I would like to ask the user if when the name the worksheet the same as an already existing spreadsheet tabe if they would like to overwrite it or unload the user form.
I am not sure of two things:
1. how to find the already existing tab?
2. Once I find out how do I programatically delete it, so the code can continue
The code below works with the exception of the last section (trying to achieve the questions stated above).
I am using Excel 2010.
Code:
Private Sub CommandButton1_Click()
If TextBox1.Value = blank Then 'Need name for processing
MsgBox ("Name must not be blank.")
Exit Sub
End If
If Len(TextBox1.Value) > 12 Then
[code]....
View 5 Replies
View Related
Jul 17, 2013
I am after tips on creating a macro in Excel 2010 where it will search a list of folder paths in a column on a spreadsheet and insert a jpeg within the sheet from that folder if it exists. If more than one jpeg exists i would like it to insert the first jpeg only. i have found tips where it will insert images if you know the filename, however I don't have this luxury as file naming routines vary from folder to folder the only constant is that the file is a .jpg format.
View 1 Replies
View Related
Dec 29, 2013
With VBA in Excel 2010 I am connecting to a SQL server by using ODBC.
This is a two part process:
Part one:I have created a connection string that gets me the following data: A, B, C, D, E from sheet tpoPurchOrder Where B is equal to "1" And Where C is equal to a changing field under Sheets("Macros").Range("B2")
Here is part one
Code:
Sub Part1()
Sheets("Open PO by Vendor").Select
Sheets("Open PO by Vendor").Cells.Clear
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=Connection;Description=Description;UID=USER;PWD=PASSWORD;APP=Microsoft Office 2010;WSID=Workstation;DATABASE=Database" _
, Destination:=Range("$A$1")).QueryTable
[code]...
Part one works perfectly.
Part two is where I have problems.Part two is a little different because I have the same connection, but what changes is the table that I am looking at "tpoPOLine" instead of "tpoPurchOrder" and the where is now going to have a variable number of commands.
I want part two to be depend on part one. Under part one I want the result from column E to be a where statement in part two.
As follows:
In particular: *E3 from Part 1*, etc.
Code:
"SELECT tpoPOLine.Status, tpoPOLine.POKey, tpoPOLine.ItemKey, tpoPOLine.POLineNo, tpoPOLine.UnitCost, tpoPOLine.ExtAmt" & Chr(13) & "" & Chr(10) & "FROM mas500_DII_app.dbo.tpoPOLine tpoPOLine" & Chr(13) & "" & Chr(10) & "WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (" _
, _
"tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)" & Chr(13) & "" & Chr(10) & "ORDER BY tpoPOLine.POKey" _
)
Now my problem is that sometimes the E column from part one ends up being 1 row, sometimes it ends up being 50 rows. I would like the code to change accordingly.
1. Pull everything from the SQL server filter once in excel. This is not as efficient as the database has ~300,000 rows in the tpoPOline table and would take a lot longer then needed.
2. Create a nested if table and
Change
Code:
WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)"
to the value of that nested if table
The nested if table would be something like =if(isblank(E2),"E1",if(isblank(E3)... etc for ~50 rows. I know the syntax is incorrect but you get my point.
3. Rerun the query for each value in column E. That would require the connection to happen ~50 times which would not be that great, as well as I would have to copy and paste the data after each run as the tables cannot overlap.
What I am looking for is a way to run this only once, on one sheet, without writing a nested if table with 50 if's.
View 5 Replies
View Related
Jun 12, 2014
is there a way to reduce spaces between text stings to one space only when there are many spaces? To make it worse, the number of spaces between the text strings vary. I am using Excel 2010.
View 9 Replies
View Related
Jan 24, 2014
The situation I have on my hands is over 200 users, were the majority can't use Excel's built in function to create PDF's.
Yes, I've tried to teach them, but I'm probably not a good teacher.
So, I've resorted to create a button at the top of the worksheet, that says "Create PDF". I thought this was gonna be very easy to program, but alas.
Is there a way to start the built-in Excel dialog that happens when you click "Create PDF/XPS" in "Save & Send"?
View 4 Replies
View Related
Dec 21, 2011
Having problems with the code below on a 2010 Excel spreadsheet. The function flags me at first line and highlights Mid. I get a "compile error, cannot find project or library". I'm trying to set GetSheetName = m_sSheet but the GetSheetName function is not declared as returning any type.
Code:
Function GetSheetName(ByVal m_sFormula As String) As String
Dim m_sSheet As String
m_sSheet = Mid(m_sFormula, 2, InStr(m_sFormula, "!") - 2)
If InStr(m_sSheet, "'") Then
m_sSheet = Mid(m_sSheet, 2, Len(m_sSheet) - 2)
End If
GetSheetName = m_sSheet
End Function
View 1 Replies
View Related
Jul 22, 2012
I'm currently working on my masters dissertation and am using excel 2010.
Basically, I'm trying to generate a list of say, 1000 numbers within a range. I can already do this using the rand() function. However, I need excel to take each random number, apply it into a formula, and then list the answers in a seperate location. This would be easy to do if there was one simple function (which i could just insert into any adjacent cell). The problem is that each random number value is input into a table. The table, which has about 10 columns and 200+ rows, gives one final answer.
I'm sure there has to be a faster way than creating 1000 tables for each random number generated.
Let's say I have a list of 1000 random numbers in sheet 1. the function table is in sheet 2 and produces the answer in the same sheet, in a single cell. I'd like excel to use each random number generated, input it into the function table, attain the answer from the table, and list it in the cell next to the random number.
View 2 Replies
View Related
Dec 28, 2011
Im trying to write a code in vba in Excel 2010 that would incorporate the vlookup function in excel.
My Data Looks like this:
List 1Weight 1List 2Weight 2List 3Weight 3List 4Weight 4a2.00%j20.00%a14.00%p2.00%b4.00%k32.00%d2.00%y3.00%
c7.50%e10.00%g14.00%u6.00%d8.00%d15.00%h20.00%h7.00%e9.00%q13.00%y45.00%f8.00%f20.00%r5.00%q5.00%
d9.00%g35.00%n5.00%t10.00%h10.00%r14.00%i4.50%w15.00%b0.18Z0.04S0.04Total100.00%Total100.00%Total100.00%100.00%
Where I have X number of Lists, each composed of two columns. The 1st column has the name of each item in each list, and the 2nd column has the value for that item.
What I'd like to do is create a function that would let me choose two lists, and tell me the amount of items in List X and what their values are in List Y, and then total them. Also, I would like it to work the opposite way, and tell me the amount of items in List Y and what their values are in List X, and then total them.
So for example, If I wanted to look at lists 1 and 2, the function would calculate that for List 1, Items D & E are found in List 2 and have a total value of 25%. For List 2, Items D&E are found in List 1 and have a total value of 17%. For all items not in both lists, it would return values of 0.
The code I came up with so far looks like this:
Function AK_Overlap_Go(x, y)
Dim Temp(1 To 2, 2 To 1)
Dim x As Integer
[Code]...
One of the problems I'm having is that the lists contain a different # of items, and so I think I need to loop the vlookup for each row. However, I'm not sure how to do that and get the cumulative values for each list.
View 3 Replies
View Related
Apr 18, 2012
The problem is that whenever I have any browser open, IE, Chrome, Firefox, etc... the Cut & Paste, Copy & Paste function does not work correctly in Excel 2010.
When I Cut or Copy the blinking marquee around my selection briefly appears and then disappears. When I try to paste, I only have two options under the paste special function: Unicode Text and Text, same with cut and paste, however, the text doesn't actually cut, it only copies.
As soon as I close down any of the mentioned browsers, the full functionality of the cut/copy & paste functions are restored, no need to restart excel.
I need to have open a browser most of the time for work as our system is web based, so closing and re-opening is more than just an annoyance.
View 2 Replies
View Related
Jul 22, 2012
I'm currently working on my masters dissertation and am using excel 2010.
Basically, I'm trying to generate a list of say, 1000 numbers within a range. I can already do this using the rand() function. However, I need excel to take each random number, apply it into a formula, and then list the answers in a seperate location. This would be easy to do if there was one simple function (which i could just insert into any adjacent cell). The problem is that each random number value is input into a table. The table, which has about 10 columns and 200+ rows, gives one final answer.
I'm sure there has to be a faster way than creating 1000 tables for each random number generated.
Let's say I have a list of 1000 random numbers in sheet 1. the function table is in sheet 2 and produces the answer in the same sheet, in a single cell. I'd like excel to use each random number generated, input it into the function table, attain the answer from the table, and list it in the cell next to the random number.
View 1 Replies
View Related
Sep 16, 2012
Using Excel 2010 Pro.
I have a formula to create a string to create a lable for a Trial Balance report
=LEFT(I5&" "&REPT(".",95),101)
But the decimal portion of the string is distracting. I would like to make it a grey instaed of a black so that the text portion of the label "pops".
I don't believe I can do it with conditional formatting and I do not want to do it manually.I was thinking some kind of function or UDF like:
=LEFT(I5&" "&RGB(REPT(".",95),(191,191,191)),101)
View 3 Replies
View Related
