AVERAGEIFS Formula With A Range?
Aug 11, 2014
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
View 3 Replies
ADVERTISEMENT
Nov 27, 2012
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.
View 1 Replies
View Related
Jan 18, 2014
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?
View 4 Replies
View Related
Feb 7, 2014
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 Related
May 18, 2014
Macro for AverageIFS, with multiple criteria in the same criteria range....
View 9 Replies
View Related
Apr 2, 2014
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.
View 3 Replies
View Related
Aug 5, 2009
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 .
View 3 Replies
View Related
Dec 4, 2008
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.
View 9 Replies
View Related
May 5, 2014
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.
View 2 Replies
View Related
Nov 21, 2013
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.
View 7 Replies
View Related
Feb 5, 2014
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.
View 2 Replies
View Related
Oct 25, 2008
How 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.]
View 14 Replies
View Related
Feb 9, 2010
I need a formula that will return the minimum value from a column range that flexs without having to manually go in an change row references. There is a blank row between each section of data in order to separate info. As an example:....
Need to formula to flex to include rows 5 thru 7 in the first section but expand to include rows 9 thru 13 in the second section. =MIN(A5.A7) works but how do I get next section to flex to =MIN(A9.A13) without manually changing the cell references? There is always a blank row in between the sections to separate.
View 5 Replies
View Related
Jan 2, 2014
I like to create "templates" for files that I work with on a reoccuring basis, just to make things simpler. The current template I am working on tracks items processed by day and is used for two reporting purposes; 1 totals the pay ending items processed so I have the dates laid out based on the 2 week period with formulas for that. The 2nd way it is reported is by the month. Since this is a template, only the first day of the first pay period for a calendar year needs to be entered and the rest of the dates populate based on that date. What I would now like to do is add columns for January - December monthly totals using a formula that will read something like "if column A (where the date is) contains 1/1/2014 - 1/31/2014, then sum up column L" and than copy that formula for the remaining months.
View 8 Replies
View Related
Aug 10, 2008
I have a named range that expands and contracts based upon the amount of data that is in some column. Call it AllData_UsedRange.
I have another named range that actually refers to a range. Call it AllData.
Column A
Row2 56
Row3 44
Row4 65
AllData is a named range that refers to the range A2:A65536
AllData_UsedRange refers to A2:A4 by way of this formula.
=OFFSET(AllData,0,0,COUNTA(AllData))
How to I obtain an address of AllData_UsedRange in VBA code?
These do not work...
ThisWorkbook.Names("AllData_UsedRange").RefersToRange.Address
Evaluate(ThisWorkbook.Names("AllData_UsedRange"))
View 9 Replies
View Related
Sep 8, 2006
I am trying to create a graph for a range of data that updates monthly (adding an extra month each time). I wanted the graph source data to update automatically each time the data is refreshed so used an OFFSET formula to identify a named range. I then point the graph to the named range as the source data.
When I enter the range as the source data the graph picks it up. However, when I re-enter the source data option on the graph it has converted the named range into a cell written range (ie. replaces "=QUALITY" with "='Front page'!$B$7:$J$10" - which therefore will not update when the range increases.
View 4 Replies
View Related
Apr 1, 2009
I have a range of values that are not a constant length. I want a macro that will calculate the range and fill with a formula using the new range. In this example, I need a formula to look at the values in range f464:f471 and calculate the number of values greater than $1000. Column B is a salesman identifier. I can do this manually, but it takes me about 1/2 hour to complete. At every change in "B", I need to place this formula in the open, blank cell in "E" such as highlighted below.
week39
ABCDEF45939SCHTERR-WIEDEMANNDCCDIST-MUSCHANY046039SCHTERR-WIEDEMANNDCCDIST-MUSCHANY046139SCHTERR-WIEDEMANNDCCDIST-MUSCHANY046239SCHTERR-WIEDEMANNDCCDIST-MUSCHANY0463 SCH Total 14042.2146439SCITERR-SULLIVANDCCDIST-MUSCHANY15587.1546539SCITERR-SULLIVANDCCDIST-MUSCHANY7217.2246639SCITERR-SULLIVANDCCDIST-MUSCHANY5316.6246739SCITERR-SULLIVANDCCDIST-MUSCHANY2720.6946839SCITERR-SULLIVANDCCDIST-MUSCHANY520.446939SCITERR-SULLIVANDCCDIST-MUSCHANY195.7147039SCITERR-SULLIVANDCCDIST-MUSCHANY047139SCITERR-SULLIVANDCCDIST-MUSCHANY0472 SCI Total 31557.7947339SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY4190.7147439SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY3219.7647539SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY3187.147639SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY2209.3747739SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY2132.7547839SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY2034.56
Spreadsheet FormulasCellFormulaF463=SUBTOTAL(9,F435:F462)F472=SUBTOTAL(9,F464:F471)
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
View 9 Replies
View Related
Jun 3, 2014
I am trying to find out how many weeks our current inventory will support our sales. I am trying to research formulas that will do this, and coming back with things like OFFSET, MATCH, INDEX but am not certain the best way to put them together to get what I need. I use excel daily, but this is a bit mroe advanced than I am used to
Mini.xlsx
Starting in cell B4, I would like to count how many weeks of Demand can be covered by the specific Available On Hands in row 3 for that week, put the number of "Weeks Covered" into cell B2, and then fill over to the right in row 2. Right now, the values in row 2 are from my own manual calculations, but I would like a dynamic formula that will sum up the values in row 4 up to (but not greater than) the value in B3, give the count of cells that reached that sum (or even better with decimals to show the percentage covered), which I will copy over into B3:B13. Not sure if that makes sense, or if I can explain in a better way. The yellow cells are what I am trying to create a formula for and am currently stuck.
View 6 Replies
View Related
Mar 17, 2014
I have a row data corresponding to the measuring of load cell per min and I need to average the values per hour. So I have a column B for the date (from 1-01-2013 to 31-01-2013, column C for the time (0:00:00 to 23:59:00), and D de values per min I want to average. I have to do the same for the rest of the month of 2013 (February, ...., December).
I would like to know if there is a way to create a formula to calculate the average of the first 60 values (to get the average of the first hour of 1-01-2013), and then copy it to get the average of the following 60 (average of the second hour in 1-01-2013) and so on.
If there is no way to do it, I would like to know if I can do it using functions like average, match, index, offset, what would be the best match of those functions.
I also tried it by doing the analysis in another tab and using the function "averageifs" with two criterias: one for the date (example 01-01-2013) and another one for the hour (example 0:00:00), but it didnt work, it show error: #value. I inserted an extra column in the data tab with just the hour (example 0:00:00) in front of the corresponding column with (example 0:01:00, 00:02:00, etc)
Equation I used for this:
=AVERAGEIFS('Data (min)'!D$6:D$43206,'Data (min)'!$A$6:$A$44646,A6,'Data (min)'!$B$6:$B$44646,B6)
=AVERAGEIFS(TAB AND COLUMN WHERE THE RAW DATA IS,RANGE OF CRITERIA 1,CRITERIA 1,RANGE CRITERIA 2,CRITERIA 2)
View 2 Replies
View Related
Mar 5, 2014
Needed a formula in sheet1 in column D where if in sheet2 in column J when the cell value is in the range from 50 to 100 then the cell value in sheet1 column D becomes 0.
The range from 50 to 100 means starting from 50 it can be any one number till 100.
The formula will be pulled down in D column upto the required length.
View 14 Replies
View Related
Dec 26, 2007
=SUMPRODUCT(('DVP Trades'!$B$2:$B$1000="LOAN")*('DVP Trades'!$C$2:$C$1000=A31)*('DVP Trades'!$A$2:$A$1000=FUNDS_SS)*('DVP Trades'!$F$2:$F$1000))
Maybe this is impossible, someone let me know.
I am using the above formula. I am trying to use range names where its bolded in place of repeating different cells. However my the result of the formula is N/A.
The range name of "Funds_SS" is a group of 12 funds. What I want to do is take the total of anything that is a Loan in column B, with a given client in column A, if it occurs in any of the funds in range name FUNDS_SS, and the value of the loan is in column F. Does anyone know what I am talking about? Can it not be done? I don't know if I can explain it any better.
View 9 Replies
View Related
May 22, 2009
I m writing a formula so that if the value in a cell is within a range, it will have a value other than 0.
The formula I'm working on is:
View 2 Replies
View Related
Mar 20, 2007
I'm trying to refine my code a bit so I don't have to re-write when my list data changes. How would this code be modified to use a dynamic range name? I have created the range name on the other worksheet using this code in the refers to box of the define name drop down.
View 14 Replies
View Related
Nov 2, 2007
In a cell named item I have a text "unit price".
In a cell named unitprice I have a number 3.45.
In a cell named linetext I have a text "=concatenate(item;unitprice)"
Is it possible to use range.formula to do this using VBA? Is there another way to do this?
I need this type of solution because the formulas in linetext will change based in other cells.
View 14 Replies
View Related
Oct 7, 2009
I have a range of cells, D14:IG14, that may or may not have the words "Full Redemption" in each cell. If any of the cells has full redemption, I want "full" to appear in cell II14.
I'd like to do this via formula, but am having trouble with the fact that it's a range I want to look it.
View 9 Replies
View Related
Oct 9, 2009
A1: =""
A2: =Text(2,0)
I try =SUMPRODUCT(--(A1:A2)) and CSE Formula =SUM(VALUE(A1:A2)) the result is #VALUE!
What is the formula to sum range A1:A2 which the result is 2
View 9 Replies
View Related
May 10, 2007
I'm trying to develop an automatical update function for a workbook. I have a workbook, in which I want to updates some formulas. In this regard, I've set up an Add-In. The problem is that when using *. range.formula an error occurs. I understand why this error occurs, but can not mangage to find a solution to solve my problem. The code I'm using follows:
Public Sub DoToAll()
Dim ws As Worksheet
Application. ScreenUpdating = False
For Each ws In Worksheets
ws.Range("B15").Formula = "=IF(D12<>"Age","",IF(AND(YEAR(MID(D9,14,10))=YEAR(TODAY()),MONTH(TODAY())>=7)=TRUE,"Do Nothing",IF(MONTH(TODAY())>=7, DATE(YEAR(TODAY())+1,1,1),IF(MONTH(TODAY())<=6,DATE(YEAR(TODAY()),7,1))))) "
Next
Application.ScreenUpdating = True
End Sub
I have several similar ws.range.formula codes I want to use.
Does anybody know how to insert such formulas via VBA?
View 4 Replies
View Related
Nov 20, 2013
Coming from this formula in vba :
VB:
Dim sh As Worksheet
Set sh = Worksheets("Data helper")
Set Rng = sh.Range("C2:C" & sh.Range("C" & sh.Rows.Count).End(xlUp).row)
I want to translate this to a workbook dependent formula, but I'm not sure what I'm doing wrong
VB:
Dim book1 As Workbook
Dim sh As Worksheet
Dim srchRange As range '(added this line in edit : still not working)
'Set some variables:
[Code] ....
View 9 Replies
View Related
Jan 12, 2014
Please refer to attached sheet.
I have number 10000 to 10200 in column D.
I need to use IF Statement and add corresponding number in Column A as follows.
IF D2 = 10000,10006,10012,10018,10024 and so on then A2=20002
IF D2 = 10002,10008,10014,10020,10026 and so on then A2=20003
IF D2 = 10004,10010,10016,10022,10028 and so on then A2=20004
(Basically there is increment of 6)
Once i have formula for A2 then i can copy down the column.
Book1.xlsx
View 8 Replies
View Related
Jan 27, 2014
how to make make cell D4 dependent on the value in cell D3. So if cell D3 = 10 (it's max), then cell D4 could equal 0-10. I want to use this in conjunction with Solver so that D4 is a variable, but can't be greater than zero until D3 is maxed out. I've already tried different formulas, but I'm not very good at writing them and data validation didn't seem to work either.
View 2 Replies
View Related
