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.
1st problem concerns entering data as text and it being converted to time. I have found the formula 00:00 which does an excellant job of converting. However, when I want to subtract, (=a2-a1) for example, it treats the time as a number. Sometimes it works, but if the hours are different, say 23:30- 22:10, it will subtract 2330-2210! Is there a way to make this work?
2nd problem, related to the first is when I try to avg a column of times, I get a similar effect.
Is there a " Sum" function that can be used for a cumulative series of subtractions?
For example, if I have 10, 12 and 15 in 3 respective cells, and use the sum function upon them, it will return 37.
However, I want to say 10 minus 12 minus 15, and thus return -17.
I have a spreadsheet that has two different data sources (i.e., A and B). The amounts of these data sources (i.e., the number of columns) varies from sheet to sheet. I have to calculate the averages of these data sources independently and together. Because this data is spread across twenty or more spreadsheets, these calculations can be time-consuming. I want to do the following:
1. Locate the last occurrence of the first data source "A" in row and then insert a column after that cell.
2. In that cell, I want to get the average of each data source for each row of data (i.e., there are always 19 rows of data).
I want to do the same two steps for the second data source "B". Then, I want to insert a column after the "B" average and this column will be used to get an average of each row of data from A and B together. Please keep in mind that the number of data sources for A and B varies from sheet to sheet.
So far, I am working on code to try to " find" the text in a range (i.e., find the last instance of "A"), but I cannot figure out how to get it to get it to stop at the last occurrence and then insert a column. I have some ideas about how to calculate the average, but any of yours are much appreciated.
Also, the row with the type of data (i.e., A or B) is named because this function is part of a larger macro. Therefore, it is relatively easy to get to it.
I have attached a version of the file that displays how I want it to look.
The code I have thus far is:
Cells.Find(What:=A, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
I'm looking for a formula that acts the same way as the subtotal formula (revises if rows are hidden and only adds visible rows). Is there anyway to get this removal of hidden rows but for subtraction?
View 3 Replies View RelatedI 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?
I am attempting to perform a cumulative subtraction function that starts over again when the value in a column changes. I have a list that is sorted by a column for item number. The other two columns show the amount we have in stock and the amount being ordered against that stock. The order of rows IS relevant and cannot be changed.
Sample output is attached, with the "given" data outlined in a bold border. The only way I have figured out to do this is to nest a whole lot of IF statements in a formula, which is not very elegant or easy to do, and is limited by the number of IF statements that can be nested.
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] .......
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.
I'm trying to calculate the average of every five rows. I have a formula =Average(A1:A5) in cell B1. When I do an autofill in B2, the formula would read =Average(A2:A6). What I actually want it to be is =Average(A6:A10), increments of 5. I don't know what i'm doing wrong. I have also tried selecting all the B rows and doing autofill and that still doesn't work.
View 3 Replies View RelatedI have a list of grade from 2~4 (i have long list but for sample i made it short). I want to get average of all grades one by one as well as by making some groups. Like 2,3, "2~4" (all groups) then "3~4". All the things were going nicely with a drop down menu. But when i put average on grde "3~4" only then the problem occurred. Although I've done my work but still I think there must be some improvements.....
View 6 Replies View RelatedI would like to get a monthly average between the first of the month & the end of month of a handful of percentages collected during the month for individual projects.
View 10 Replies View RelatedE11 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.
I'm trying to figure out a formula that will give me monthly average (per person) on the following report. The problem is that people start at different times and sometimes they don't produce in a given month after they have started. Basically I'm trying to get a monthly average from starting month to the last full month, in this case January.
View 8 Replies View RelatedI need help creating a macro that will search through my excel spreadsheet and for every instance where column A isn't empty it should cut a range of columns from that row and paste them in a different range of columns in the row before it. It should then delete the row that it cut the columns from and keep searching until it has done this for the whole worksheet. I can modify which range of columns are needed, but it has been so long since I've worked with excel macros that I haven't been able to do it.
View 5 Replies View RelatedI would like to create a shortcut to averaging ranges. The ranges vary in size so I want to be able to click on a cell. Press Ctrl + W and automatically average the cells above it. I know how to get the short cuts, I just can't figure out the proper code to be able to average based on different sizes. Let me know if you need a better explanation./
View 3 Replies View RelatedI would like to average data between 0600 and 2200 hours, between two date ranges, such as July 1st and July 31st.
Capture.JPG
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,"
I have created a spreadsheet which creates an average of feedback for trainers in a training company. The form adds up the feedback score into column L of the summary sheet and I have created a summary sheet which I want you use to calculate the average for each trainer.
I have cobbled together an array formula which creates the overallaverage for each trainer based on the named ranges entered via the form.
It looks something like this:
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
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.
How can I use a function similar to the "max Function" using more than 30 numbers?
View 9 Replies View RelatedI am trying to create an IF function and how to create it for multiple ranges. The for the following criteria is what I am trying to work with: If a checking balance is $500 dollars or below return "low", If it is between 500 and 2000 return "Medium", and IF greater than 2000 return "high"
View 2 Replies View RelatedI have used the Networkday Function to get the Working Days in a month excluding the Saturdays and Sundays and also the Holidays as Networkday Function by default excludes the Sat/Sun and any of Regional Holidays. This works absolutely fine, howevr in the realistic scenarion we need to also add any leaves allotted to an employee.
Now if this List of Holidays is entered in a seperate column, how can this be consolidated to get the actual Working Days = Total Days in a Month - ( Sat/Sun + Regional Holidays) - Leaves ). In the below syntax, the range $I$2:$I$4 contains the Regional Holidays and can be defined as a Name= Holidays
NETWORKDAYS(B2,C2,$I$2:$I$4)
NETWORKDAYS(Startdate,EndDate,Holidays)
Can we incorporate something like a double range like this : Leaves without writing it below the Holiday Range, this new Range by using Offset function can be made into a Dynamic Range. < =NETWORKDAYS(Startdate,EndDate,Holidays & Leaves)>
VBA will not allow the worksheet function MMult to be called during the subroutine. The error that flashes is: "Run-time error 1004. Unable to get the MMult property of the WorksheetFunction property"
Worksheets("MatrixOutput"). Range("ak4:di35").Select
Worksheets("MatrixOutput").Range("c4:ah80").Select
XTranspX = Application.WorksheetFunction.MMult("c4:ah80", "ak4:di35")
Worksheets("MatrixOutput").Range("b84:ag115").FormulaArray = XTranspX
I am trying to create a table showing the yearly average occupancy rates per quarter for every year between may 1998 and december 2008 inclusive (for licensed hotels, motels and guest houses, and serviced apartments in Australia; if that interests you).
I have a spreadsheet with a row of data showing the appropriate quarterly results for those dates. Let's say the data for this is displayed A1 through to A44.
For the yearly per quarter averages, let's say they are to go from B1 through to B11, I know I could put, for each year, an average formula with the relevant data range. Finding this to be tedious, I tried to fill-down.
I learnt the hard way however (not being too aware of how excel's grammar ticks) that the fill down function will not work for me. i.e., if B1 contained the average for the data range A1:A4 (the four quarters of 1998), the filled-in B2 would have the data range A2:A5 (i.e. the 2nd quarter of 1998 through to the 3rd quarter of 1999).
I need to use the ADDRESS function to define a range from which an average can be determined. My current function is as follows:
View 3 Replies View RelatedCustom average function. can this be done with Worksheet functions:
View 4 Replies View RelatedI have a data range - C4:C54 and I want an AVERAGE calculated in C56, but I only want a value displayed in C56 when a data entry is put in. Up to 50 data points could be put in anywhere e.g. only 14 values in C16:C30, or 44 values in C6:C50, or the full 50 in C4:C54.
The formula I've been trying is
=IF(ISERROR(C4:C54<>""),AVERAGE(C41:C54),"")
However, when no values are in the range it displays DIV/0! whereas I'd like the cell blank.
How do I perform average for a range and be able to exclude outliers. For example I am taking 100 measurements, some are not measureable and recorded as 9999.9. How do I average 100 values, but exclude all the 9999.9?
View 7 Replies View Related