Calculate Averages Based On Where Names Are Positioned
Aug 21, 2009
Code:.....
The names are not static and can be moved, meaning that John can be 1, 2, 3, or 4 (all depending on how the name is chosen). I want to find the average on where John ranks. In this instance, he would be 2 [(1+3+2)/3].
My question is - How can I create a formula that will look for where John appears in the column (B1) and call the previous cell (A1)? This formula needs to be repeated on each column, added and averaged.
View 5 Replies
ADVERTISEMENT
Oct 6, 2011
I have 3 columns (B,D,F) that have 15 minute data intervals and I need to calculate the hourly averages for each column but here is the catch, I need to do this from July-1-2011 00:00:00 to Sept-31-2011, 24:00:00 (that's 26,217 data points and 6,554 =average() calculations......
IS there syntax I could use that would calculate the average every 4-cells so I could get the hourly average without having to type =average(XX:XX) 6,554 times?
Example of set-up:
29-Sep-11
23:00:00
0.820412164= AVERAGE(B8735:B8738)
0.762574274 =average(D8735:D8738)
84.98389991 =average(F8735:F8738)
29-Sep-11 23:15:00 0.819168591
0.762781167
[Code]...
View 6 Replies
View Related
Jan 29, 2010
I am wondering if there is a function in excel to calculate the average of multiple percentages. Currently I rely on the following formula to achieve this result and I am curious if there is a quicker way:
Try this formula. .....
Just today I learned there is a function to calculate the compounded return (FVSCHEDULE). It does part of what I am looking to do by taking away the PRODUCT function and the array, but does not really simplify things. Using FVSCHEDULE my function would look like ....
View 9 Replies
View Related
Feb 20, 2008
Situation: I need to calculate a lot of averages of non-connected values. That is, they are separated by several rows (which also contain values). So far, I use the code-sample below (just to illustrate), which is not really elegant. Unfortunately, it seems that this technique comes to its limits when a high number of different non-connected values are combined into one string and get assigned to a formula- range (last line of code), which is where the program stops.
My question is: Is there a nice way to solve this? A way, which is not limited by the amount of data processed? The only way I could think of, would include a UDF, where a sum, sums all values up and then devides by the number of added sums ... but this would be relatively slow, and I have an awful lot of values to be averaged (100 sets * 6 rows * 200 values = 120.000 calculation- steps), as well as STDEV and a Standard Error of Mean (SEM) to calculate. Is there a better alternative?
Const intCells=9 'actually, this is no constant but a variable
Const i=4 'actually, this is no constant, but a nested for- loop-value
Const intLinesInBetween = 12 ' amount of lines, which separate my values
Dim j As Integer 'simple counter, runs through all cells
Dim rngUpperRow As Range 'Range of the upper row = normalized time vector
Dim rngCurrentRowAvg As Range 'current row into which avg formulas are inserted
Dim intCells As Integer ' amount of experiments in range............
View 4 Replies
View Related
Dec 31, 2008
On another thread I found a solution to creating a weighted average given a pair of columns of numbers but I have column B consisting of values given as High, Medium and Low and the weighted column C of 1, 2 or 3.
How can I use the formula below to accomplish this, or is this maybe not the right way to created a weighted average?
=SUMPRODUCT(B2:B11,C2:C11)
View 7 Replies
View Related
Dec 22, 2011
I have a first column, which contains random integers typically with gaps of between 10 to 50 blank cells. The data may have up to 5,000 rows in total.
In the next column along I would like a calculation which averages the integer in the first column out across all rows until another integer is reached in the first column.
E.g. In the first column, the first number is 60, then 9 blank cells till the next entry. In the second column the first 10 cells each contain the number 6 ( = 60/10).
View 1 Replies
View Related
Jan 5, 2012
I have a large sheet of daily river flows and precipitation amounts, over 40+ years.
I want to calculate the average flows in each month of each year (ie 11/1968, 12/1968, 1/1969 etc). Dates are in Col A, flows in Col D.
How to average a particular month over all years like so:
{=AVERAGE(IF(MONTH(A2:A15282)=1,D2:D15282))}
But I don't know how to average per month of each year, or how to make a formula that will allow me to quickly calculate the averages of 100s of months.
View 6 Replies
View Related
Mar 19, 2013
I am trying to calculate final grades with weighted averages. However for the final grade I need to drop the lowest of the test grade which come after the bold line to the right, making the total relative weight of Tests, the weight of 2 tests. How would I go about calculating that with a SumProduct formula? I do not need to drop grades from B4:K4, however from L4:N4 I need to drop the lowest grade for my final grade, which needs to be rounded to the nearest integer. The relative weight of each assignment is given in Row 1.
View 3 Replies
View Related
Mar 20, 2008
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 %
View 9 Replies
View Related
Jan 30, 2014
I am trying to create a document that takes our client PO amounts, tactics/spend line items, and dates from one sheet and averages them across the months that it is eligible, so we can know the total amount of money we can expect to go out from our clients each month.
For example:
Client1 Tactic1 StartDate EndDate TotalAmount
Client1 Tactic2 StartDate EndDate TotalAmount
Client1 Tactic3 StartDate EndDate TotalAmount
Client2 Tactic1 StartDate EndDate TotalAmount
Client2 Tactic2 StartDate EndDate TotalAmount
I have mostly figured this out in a really complicated way with many nested if statements, but there HAS to be a simpler way. There will be lots of hands in this document, so I would like to make it as simple and easy as possible.
View 4 Replies
View Related
Jul 7, 2014
Let's say I have a workbook with 7 worksheets named, for example, "Instruction", "Begin", "Worksheet 1", "Worksheet 2", "Worksheet 3", "End", and "Data". (in that order)
What I want to do is run a macro to go to whatever worksheet that is in between "Begin" and "End" and copy, for example, cells $C$1:$D$10; then paste as formula into worksheet "Data" starting from cell C1 and then down a list (i.e., copied cells from "Worksheet 1" get pasted as formula into "Data" cells C1:D10; then copied cells from "Worksheet 2" get pasted as formula into "Data" cells C11:D20, and so on and so forth).
But if I were to add more worksheets (e.g., "Recipe" and "ToDo") positioned in between "Begin" and "End" and run the macro again, it'll either 1) re-copy all the formulas from the included worksheets back into "Data" including the formulas from the newly added/placed worksheets or 2) it'll add the formulas from the newly added/placed worksheets and paste into "Data" at the end of the list.
Can create the macro to run based on the position of worksheet, and not based on the name of worksheet, since ultimately there will probably be over 10 worksheets between "Begin" and "End".
View 4 Replies
View Related
Jul 24, 2014
i have one Column that is 41 rows and contains the words Yes Or No (N2:N41). I have second Coloumn with names in every cell (A2:A41).
Now i need to get in a second sheet all the names that are on the same line as Yes.
View 8 Replies
View Related
Dec 15, 2008
Using two columns of data:
Column A with a list of first names sorted by first name, Column B with a different integer value for every cell in Column A. How do I write a formula that sums the values contained in Column B for each unique first name in Column A? The formula needs to identify where first name changes in Column A as there are different numbers of entries in Column A for each first name. (Note: I do not wish to use sub-totals, as I want to copy/paste values back into the results column and use sort to generate a new lookup table)
Have attached an example of the two columns I would like to test. Ideally I would like to see the results in a third adjacent column with the sum displayed only where a new first name appears in first column (the cells would be blank in the third column when the first name is the same as the row above).
View 8 Replies
View Related
Feb 12, 2009
See the attached spreadsheet. I have people's names in cells A2:A5. These names have a rank value in cells B2:B5. What I would like to do is create a formula that sorts/orders the names in cells A2:A5 according to the rank in cells B2:B5. The results I would like to achieve are shown in A8:A11. I am aware of the "Sort" function in the "Data" drop down menu, but need to leave cells A1:B5 as they are.
View 2 Replies
View Related
Mar 18, 2008
I have a huge workbook with lots of sheets. I'm an Instructor and I use the workbook for all my students records. The first sheet has all the students names. The second sheet has a statment that only females have to sign. On the first sheet column A is "First Name", column B is "Middle Initial", column C is "Last Name" and column D is "gender".
I would like to be able to have all the Female names automatically inserted on the second sheet into the "name" cell. The concatenate formula will probably be necessary as the names will have to be put together (Last name, MI First Name). I have a maximum of 12 students so I have created 12 individual statments so that if there are 12 females their names will be inserted into each name cell for each statement. I can send or post the entire workbook if necessary.
View 10 Replies
View Related
Apr 15, 2014
Reference the attached excel audit example. Column F highlighted in Yellow is finding the difference between "Authorized Post Allow" D9 "Post Allowance" B9 . The Column location of the two Columns changes on each audit. This means I have to manually fix the formula in Column F every time. Is there a formula that would find the difference between these two columns based on their names "Post Allowance" and "Authorized Post Allow" preventing me from manully fixing the formula.
View 2 Replies
View Related
Oct 19, 2009
I need the names of students from Summative tab in the correct box based on how many question they got correct. (Q 27-32, (0-1, below, 2-3, Average, 4-5, Above Average, 6, Excellent Performance. )
I have attached a file.
View 8 Replies
View Related
Feb 4, 2010
How can I sum values with names based on a number prefix? For example, in the attached sheet, how can i sum all values that have the prefix 4.10.02.xxxx?
View 2 Replies
View Related
Jan 27, 2013
I am a small business owner with little experience in Excel and I have been trying to set up a worksheet that will organize my products and finances. I organize sets of various products and sell them in bulk to customers. I was trying to recreate this in Excel so I can figure out the total price for each of these sets and easily edit them.
What I've been trying to do is to take a list of the items and assign a price to each of them. This is on a sheet titled "Prices." Here's an example:
prices.jpg
On a separate sheet titled "Sets," I created lists of items included in each particular set. To easily add or change the products I used drop down menus.
sets.jpg
I would like for the sum of the price of all the items listed under each set to add up automatically in the "Total" row. For example, in cell B7 it should display the number 10. Here is a list of the following formulas I've tried:
[Code] ........
View 2 Replies
View Related
Jun 9, 2009
I would really appreciate if someone could help me with the following problem:
I wrote this code, but for some reason it doesn't work. I am completely new to VBA and have no idea what went wrong:
View 14 Replies
View Related
Sep 19, 2013
I have created 70 "templates" in my workbook. I would like to change the name of each template to the name contained on a list in the "Data" sheet. The list starts at a4, and may have 70 or more names. I want to cycle through each name, place it in cell c8 of the template, rename the template with the same name, then move on to the next name, rename the next template, place name in cell c8, and so forth. So far I have this, which creates the "template" and renames it according to the list, but it doesn't insert the name into cell C8 of each new sheet.
Sub NewSheets()
Dim I As Integer
Dim ws As Worksheet
Dim sh As Worksheet
Set ws = Sheets("Template")
[Code] ........
View 3 Replies
View Related
Sep 17, 2007
Spreadsheet consists of the following:
Worksheet 1
B10 : B49 = Names
AO10 : AO49 = Amount
Worksheet 2
I want it list the Names only from highest to the lowest from worksheet 1. If two Names have the same total then I want it in Alphabetical order towards who will be listed first.
Example:
Worksheet 1
B10 Sam AO10 10
B11 John AO11 14
B12 george AO12 16
B13 Steve AO13 9
B14 Carlos AO14 9
Worksheet 2
george
John
Sam
Carlos
Steve
View 9 Replies
View Related
Mar 20, 2009
I have a long list of names and right now I have to scroll down the list like crazy to get to some of the letters (xyz for example).
I really wanted to setup a clickable row or column that has one letter in each row or column, but I'm not sure I can do that as the sheet is already formatted. But I could just use one cell where I could click in it and type a letter which would jump me to the portion of the list that starts with that letter.
or I guess maybe something that pops up a requestor for me to enter the letter.
I found some stuff that was close on here that had some of the functionality I needed, but I couldn't piece it together.
I'm trying to go through David's books, but I haven't had a lot of time and I'm not getting some of the basics.
View 9 Replies
View Related
Dec 19, 2006
I made one file with 13 sheets.
sheet1 tab name is : MAIN
and other sheet tab name like following
2. xyz-Sales
3. xyz-Rev
4. xyz-SSN
5. xyz-ddn
6. abc-Sales
7. abc-Rev
8. abc-ddn
9. abc-ssn
10. ddd-sales
11. ddd-Rev
12. ddd-ssn
13. ddd-ddn
In Main sheet There are 3 buttons
1 . XYZ
2. abc
3. ddd
when user press on xyz button then only xyz sheets (like sheet 2 to 5) are shows to user and other sheets are very hide
if user press abc button then only abc sheets (like sheet 6 to 9) are shows to user and other sheets are very hide
i don't want to use
Sheet2.Visible = xlSheetVeryHidden
i want to use finde xyz sheet tab name and shows and other are hide.
View 9 Replies
View Related
Apr 5, 2007
I have an excel sheet which displays the data in the following format:
(See image attachment). This is just an example of the way the master list is formatted. The master list has over 18,000 lines. Basically, there's a project name, resource name, and number of hours worked for the project within that work week. I've been trying to figure out a way to create a formula or macro to search all these names and add up the hours of the duplicates.
I need excel to search this list of names and total up all hours for each matching name and place each total value on another sheet. For example, Ken Craver has 8 occurances within the week of 1/21/2007 (from project 1 and 25) and his total hours = 31. So the value 31 should be calculated because all the hours associated with Ken Craver add up to 31. Then excel should scan another sheet which has a list of resource names and once it has found the matching name on the other sheet, it will place the value 31 in the column next to the name.
View 4 Replies
View Related
Mar 28, 2008
I'm trying to create a macro which will run through every salesperson in a list, and copy and paste their product and client data (from separate sheets) to a template sheet. The template sheet will then by copied to the Salesgroup workbook, and renamed to the salesperson's name. Then the macro should go back to the original workbook and do it all again for the next salesperson, putting them into the same workbook.
The problem I'm having is that the references to the Template worksheet seem to be being messed up by copying the template sheet to the new workbook, and it then ends up creating all the worksheets, but it stops taking the data from the data sheets, and ends up copying the previous worksheet and pasting that into the new copied template, which means that I get the template multiple times across the same sheet.
Sub SalesSheets()
Dim Salesbook As Workbook
Dim NewBook As Workbook
Dim SalesDataSheet As Worksheet
Dim ClientDataSheet As Worksheet
Dim SalespersonListSheet As Worksheet
Dim Template As Worksheet
Dim rRange As Range
Dim rCell As Range
Dim strText As String
Dim SalesGroup As String
Application.DisplayAlerts = False
Application. ScreenUpdating = False
Set Salesbook = Application.Workbooks("2008 Salesperson TEST.xls") 'change filename here
Set Template = Salesbook.Worksheets("Salesperson Template")
Set SalesDataSheet = Salesbook.Worksheets("SP product YoY")
Set ClientDataSheet = Salesbook.Worksheets("Client YoY")
Set SalespersonListSheet = Salesbook.Worksheets("Salesperson List")..........................
View 3 Replies
View Related
Aug 5, 2008
I want to make several charts with dynamic ranges. To do that, I wrote the offset functions I need in cells. I've attached a sample spreadsheet. I want to have a macro so that when I run it, it will take the contents of the active cell, insert a name called those contents, and make it refer to the cell 4 to the left of it.
In the spreadsheet, I'd like to be able to click on E2 (sentdate1), create a name called sentdate1, and make its value A2, or =OFFSET('Sentiment'!$A$3,0,0,COUNT('Sentiment'!$A:$A),1). Here's what I have so far.
Sub Macro8()
ActiveCell.Offset(0, -4).Range("A1").Select
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveWorkbook.Names.Add Name:="sentdate1", RefersToR1C1:= _
"=OFFSET(Sentiment!R3C1,0,0,COUNT(Sentiment!C1),1)"
End Sub
Auto Merged Post Until 24 Hrs Passes;Here's the sample worksheet.
View 6 Replies
View Related
Oct 25, 2012
I am trying to produce numerous workbooks based on a filtered name. I will attach a sample spreadsheet that has the data.
On the sheet we have engineer names. I basically want to filter them (not difficult with a macro) but then to copy the results to a new spreadsheet and save the workbook as the engineers name (ie J. Bloggs has 5 jobs so they are filtered and the results are dumped into a new workbook and then saved as J. Bloggs.xls). This will happen for all engineers.
I have though about doing it as a macro and I think that would give me the end result but we have around 20 engineers and these can sometimes go up and down. Is there anyway to do this automatically?
Test Cost report 1.xls
View 6 Replies
View Related
Jul 6, 2014
I have a column containing names, blank spaces, numbers, a bunch of miscellaneous things. However all I want to do is extract only names, into another sheet, and not the numbers, blank spaces, etc. Youi'll see what I mean when you open the attachment. Offset isn't an option because the names don't appear in the column in an organized fashion.
Dummy workbook.xlsx
View 3 Replies
View Related
Dec 13, 2013
I could really use some excel function. Within my office, we work with several hundred files. Each employee is assigned files based on the last two digits of the file number. What I need is a way to identify what file is assigned to which employee based on the term digits of the file.
So for example, I have the following list of files:
1002856101
22781721
1044863815
1008799064
1044779765
1006511115
1007641804
0729939256
5303486020
8364709
0014094759
0019921519
8172717
I'm able to do a formula to get the term digits (meaning the last two numbers), but i'd like to have another column that can put names based on the term digit column. For example, Tom might work 00-04, Sally works 05-09, Greg works 10-15, Lucy works 16-21.. etc
I came across the below IF formula that is exactly what i need, except it only works for two associates and not the multiple that i need.. but it looks to be a good starting point nonetheless.
------------------------------
=IF(C2<50,"Sheryl","Lisa"). You should enclose Sheryl and Lisa with quotation marks as these are string values.
You can also use (if A2 is where the Loan # is):
=IF(Right(A2,2)*1<50,"Sheryl","Lisa")
The formula will acquire the last 2 digits of the loan and check it if it's for Sheryl's or Lisa's.
-----------------------------
View 3 Replies
View Related
