Average Row Of Numbers Using Different Row #'s
Field 1 input: Beginning Date (e.g 7/17/09)
Field 2 input: End Date (e.g. 7/23/09)
Need: Average costs on and between these given dates
From table with this information:
Date Market two
7/16/2009 80
7/17/2009 80
7/20/2009 82
7/21/2009 84
7/22/2009 82
7/23/2009 82
7/24/2009 82
7/27/2009 82
I need Excel to average Market two 7/17 to 7/23 (80, 82, 84, 82, 82) based on the dates I give it.
If I change the date to 7/20 to 7/24, I then need excel to average (82, 84, 82, 82, 82)
How can I get Excel to average based on different dates? Or basically, how do I get it to pull rows between 2 inputs?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
AVERAGE Row Of Numbers And Return Corresponding Numeric Label
I have one Row that houses numbers 80-90 in seperate cells (11 columns A20: K20) - these are my Numeric Labels. I then have several other Rows that span the same number of columns as the Numeric Labels (A21:K100) and house Numeric Values that relate to the Numeric Labels. I would like to AVERAGE the Numeric Values in each Row and have a Numeric Label (value) that corresponds to the calculated average returned as the result. Example: Sample Data A20:K20 (Numeric Label) 80 81 82 83 84 85 86 87 88 89 90 A21:K21 (Numeric Value) 07 06 05 03 09 09 00 02 04 12 10 Based on the above data - the Expected Result is 81 Average = 6; Return Numeric Label = 81 The leading zeros shown in A21:K21 is for alignment purposes only. I would appreciate two formulas: 1) Includes zeros to be averaged 2) Excludes zeros from being averaged I have tried variations on this =INDEX($A$20:$KJ$20,MATCH(AVERAGE($A21:$K21), $A21:$K21,0)) but receive the #N/A error message.
View Replies!
View Related
Delete Row If Doesn't Contain Certain Numbers
Working at a company where we every month extract our customers into a excel document. This is the CRM system , and some of the clients can't (shouldn't) be approached. In Cell C is their customer classifications. In total there is about 60 different classifications. 10 of them is clients who we can send information to. For example customers with class 11111 or 41414 or 51515 or 61616 (among with 45 others) can't be approached. I'd like a macro/script who can delete all the nonrelevant clients so that afterwards I have a list of clients who can be approached.
View Replies!
View Related
Highlighting Active Cell's Row, Along With Any Row That Shares Same Value In That Column
Is it possible to click on a cell in column C, and have the wishlist below happen: That active cell's row is hightlighted. Any cell in that column that has the same value as active cell is also highlighted. Plus, any cell in another sheet that has that value it's row is highlighted too. Example: I click on C5 in Sheet 2 its value is 45000789 it row is highlighted, this value also appears in C3 in the same sheet, so it's row is highlighted as well. Plus, in sheet 1 in C10 this value appears and it's row is highlighted as well. When any of the values are clicked again the highlight is removed from all parties.
View Replies!
View Related
Sorting Certain Columns But Ensuring Certain Rows Remain Fixed To Their 'master Row'
I have created a spreadsheet which is tallying up various data. I have a number of different categories e.g. A, B, C, D and within each one of these are sub categories. I then have a tally for the number of each category and within that the number of each sub category. I then want to sort the data so that first the main category is sorted in order of highest tally to lowest. the sub categories however need to stay with their primary category. I then need to sort each sub category within the main category. the data looks omething like this: category tally sub cat number A 6 A 3 B 4 C 6 B 9 A 1 B 6
View Replies!
View Related
Copy The Information From The Row Directly Above The New Row And Paste (values, Formulas, Formats, Etc) Into The New Row
1. In whatever cell is selected when the macro is run, enter a new row. 2. Copy the information from the row directly above the new row and paste (values, formulas, formats, etc) into the new row. 3. Return to column P in the new row, i.e if the new row is row 11, then return to P11, for row 12 return to P12, etc. I have tried recording the macro but because it is hard coded to specific rows, its not working. I have attached a sample copy of the sheet (had to zip due to the size of the file).
View Replies!
View Related
Average Minus Most Recent Row
I have a table in a sheet where the rows of data are filled in automatically from other sheets. Each row represents a different week. The data, (lifted from from the relevant worksheet for each row) represents the no. of calls in each week. Now, I've set up an average to be calculated from the data - it'll do this for every row as long as there is data present. (If the row's corresponding worksheet doesn't have any data the result will be "NA()" - not included in the calculation.) However, I don't want the average to include the no. in the "most recent" row, as this is "live" data and until the week is over does not represent the data for a full week and therefore skews the average. So I want the average to exclude the data in the most recent row with a numerical value in it and not a "NA()" ie - column could be ......
View Replies!
View Related
Average Next 5 Non Blank Values Of A Row
I would like cell B2 to show the average of the next 5 non blank values in row 2 (C2-Z2). The problem is there are varying blank cells (non data points) depending on each row. So i need the forumula to account for just the first 5 immediate non blank cells.
View Replies!
View Related
Getting Value From Next-highest 'header' Row
I have a worksheet that contains a number of rows. [Thank you, Captain Obvious!] These rows are logically grouped to represent a series of "items", with each item having one or more rows (with a "header" row at the top). The rows for a particular item represent different types of information about that item, and therefore make use of different formulas based on the row type. Due to the relative complexity of the formulas in these rows, I am creating a series of row "templates" from which I can copy and paste every time I want to create a new item or add rows to an item. As such, I am trying to avoid direct cell references to different rows, as these would easily get messed up during cut and paste. Instead, I am trying to limit myself to various lookup functions (e.g. OFFSET, MATCH, etc...) that will work regardless of where a row is added. In order to accomplish this I am using a "key" column to group all rows for a particular item together. Until now I have had to type in the key value for every row for a particular item. What I would like to do is to only have to type the key value in the header row, and have a formula populate the key value in all subsequent rows ... until a new header row is reached, at which point the new key value will be used. Take a look at the following example: ....
View Replies!
View Related
Find '0' And Hide Associated Row
I need to write a macro which find the value 'O' in column D, then hides the row which contains that value. I have very little VB knowledge I'm afraid. I have got this far. Sub Find_0_and_hide() ' Find_0_and_hide Macro ' Macro recorded 20/03/2007 by Zoë Braven-Giles ' Select all rows and unhide them Cells.Select Selection.EntireRow.Hidden = False ' Find value '0' in column D and hide associated row End Sub Do I need to do an array? Or an IF statement? Though I am not sure how to do either I'm afraid.
View Replies!
View Related
Identify Button (shape) Row To Insert Row And Delete Row
I have a button (group containing and add and delete button). I want to identify the row (position of shape/button calling the macro) to enable inserting a new row (1 row down from current row). Then do the same to delete a row (position of shape/button calling the macro) to enable deletion of selected row. This will allow me to add/insert rows by the button located at that row The problem i have is getting the row property (row position of the button eg. TopLeftCell.Row) of the add button. The add button (RowBtnAdd) is a shape within a group (BtnGrp) I also note that when a group is copied, it has the same shape name as that copied. I want to keep the add and delete shape within the group (BtnGrp). I do not want to select a cell or row or enter a row number to delete etc. refer to sample workbook attached. Currently only has one record row.
View Replies!
View Related
Combine Numbers In A Row From A List Of Numbers
I have to create lists of data in the following format: 123121321,12321215,121351213 (numbers with , with no spaces) These numbers are sent to me in excel in columns and I need to convert the columns into rows using the format above. The formula I have been given: =INDIRECT(ADDRESS(((ROW()-1)*10)+2,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+3,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+4,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+5,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+6,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+7,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+8,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+9,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+10,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+11,1,1,TRUE,"SSOs")) currently I only get 10 numbers per row I need at times for up to 500 numbers to be listed on a row. However even when I try to extend the formula it still only gives me 10 numbers per row and breaks up the 500 numbers to 10 per row. Is there another way to combine up to 500 numbers in one row?
View Replies!
View Related
Copy Entire Row By Range & Suppress 'Cannot Edit Links' Message
I have 10 workbooks which have various calculations and have several links to each other. We use Excel 2003. For each of these files, I have the following code in the Auto_Open module Range Range(“TodayComp”) is a date taken form a link in another spreadsheet. It is on Column 3, but the row changes every day as more rows are inserted before it This sub looks at Range(“TodayComp”) and checks the date on the cell directly above. If say Range(“TodayComp”) is 6/25/2008, and the cell above is 6/22/2008, it will insert three rows right above Range(“TodayComp”). These rows need to be copied with formulas from the current row above Range(“TodayComp”) Issue One: The following code works (it copies the rows), but it takes forever to run, as you can see in bold, I copy and paste each cell, instead of the entire row. Is there a way to copy and paste the entire row? Remember that I have only a named range to refer to, no cell address as it changes every time. Public Sub Auto_open() Update_Dates() End Sub Public Sub Update_Dates() Dim tdy, prev As Date Dim index, i, j, yr, no_inserts As Integer Sheets("Comparison Computation").Activate
View Replies!
View Related
Copy 'x' Cell Value Evey Nth Cell Along On Same Row
Have often used this site, but now need an answer to something a little more specific. I would like to create a forumlae/function that would allow me to copy a cell value 'x' into every 'y' cell along. e.g copy cell x every 3rd cell along (in same row). The formulae must allow for 'y' to be of any value between 1-5 as this can change. I have attached a screenshot, as pictures are probably much better than my text above.
View Replies!
View Related
Row Numbers For Each Sub Total
i had a subtotal sheet with more than 5000 rows of data.i applied subtotals for my data.i just want to serial numbers for each of the subtotal.what is the easy way for numbering my rows with sub total. My subtotals are in column A.i inserted a column before it and want to show the row numbers which contain the subtotals.
View Replies!
View Related
Insert Row Numbers
I'm using Excel 2007 and have a spreadsheet with 2500 rows. What I would like to do is insert the row number into a blank cell in each row. This will become a unique ID# for each row that I need to import this into a database later.
View Replies!
View Related
Colour Row To Denote It's Use In Autofilter
I am using autofilter and then copying some cells from the rows that are selected as a result and pasting them to another worksheet within the workbook. My problem is that in the worksheet that contains the autofilter I need to colour (or index?) the row that I am copying to show that it has been used. I tried UsedRange but got in a muddle.
View Replies!
View Related
Add Up All The Numbers In Row 1 Getting An Error
Can't seem to figure this one out. When I try the "=SUM(1:1)" on A1 to add up all the numbers in row 1 I am getting an error. Is there a way to have A1 hold the sum of all the numbers on row 1 excluding A1 cell? Will be adding new columns frequently and I don't know when a column ends to assign the =SUM to, so assigning row take away that problem but created another. Want it on A1 because B1 have the name of that row. So the numbers actually begins on C1 - infinity.
View Replies!
View Related
Add Numbers Together That Fall Under A Certain Value In A Row
I need a formula to add numbers together that fall under a certain value in a row and a certain column from more than one pivot table. I have used this =GETPIVOTDATA("orders",$A$*,"Month","December,","WAIT BAND","2-3") and this works fine for bringing across the data for that pivot. The problem i have is if i then also want to add numbers from another to another pivot. =GETPIVOTDATA("orders",$A$*,"Month","December,","WAIT BAND","2-3") + =GETPIVOTDATA("awaiting orders",$A$*,"Month","December,","WAIT BAND","2-3")
View Replies!
View Related
Averaging Smallest, Non-zero Numbers In A Row
I'm struggling to get the syntax right for this formula. I've been trying variations on the following formula but can't get it right: =AVERAGE(SMALL(IF(AP8:AZ80,AP8:AZ8),1))) Columns AP:AZ: 1.8921.7621.6501.1741.2261.2991.3020.0000.0001.1461.333 Any column in this range could have a zero value. What I need: The average of the 8 lowest non-zero numbers in range AP:AZ
View Replies!
View Related
Row File Numbers Missing
Excel 2003. As I entered 720 names/addresses in a spread sheet, I stopped and started repeatedly. When done, the row numbers go from 1 to 728. Puzzled, I carefully reviewed all names and the row numbers assigned. Row numbering skips eight numbers, 432-439. How do I get Excel to reassign the row numbers so they reflect accurately the number of entries?
View Replies!
View Related
Drop Down List Isn't Transferring In New Row
I have a spreadsheet that has 5 columns. The first row is the Header row, the second row is for the data input, here are the columns: Column 1: Company Name, I have a drop down list associated with this column. Column 2: Sales Person, I have a VLookup function to check for the company name in Column 1 and fill in with correct Sales Person. Column 3: Total Sale, Manual input. Column 4: Commission %, I have a VLookup function to check Company Name and fill in the correct Commission. Column 5: Commission, Basic function of Total Sale * Commission %. I have a macro created that when run it will take all the information from row 2 and move it to row 3 and at the same time inserting a blank row 2 with the functions for me to fill out new information. The problem that I am having are 2 things: 1: When running the macro the functions will get applied to the newly inserted row but the drop down list for the Company column doesn't. 2: I would like to be able to start typing in the company name i.e "F" and have it jump to the F's in the drop down list.
View Replies!
View Related
Return Active Cell's Location/row
I'm having trouble identifing a way to return a location for the position of the active cell. I've searched Excel help with "Position, location, return, activecell, etc." and I can't seem to figure this out. I know that it's possible, so that's why I'm on here! ... Ok, say the active cell is currently "F1", and I need the location "F1" to identify the ROW to be used in a formula later, how would I go about that? The current contents of cell "F1"' will be "REPLACE", but I need to change the words "REPLACE" in "F1" and other cells labeled "REPLACE" in column F to the following formula (where the "1" in "A1" is is the current row):
View Replies!
View Related
Nested If's Or How To Check Multiple Conditions In A Row
I have the data in this form YYY NYY NYY NNY NYY NNY NYY NYY NYY NYY YNN NNY NNN NYY The output should check two conditions. If all three columns are "Y" then it should return value "Y". If any two columns are "Y" then it should return value "Y". or else it should return "N". Then the output should look like this.... The output column is colored in Red..........
View Replies!
View Related
Can't Get Row References To Update From Userform Formula
I have a userform that populates a list of names. As a name is added in column "D" column "C" updates a reference number using the formula =IF(D2="","",C1+1). I can get the formula to copy to the spread sheet but as a new name is added the formula stays the same. If I enter a name in cell D2 everything is fine but when I make the next entry in row 3 the same formula is entered as if it were text and not as a formula. so I wind up with a column of formulas that all say =IF(D2="","",C1+1) reguardless of what row the formula is in.
View Replies!
View Related
Sum Up A Row Of Numbers Based On Another Cell
I have been finding answers on this website for soo long. But today is the day, I finally am stumped and could not find an answer here. I am setting up a standard excel sheet of data. Row 1 has labels and the data goes down each column. the first column is 1 of only 5 words. the rest of the columns are number values. Example. city num1 num2 num3 cityname 3 4 5 cityname1 2 3 4 cityname 3 1 2 The data in the first column will be 1 of 5 city names. My question is how can I setup a cell to keep a sum of num1, num2 and num3 of each of the 5 cities. I have been scratching my head and googleing for a couple hours now. I was thinking of using an if then statement. Here is my psuedo statement for a cell that would be keeping track of num1: (if any cell in column 'city' = cityname add the current value in this cell to the value located in column num1 of the row thats currently being evaluated. The cell that contains this formula would have to constantly monitor the city column and update the num1 column as needed.
View Replies!
View Related
Find Last Used Cell And Align All Numbers In Same Row
Example Table A: *ABCDEFGHIJKL1No. 1No. 2No. 3No. 4No. 5No. 6No. 7No. 8No. 9No. 10No. 10No. 10221418269124333324171165141728884321000125067775100860615906015712170712180910 Result Table B: *ABCDEFGHIJKL1No. 1No. 2No. 3No. 4No. 5No. 6No. 7No. 8No. 9No. 10No. 10No. 10263214854241121253210609433610178011412288370151076151767788070121206015907910 What i want here is to align all the numbers considering the last used cell. In example table a; the last number is 0 (F8). Considering that, in the Result table B all the numbers are aligned in row 8 from the bottom to top giving an equal alignment to the table.
View Replies!
View Related
Displaying The Row And Column Numbers Within A Range
Is it possible to display the actual relative row and column number in a cell within a range more efficiently then listed below? For example: I have a named range "data" D7:L19. In cell E8 should display R2C2. In cell G17 should display R11C4. etc....... I am using a method described below where cell E8 will have the formula: ="R"&ROW()-ROW($D$7)+1&"C"&COLUMN()-COLUMN($D$7)+1 The reason i am doing this is to solve a bigger problem creating dynamic column headers from a vertical list (named range). But sometimes the column headers, although being consecutive, may be listed in every other column. Col 1 Col 2 Col 3 Col 4 Heading1 (nothing) Heading2 (nothing) etc...... Dynamic List Heading1 Heading2 etc....
View Replies!
View Related
Update Sequential Numbers After Row Deletion
How do I get my worksheet (ServicePlan) to update column A (plan number) after deleting a the row from a userform. When I delete the row the plan numbers do not update. Example: I delete plan #1 from the worksheet. The plan number #2 should now be amended to read plan #1 and the plan number for record #3 should now be amended to read Plan #2, however the plan numbers #2 and #3 remain the same. Plan #Rv# Name:Dept: Rep: 11.1mikeEnvironmental Cathy S. 22.1TomCasualty Rich A. 3 1.5 Michelle Special Programs Diane Here is the code I used for the delete plan # button. Thank you Private Sub CommandButton15_Click() If MsgBox("Are you sure you wish to delete this record?", vbYesNo, "Confirm Deletion") = vbYes Then sheet1.Activate Dim strFind Dim Nullstring Dim rSearch As Range 'range to search Set rSearch = sheet1.Range("A2:A1000") Dim c Dim r As Long
View Replies!
View Related
How To Lookup Row # Based On Content Of Another Column's Cell
I have a 3 column table with 20 rows. The first two columns contain travel expense data. The cells in the third column are blank exept for whichever cell I enter an "x" in. Please let me what formula or worksheet function do I use to calculate the travel expenses based on the data in the first two columns that correspond to the row of the cell with an "x" in the third column?
View Replies!
View Related
|
TRACKER
