Trying To Imitate AVERAGEIF() With An OR() Test.
Dec 17, 2008
I've got a spreadsheet I use to track quality scores, it will appear like such, and with 2003 I don't have AVERAGEIF()
name | team | score
joe f | billing | 3
may r | billing | 3
roy m | status | 1
kay t | first call | 1
etc...
I already used an array function to find the average of each team (even with empty scores) by using:
=AVERAGE(IF(DeptRange=$B$7,C$20:C$200)) [Ctrl-Shift-Enter]
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:
=AVERAGE(IF(OR(DeptRange=$B$7,DeptRange=$B$9,DeptRange=$B11),C$20:C$200)) [Ctrl-Shift-Enter]
View 2 Replies
ADVERTISEMENT
Aug 21, 2013
I am trying to determine Long Term Gain (LTG,) Long Term Loss (LTL,) Short Term Gain (STG,) Short term Loss (STL,) or No Loss nor Gain (NGL)testing two cells (A1 and B1)and setting a third cell (C1) to the text LTG, LTL, STG, STL, or NGL depending on the results of testing cells A1 and B1.
A1 represent a number of years and B1 represent gains or losses (negative)in dolars.
The way I see the logic is as follows:
If cell A1 or cell B1 are either one of them equal to 0, then it is neither a Gain nor a Loss (NGL.)
If cell A1 is greater than or equal to 1, then it is Long Term; else, if A1 is greater than 0 and less than 1, then it is Short Term.
On the other hand, if cell B1 is greater than 0, then it is a Gain; if B1 is less than 0 (a negative number,) then, it is a Loss.
I need to find (if it is posible in Excel) one formula to test the two cells for posible outcomes:
If A1 = 0 then C1 = NGL
If B1 = 0 then C1 = NGL
If A1 >= 1 and B1 > 0 the C1 = LTG.
If A1 >= 1 and B1 < 0 the C1 = LTL.
If A1 < 1 and B1 > 0 then C1 = STG
If A1 < 1 and B1 < 0 then C1 = STL
View 3 Replies
View Related
Jul 8, 2009
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!!!!
18 hours and 2 minutes can not be correct!
View 11 Replies
View Related
Feb 3, 2010
I am using the following formula, but for the cells that dont have data I get the #DIV/0! error.
=AVERAGEIF($B$6:$B$48,B59,$W$6:$W$48)
View 2 Replies
View Related
Jun 5, 2014
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]....
View 12 Replies
View Related
Oct 27, 2009
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.
View 3 Replies
View Related
Sep 24, 2009
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?
View 10 Replies
View Related
Nov 6, 2008
I have data that i need to average. In Excel 2007 there seems to be a function to do this but it isnt available in 2003 version.
In column A there is a load number in column B there is a total load time, i need to be able to calculate an average load time.
View 12 Replies
View Related
Dec 3, 2012
How can I do
=AVERAGEIF(AZ6:AZ1721,">0",AZ6:AZ1721)
But across 18 tabs all with the same ranges???
View 3 Replies
View Related
Sep 11, 2013
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.
View 1 Replies
View Related
Jan 2, 2009
I cant figure out an averageif formula for a particular criteria. my criteria is any number between 10000 and 20000. Here is an example.
=AVERAGEIF(C:C,"between 10000 and 20000",G:G)
DateMachineLocationEventAmountStaffTimeComments1416041416042141604612326123211612321120410
1120512112035
View 9 Replies
View Related
Mar 11, 2014
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?
View 11 Replies
View Related
Jan 20, 2010
This is what i was using,
=AVERAGEIF($B$198:$B$240,B254,$C$198:$C$240)
I want to add more sets of ranges and average ranges like this,
=AVERAGEIF(($B$6:$B$48,$B$70:$B$112,$B$134:$B$176,$B$198:$B$240),B262,($C$6:$C$48,$C$70:$C$112,$C$13 4:$C$176,$C$198:$C$240))
Which of course returns #VALUE!
View 2 Replies
View Related
Sep 26, 2007
I've searched around the web for AverageIf solutions and found the CSE formulas. However I've come across a big problem I can't resolve.
I have two columns of data. Column A has _similar_ text and column B has data.
The problem is the following: AverageIf does not support finding similar text. This means that a formula like:
=AVERAGE(IF(A2:A400="Info/German*";F2:F200;FALSE)) does not work.
Column A contains words like Info/German, Info/German2, Info/Italian etc. But I only need to find the average of cells that begin with "Info/German".
View 9 Replies
View Related
Dec 5, 2013
I am trying to use the AVERAGEIF function to give calculate averages based on dynamic data. The formula I want to work is:
[Code].....
but this returns the "#DIV/0!" error.
However,
[Code] ....
returns the correct value.
I want to be able to calculate averages of only the cells that are greater than the value of cell D6. Is there a way to do this?
View 3 Replies
View Related
Sep 15, 2009
I'm trying to use the average if function to average a range of numbers, only if a different range of numbers numbers equal a number in two different cells.
ex. average A1:F1 if G1:L1 equals M1 or M2
View 3 Replies
View Related
Feb 12, 2014
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.
View 3 Replies
View Related
Jun 5, 2013
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...
View 2 Replies
View Related
Feb 10, 2007
I am trying to test for the next available row in a sheet using a range as follows:
NextRow = Range(MyRange).End(xlDown).Row + 1
If there are no rows being populated, the formula throws an error and I assign NextRow to 0 indicating no entries present
However, and this is my question, if there is one row populated the value for next row seems to either give 0 or 65637.
After this, the code runs OK.
View 9 Replies
View Related
Nov 9, 2009
How do I test if a cell has #N/A?
View 4 Replies
View Related
Jun 27, 2013
From a large worksheet I'm trying to find out if a particular value is used in that formula. E.g. formula: (In cell C1)=A1+B1*.75
I want to check if .75 is used in the formula (which is 75%) There are 1000's of record would it be possible to check the entire column C
View 6 Replies
View Related
Apr 9, 2004
I am using the IsDate() and IsNumeric functions in my VBA code. Is there an equivalent function for testing if a value is an integer?
View 5 Replies
View Related
May 11, 2007
I'm having an intelligence lapse and I need help with a formula
Simply need to return a "1" if value in Cell X is equal to or greater than -(minus)4% and a "0" if value is less than -(minus)4%.
View 9 Replies
View Related
Jul 16, 2008
I have a 32 question test that I expect everyone to get most (if not all the answers correct. I have a cell that is counting the number of correct answers.
What I want to do is start a timer when the first correct answer is put in and stop when the last answer is put in. So essentially, I need a way of looking at a cell and starting a timer when it equals one and stopping when it hits 32.
View 9 Replies
View Related
Mar 19, 2009
How i can test to see if the autofilter, while enabled, is not currently filtering?
View 9 Replies
View Related
Jan 23, 2007
how to perform Cochran's Test in Excel?
I know it's probably available somewhere as an add-in, but we aren't allowed to install any, so we have to do it from scratch.
Basically, I'm have to set up a spreadsheet to look at "Between Laboratory Outlier Tests", and Cochran's test is one of them.
Also, how would I calculate the Cochran Critical Values for a given set of laboratories and replicates?
View 3 Replies
View Related
Apr 25, 2007
I am currently working on code that uses either arrays or ranges. I often resize local array based on the dimensions of the input. However, I am running into trouble because arrays use ubound for dimensions, but ranges use ether rows.count/columns.count or ubound on value2. How can I determine whether a variable is an array or a range. the IsArray function happily passes a range variable, so that doesn't work.
View 2 Replies
View Related
Jul 25, 2007
I have a defined range Picku_time and this function fails
=isdate(range("Pickup_time"))
it gives the =#Name?
View 3 Replies
View Related
Nov 28, 2007
I am using the formula below to return a blank cell if either logical test returns true but apparently the OR operator returns a #VALUE error when applied to a non numeric value in a cell - in this case cell E40. =IF(OR(E40="N",P40=""),"", SUM(P40,S40,V40,Y40,AB40,AE4))+F40. My goal is to return a blank cell if E40 contains the letter N or P40 is blank, otherwise perform SUM(P40,S40,V40,Y40,AB40,AE4))+F40.
View 4 Replies
View Related
Jan 22, 2009
I have a resolution calculator that I am working on.
You put your original resolution in say 1440x1080
then below you put in any one of the new target resolutions.
Place 720 in the height and you get a message that says
"960 is your new matching Width resolution"
Id like to follow up on that with an if statment that test to see if the cell is blank if it is then null, if not then test 960 in this case to see if 960/16 = a non decimal number.
So in the cell to the right of that sentence it would return the result
"and 960 is 16 pixel safe" something like that.
I can probably figure out how I will handled the cell arrangement, numbers, and text I just for now need to find a way to do a test on whole numbers and return a text value (my guess is an if statement)
View 6 Replies
View Related