I want to get the average of numerical values in 3 ranges , eg A2:A15, A20:A30, A35:A45, ">0". The intervening cells contain other data, so the ranges are not contiguous. I think AVERAGEIFS is the function to use.
I have found my way through nested IF and other functions so am reasonable able to trouble shoot a function .
I have data that is organized in a table and has many columns that I'm interested in calculating their averages. The first two columns contain the criteria range; say DATE and NAME.
So I want to generated several report where every column of the data table corresponds one report. The report template is organized such that NAMES are on the rows and DATES are on the columns. I used to highlight the body of the report and write a formula such as:
AVERAGEIFS(Table1[Success Rate],Table1[NAME],$A8,Table1[Date],E$1)
This formula worked well and all I had to do was copy it to 17 other reports adjacent to each other and change name of the column that I want to find its average. Obviously this is very time consuming. Also the rows template are not always the same.
I have written a some code that recreates the template for me based on the number of names in the row. Everything is working fine. The only problem is how to write the formula above in VBA. I already have the names stored in separate arrays.
I can't seem to get the OR( operator to work in conjunction with AVERAGEIFS(. In the example below, Quality is the name of the table and Call Rating is my average range. Quality[Manager] is Criteria 1 range and I would like it to use only Manager 1 and Manager 3.
=AVERAGEIFS(Quality[Call Rating],Quality[Manager],OR("=Manager1","=Manager3"))
I thought that an OR( operator would work perfectly, but no matter how I vary the syntax it always give me a DIV/0 error.
I've been trying to use the averageifs formula to determine the aveage FTE needed for 1st, 2nd, 3rd, 4th and years beyond for different client types. Column A displays the client type, Column B the number of years live and Column C is I've come up with the following formula but I can't seem to get it to work right.
=AVERAGEIFS(C2:C16,A2:A16,"C",B2:B16,">=0",B2:B16,"<1")
ClientYearsFTE
C3.612.49
P-0.00
P4.610.16
M0.940.00
M3.280.57
P0.940.00
C-0.06
C3.610.51
C3.450.29
P3.124.18
C4.121.58
C5.371.25
M4.120.63
P7.290.36
M2.611.27
I have large data sheets were I need to calculate the average value only for certain team members performing certain service call types. There are several different teams involved. The results need to be displayed in a interactive dashboard were the user can choose the team to see that teams result.
So I need to build a formula that uses a named range that contains a list of team members. I don't want to use named ranges in the data sheet itself.
The data sheet has one row for each service call.
-Column A has the team member ie: 20TE01, 20TE15, 20TE78 ect ect.
-Column B has the service call type ie:, M, A, S, O, ect, ect.
-Column C has the value I need to average.
Named Range is "teamA" contains 20TE01 and 20TE15 as an example
I can use this to count the call type "M" by team as an example =SUMPRODUCT(COUNTIFS(A:A,teama,B:B,"M"))
What formula will provide me the average of a particular call type by team?
I am using the following formula and it is working fine. Is there a better way to do so?
=IFERROR(AVERAGEIFS(INDIRECT("Data!$U"&$B$1&":$U"&$A$1),INDIRECT("Data!$A"&$B$1&":$A"&$A$1),">="&D$4,INDIRECT("Data!$A"&$B$1&":$A"&$A$1),"<="&D$5,INDIRECT("Data!$U"&$B$1&":$U"&$A$1),"<>0"),"-")
I am looking for a better way as I have to replicate the same formula in 100 columns and in 60 rows. Where every row use a different target column (like the above mentioned is using column U.
Indirect function gets the values from A1 and B1 cells (Start and end of column because end value keeps changing with data updates)
AverageIfs get average of a particular value between two dates mentioned on top.
I'm trying to use AVERAGEIFS to take the average of the values in a column that are > a low cutoff value and < a high cutoff value.
In my sheet, the cutoff values are calculated in two cells, and I need to refer to those values in my AVERAGEIFS formula.
Here's what I've tried so far:
=AVERAGEIFS(H2:H81,H2:H81,">B10",H2:H81,"<B11")
This gives me a #DIV/0! error.
If I replace the cell references in the criteria clauses (">B10" and "<B11") I get the correct answer, so I guess it's not handling these sections correctly.
So I have 3 columns in this example. I want to average column K if certain criteria are met in Columns G & I.
Columns G & I have text values and Column K is a number value.
Does that make sense?
If I'm simply counting how many meet both criteria, I have this: =COUNTIFS(Data!I2:I282,"Waiting",Data!G2:G282,"Sev1")
but I want to now take those and average the values for Column K.
I have 3 columns of information. Column A will be between 1-6, Column B is between 1-10, Column C is the results that I need averaged. I need the formula to give me the average of Column C of all 1's in Column B that are even numbers in Column A. I have been looking on forums, and can find multiple criteria for ands, but can't see where I can do an and /or. I could do Column B = 1, Column A =2, but I need it to be OR =4 OR =6. I don't see any easy answer in excel for even/odds.
View 8 Replies View RelatedHow to compute the realised Profit or Loss for each of the shares transacted for the year ended 31 December 2008 based on average cost of each share.
using AVERAGEIF, if appropriate. [Pls refer to the attachment.]
Using Office 2010, I am trying to do a averageifs formula for a specified date range. I can get it working by specifying the date range in the formula itself, however when "pointing" the formula to a specific cell with a date in it, the formula gives me a div/0 error.
Formula that works is:
=AVERAGEIFS('UHP Weld Data'!M:M,'UHP Weld Data'!B:B,"08/29/2012")
What I need is a version of: (currently not working)
=AVERAGEIFS('UHP Weld Data'!M:M,'UHP Weld Data'!B:B,"H1")
The date range will constantly change as I want it to show me the past 30 days only.
Macro for AverageIFS, with multiple criteria in the same criteria range....
View 9 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 Relateddefinitions for static ranges and dynamic ranges?
View 9 Replies View RelatedI have two vertical ranges that I need summarized into 2 adjacent vertical ranges.
"
A B C D
| SUMMARY
model qty| modelqty
1 4.12922.0000| 4.12952.2000
2 2.000012.1250| 2.000025.1250
3 4.12929.0000| 318.0000
4 318.0000|
5 4.1291.2000|
6 213.0000|
"
A1:A6 is my SKU's model number B1:B6 is my inventory C1:C6 should contain formulas that result in a summary of the models D1:D6 should contain formulas that result in a sum of the inventory count for each model
In the attached file, I have variable range in column A:B, column C:D and in column E:F
I want a macro to do the following:
Start with sheet "A", select the available range in column A
then copy and paste in the sheet "B" but with all the cell values added with the value in H1.
Then in sheet A, simply copy the available range in column B and paste it in sheet B
Do the same until column F in sheet A. Pastespecial if it is odd column. simple past it is even column.
I know the macro code for the simple paste. But I am struggling with the paste special code.
I have written this macro to convert into a csv file to run for all defined named ranges in the activesheet. It run jst perfect when I hit SAVE button and it creates that many different CSV files for each named range.
However I am trying to use same macro in the another file and the problem I am facing is there a lot more named ranges and I want to run the macro for only selected NAMED RANGE. In this case 2 Named Range / 24 Named range.
What part of code do I need to change and to what to make it work for just 2 named ranges ?
I am trying to locate a named range cell in my VBA macro, but find that VBA will only allow me to specify the actual cell eg. Range("E2").
The piece of code I am using below is
Sheets("sheetname").Select
Range("E2").End(xlDown).Offset(1, 0).Select
I would like to use
Range("namerange").End(xlDown).Offset(1, 0).Select
Do i need to Dim or specify the name range in any particular way.
I'm trying to get a formula to give and yes or no answer if a cell is between two numbers.
Example: Cell AB9 = 100.2 in cell AB11 would be a formula that would answer "Yes" because it is between 96.8 and 100.4. Any number outside this range would answer "No."
I am trying to make a UDF that searches for a header, grabs everything under the header, and pulls it somewhere else. My UDF has three parameters:
1) Output_Range: the named range where the parameters will be pulled to
2) Header: the header to search for in order to copy the data underneath it
3) WorkbookName: the name of the workbook to search in
It looks like this:
VB:
Function LoadParameters(Output_Range As Range, Header, WorkbookName As String)
MyTimer = Timer
'Defining the variables.
Dim HeaderCell, HeaderCellEnd, HeaderRange, Output_Range
[Code]....
I'm attempting to do a sumproduct with different dimensioned ranges. I've read multiple posts that say that all ranges in a sumproduct must have the same dimensions. I've tried different commands (index/match/lookup, etc) with no luck. Perhaps someone can come up with a solution for me....
View 7 Replies View RelatedI have set following ranges:
[Code] ....
I use above rngData1 as follows:
[Code] ....
How can I use all above ranges in For Each statement?
For example For Each rngRow In .Range(rngData1+rngData2+rngData3+rngData4).Rows
I'm trying to create a Formula where the User has to select 2 Ranges and then a calculation is done for each corresponding cell.
I thought about using arrays or ranges but I'm quite lost.
[Code] ....
My Second approach would be to use Arrays
[Code] .....
This is what I thought about so far but it does not work. I guess because I have to Idea how to tell VBA to use the Interestrates Range for each corresponding cell.
In sheet x the range("d2:d20") will change from time to time. I've created a button that should copy this range("d2:d20") to sheet called summary and there to the next empty column. So the variable lstcol (dim = long) is the last empty column in summary. I'm definitely not using properly the range method.
View 8 Replies View RelatedI have 2 ranges with values, and I want to use countif to see of there's a same value in each of these ranges.
I would like to perform a calculation with each cell in a range from 2 worksheets and place the result in a range on a third worksheet.
I thought I could use the For Each Cell in Range construct but I don't see how to reference the two ranges in a nested pair of For loops. I am sure there must be an simple/elegant way to do this.
I have a macro that has automated 95% of a task but one thing is still evading me! If I use the keys then this is what I do:
Select cell C192
Select Shift and R-Arrow so that C192 and D192 are selected
Select Ctrl, Shift and Up Arrow so that C1:D192 are selected
Select Shift and Down Arrow so that C2:D192 are selected
I have some named ranges that refer to 5 pieces of data organized into a row. For example, the name MyNamedRange might refer to $C$5:$C$10.
I am trying to loop through each column and get the values in MyNamedRange, then change corresponding values in a different named range. However, when I try to use Offset to access the subsequent columns of MyNamedRange, it doesn't work. It only gets the value of the first column right, the rest return <EMPTY>.
Sample .......
I have two Ranges, which I view as Arrays or Matrices, I want to subtract the two (which do not overlap or union in any way) to get a new third range of the differene. This new range should be the same size, and can be considered a difference of each cell in the range. Note both ranges are already identical in size.
Any help solving this, searched up and down for hours.
Note; Im trying to avoid looping, as this might become incredibly inefficient if the ranges grow to big, looking for alternative that is faster, someone said maybe excel.evaluate
1. insert a new procedure and put this code into a for loop:
View 2 Replies View Related