I am trying to calculate time in the ER, avg. time in the ER, and time before seen in minutes USING the Averageif function. However, in the columns that I am trying to calculate, there are a lot of ######### and 0:00. So I thought that if I use =AVERAGEIF(AL2:AL47,">0") then that would exclude the ######## and the 0:00.
However, it seems that the formulas are not working. For example, one column contains 00:10, 00:04, 00:05, then a lot of ########, and 0:00.
When I take the =AVERAGEIF(AL2:AL47,">0") of this whole colum then it gives me and average of 18:02!!!!
I am trying to use the AVERAGEIF function on the below screenshot. I have a mix of text cells (ignored by default), 0s, and whole numbers from 25-100. For some reason, the value in Q16 is incorrectly averaged. The interesting thing is, I have similar data and function in R16 and it is working as expected.
Q16 =AVERAGEIF(B16:B16:D16:D16:F16:F16:H16:H16:J16:J16:L16:L16:N16:N16,">0") --> expected value of 275/3 (decimal rounding 0 places) is 92[code]....
I currently have this equation: =AVERAGEIF($B$2:$B$900000,">="&L5,$C$2:$C$900000). Where L5 = 100. I'm interested in having the formula averageif B column value is between L5 & L6. Where L6 = 200. So I want it to find all values in B$ that are between >= 100 but < 200, and then average their C$ counterparts.
I need something like AverageIf function that basically takes the B column and averages only the numbers that are not 0Ö I have messed around with this and finally just got frustrated, maybe its because Iím using Excel 2003?
EDIT: Incidentally, I changed AVERAGE() to COUNT(), and it kicked back 9 values instead of 3 (I have one value entered for each team atm). B7 is the name of the department I'm finding the average for. I've got 3 departments in one location, and 6 in another location. I'm trying to add OR() to give me our regional quality averages, and it's not working. I used:
In cells G2 to G57 I have the months from Jan-09 to Aug-13. In H3 to H57 I have the monthly data. In cell k2 I am trying to average all of the December data without individually clicking each cell (Ex: Dec-09, Dec-10, Dec-11, Dec-12). I was thinking of doing Averageif formula like =AVERAGEIF(G2:G57,"Dec*",H2:H57) but I get the dreaded #DIV error.
I'm looking to average the numbers contained within 5 different columns if the corresponding cell in a different column is blank.
Here's what I have currently (with column A being the conditional cells and D:H being what I'm looking to average)
=AVERAGEIF(A3:A26, "", D3:H26)
From what I understand, this function only allows me to average a single column. If this is in fact a limitation of excel and not just of my knowledge, is there a different way to calculate the average of D3:H26 when the corresponding cell in column A is blank?
Is it possible to utilize the AVERAGEIF function for a specific range of values. For Example, instead of ">45", I would be looking to only average the cells that have values between 40 & 60. I've tried several different ways to input that into the function but nothing seems to work.
I have simple Countif and Averageif formulas that I would like to have linked to variable data in cell E6 on the Assumptions (2) tab however, the formula is not taking the cell as the criteria and only taking a hard number when I am using >=.
The example would be: I3 needs to be able to use the data changed in E6 for the >= number instead of the hardkey 29...
I am wanting to populate the "Most Recent" column by selecting the last value in each row between B to F so that I get the following result:
A B C D E F
1 Most Recent 1st 2nd 3rd 4th 5th
I am also wanting the result under Column A to indicate by cell colour red if the value is less than the previous value and by cell colour green if the value is equal to or greater than the previous value.
For example; Cell A2 should be highlighted green because the last value which is D2 is greater than the previous value in C2. While Cell A4 should be highlighted red because the last value which is C4 is less than the previous value in B4.
I do have another question actually. In the Total Sales worksheet, I have a a cell in S2 that I wish to show the max value from the "total sales" colum and then show the first and last name related to that particular row where the max value was. so, I only have =MAX(O2:O112), but I want to show the Name associated with the max and not just the $ amount.
So in S2 I want ---> John Doe and not $512.50, but I can only think of =IF(MAX(O2:O112), $B2+$B3, 0) becase I want both the first and last name. I used this formula with only $B2, but it just returned the first name in the B2 field
I am trying to create a formula that will search a row left to right for the first instance of zero and return the corresponding column label. I had this working for a smaller table using a couple of hidden columns with nested ifs however the new data set is approximately 25 columns wide and it will get very messy using the same approach. See attached sample with desired results.
I have just decided to dable in the 'VLOOKUP' function. I have looked at a few examples on the net and I have tried to replicate them with no luck (I have modified titles and values). I have attached my example to this post.
If I have a column full of data is there a way to copy every nth row to a new column? For example, I may have dates and corresponding days of the week. Can I copy the date of each tuesday (i.e. every 7th row) to a new column?
I am building a form that uses a combo box to lookup up a row in a table and return row data. To make things more complicated I want to return more than one record. For example: the table has a list of jersey. I search for a style jersey. I want to search the list and return all the colours for the chosen style. Sometimes there is only one colour or there can be up to 10. Have a look a the attached example.
I am trying to use a sub to count files that some one has helpd me out with in the past. It was working but for some reason now it is not. I always have 0 returned as the number of files in the selected location (SourcePath), which is a filthy lie as there are files in there. The idea is that all .xls and all .xml files are deleted, and then if the folder is empty, that is also deleted, but as the Count_Files sub is always returning 0 the code is trying to delete the folder, which causes an error as it has files in it.