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'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?

I've got no clue about all this, but I've had to get specific formula examples and fill in the blanks in order for my timesheet to work. There's just one final problem if somebody could please help.

This is a timesheet for a 5 day work week. I need to count the number of unique log numbers for a specific activity. The log numbers counted must be unique across the entire week, not just for each day, which means I want the formula to count the unique log numbers across multiple sheets.

The formula also has multiple conditions. I got 2 columns. The first part of the formula needs to verify a word, say, "split" and if it does it checks the adjacent cell for a unique log number. If both arguments are true, it counts the log as 1 unit.

Here is a working formula for only one page. =COUNT(IF(D4:D29="split",IF(FREQUENCY(C4:C28,C4:C28)>0,1,)))

Here's 2 problems with this formula: 1. I will count if it encounters a blank cell in the Log numbers the first time (which will happen as not every activity we do has a log#), but it will stop counting if it encounters a second blank cell.

2. I don't know how to make it work across several sheets.

This is an alternate formula which works and skips the blank cells, but I don't know how to add the multiple condition of "split" and to have it work across multiple sheets. I just copied it Microsoft. As I said, I don't understand it, I just fill in the blanks.

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 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.

D E I J Account1 Sum1 Account2 Sum2 151 93,79 BF2479 30,00 1476757 93,79 BF7 22,00 BF2479 30,00 151 93,79 86041 84,03 23235 30,00 534 6608,45

2. I need to clean the multiple identical data in the list by both Account no. and Sum (of course the list is much longer!) so that the list should look like this:

D E I J Account1 Sum1 Account2 Sum2

1476757 93,79 BF7 22,00

86041 84,03 23235 30,00 534 6608,45

I must say that I am a beginner in Excel, so I tried to use this:

(which is reverse of the first one) for column J; (The ranges are the ones used in the real sheet), not for the ones in the example above!); The formula does seem to eliminate a dew unwanted data, but does not give me all the wanted results!

I'm trying to divide my data into 6 different groups, based on 2 different criteria. First, I am not sure how to write the logical test to take 2 columns of data into consideration (using "&" and "AND" do not work; I am not sure what else to try), and second I can't figure out how to write the formula so that it can select from 1 of 6 conditions.

So, overall here is what I want:

If DL2=3 and CK2=1, then I want this to be labeled as '1' If DL2=3 and CK2=2, 2 If DL2=1 and CK2=2, 3 If DL2=2 and CK2=2, 4 If DL2=1 and CK2=1, 5 If DL2=2 and CK2=1, 6

These 6 conditions cover all possible combinations of numbers in the two columns.

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".

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.

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...

X Y Z DOU Quarter Sum 0 Q1 = sum of 1's in Q1 1 Q1 = sum of 1's in Q2 1 Q2 = sum of 1's in Q3 0 Q2 = sum of 1's in Q4 1 Q3 1 Q1 1 Q4 0 Q2

A cell in the SUM column must contain the sum of 1's in column DOU if under column Quarter the value is Q1. Similarly for Q2, Q3 & Q4 if only the corresponding value under DOU is 1.

Its like.. count the number of 1's per quarter and come up with the sum in 4 distinct cells for each quarter.. i.e. quarter-wise sum/count of 1s

If ActiveCell.Value < -25 And ActiveCell.Offset(-2, 0).Value < -25 And ActiveCell.Offset(-3, 0).Value < -25 Then Range("C4").Select End If End Sub

I am looking to do something similar on excel 07 as follows: Range("O16").Select IF ActiveCell.Offset(0,-1)Value ="1" And ActiveCell.Offset(0,-2).Value = "1" Then ActiveCell.Value = "1"

Compile error expected: Then or GoTo At the First value (which I have highlighted in red)

Also would like the If formula to repeat for range O16:O36.

I need to create a conditional sum in the report tab in the yellow cells that will sum all the values from the list sheet, based on the following conditions.

1) The name is in the list 2) The code in the list is one from the codes tab.

I saw somewhere that I need to put an 'OR' statement in the conditional sum but I can't get it work. It seems to add up values for the code 'HOLIDAY' and I don't want it to.

I cant seem to rack my brain around how to go about making a formula in order to achieve my desired effect using the IF Formula. The data is as follows:

Worker Status % S1 75% and higher S2 42.8571% -74.9999% SH Anything below 42.8571%

My sheet pulls the workers status from a preset data table, lets say S2 for Joe. I want the cell beside his status to compare what his percentage is and display what the status should be (based on the conditions of the above table). If hes working 73% it will turn up S2 and I can compare the value from the table and the value generated based on his working percentage (I would use conditional formatting to color the cell red or green to indicate if it matches or not.)

Column A: Individual Column B: Not used in formula Column C: Not used in formula Column D: Location Column E: Duration in Minutes Column F: Pay (Column E)*(Column G) Column G: Formula

(simplified version listed below)

[Code].....

To be clear of what I'm trying to achieve:

When in location 1 & 2 (Column D), the pay for each individual is a different rate depending on the duration in Column E, but when in Location 3, the rate is consistent no matter what the duration.

My question is:

How do account for individuals who aren't individual1? That is, my understanding of the above formula is that I need to list each individual that will appear in Column A for it to work. What if I just want to apply the above to individual1, but apply some other calculations to all other individuals?

I had tried adding these lines (with their corresponding closed-parentheses at the end)

[Code]....

But it doesn't work. I'm guessing it's some sort of OR, but I don't yet grasp how.

I should note that my formulas are all on one line in my spreadsheet, I just have them on separate lines to make it easier to find my way around.

I'm trying to get a sum only if 2 conditions are met.

Column A has dates (ex. 01/02/2007) in a random order. Column B has a location in a text format (ex. London). Column C has a series of values formatted as [h]:mm.

What I'm trying to do is get a sum of the values in column C where the corresponding values in columns A and B = the date and loction I specify. I've tried...

I have the following formula entered into cell O4: =IF((AND($K4="1",$B4="1 - C",$I4="open")),(1),(0)). When the three conditions are met in the corresponding cells, I get a "0" returned in cell O4. I would like the formula to return a "1" when all three conditions are met.