Formula To Count Multiple Ifs And Take An Average
Oct 15, 2009
Im having a problem finding a formula that will count and take an average of multipul ifs.
On sheet 1 of the attached file is multipul queries raised for us to answer.
On sheet 2 is an empty table which i would like help formulating.
In the Submission Lag Time Column i would like a forumla that looks down Column L on Sheet 1 and gives me an average for the amount of days a query has been with us from each company (column B) per month (going off the dates in column D).
I the same formula will be used for the other 3 columns, put looking down a different column on sheet 1.
View 11 Replies
ADVERTISEMENT
Oct 19, 2009
I have a transactional data set with a line for each transaction and I am looking to count the number of documents (each contains multiple transactions) against criteria.....
It looks something like this.....
Column A Column B
Document No Category
11000001 A
11000002 B
11000003 B
11000002 A
11000001 A
Is there anyway to do this without subtotalling for each document and then a count?
View 9 Replies
View Related
Mar 30, 2014
HTML Code:
=100*($I2/AVERAGE(IF($B2=$B$2:$B$38129,$I$2:$I$38129)))
I have the above array formula which I wanted it to calculate the average of all products selling in each store, but it seems to be calculated each?
In column b I have the store number, column I is the sales, I want to average all the sales in column I for each store.
I have probably over complicated?
View 3 Replies
View Related
Aug 31, 2007
I have several Workbooks ( named 110807, 180807 upto 100508) which include many worksheets(Lets say named Mon, Tue, Wed etc). The format of these workbooks is identical but the numbers within are different. I also have a separate " Totals" workbook in the same folder within which I need to calculate the average of all H15 cells across the whole range of Tue worksheets but only if Cell H3 in the same Tue worksheets is >0. The problem seems to be asking for a =AVERAGE(IF formula but no joy.
View 2 Replies
View Related
Jul 9, 2014
I need an array formula that can take average based on multiple criteria present row wise and column wise.
Check the attached sheet for detail. I need array formulae in yellow space. I have given one result that I need.
Average.xlsx
View 13 Replies
View Related
Jan 3, 2007
The formula below contains an error (one of many as I've been trying to resove this). = COUNTA(IF(CZ4:DE4,DI4:DN4,DR4:DW4,EA4:EF4,EJ4:EO4>"1",Games / Activity,Games)) The formula should return the following result:
If two or more cells in the above ranges contain numbers and cell C4 isn't empty then return the words "Games / Activity", otherwise return "Games". If C4 is empty keep blank. Can this be done with a standard formula?
View 2 Replies
View Related
May 20, 2014
I'm trying to find out if there is a way to count a specific number from a cell that has multiple numbers in the cell. I have attached a worksheet.
Example, on the attached worksheet, I want to count how many 16's in column C and input that total number into G18. Then I want G19 to tell me how many 17's in Column C, then how many 18's in G20 and so on. I want to do this for all days in a month, 1 through 31. If there are no numbers, then "0" or a blank cell is ok. Not to concerned with zero's.
Attached Files: xlsx Book2.xlsx
View 7 Replies
View Related
Dec 7, 2013
What I require : Column F to be blank as long as the following is true:
1) Column A (Order) cannot be blank.
2) Column E (Status) has to be equal to either "Completed" or "Successful".
3) Column D (Sales) has to have the same date as Column C (Contact), but due to bad data quality each order number can have several rows and as long as the Sale date from one of the other "Duplicate" rows equals the contact date it needs to be counted. This is as long as there is not already a row for the Order that already has Sales / Contact day matching.
In the attached spreadsheet, I have highlighted the rows that should be blank in column F in green. My attempts at formulas to count this are in the columns highlighted in red.
WORKAROUND.xls
View 1 Replies
View Related
Jan 14, 2014
I have multiple columns in excel that contains values like this
A B C D E F G H
TrxVolTrxVolTrxVol Trx Count Vol Count
122001400013500
125031290012499
130001300012700
Now at the last columns G & H i want to get the result that how many column of title "Trx" are having values greater than or equal to 1 and how many columns of title "Vol" are having values greater than 2500 respectively.....
View 1 Replies
View Related
May 20, 2006
My question is:
I do multiple audits using excel, and would like to start a database as I put more audits into one folder. So it updates the values as I insert more audits into the folder.
My audit is a workbook which has multiple worksheets. But for each audit, it is the same worksheet with the same cell. The audit consists of yes and no questions, where you put in a x for either one. I would like to start a database, so for each question on my audit, I would have a percentage of yes or no for all my audits. For instance, question 1 , 7 out of my 10 audits, I had yes for that question.
How do I write a macro, so it counts the x's for multiple workbooks, and updates automatically for each question as I add more audits into that folder.
View 4 Replies
View Related
Oct 9, 2009
I have a number of records that have in one cell a string of the form 1,2,3 etc (up to 10) representing conditions that have been met. There are a number of options available for producing reports on all of the other attributes in a record but now we wish to add this element as well.
The reports I can produce using VBA and the INSTR function, however on the summary page where, a total of all records matching available criteria is shown, depending upon drop down items being selected, I need to add this element to the equation as well. The existing conditions are counted using SUMPRODUCT and a combination of IF statements and work fine.
As there is an option to create a report for both AND and OR, e.g. if condition 1 and 2 and 3 apply include in the report, or if condition 1 or 2 or 3 apply include in the report
The conditions are selected using a check box and a drop down list to select the AND/OR condition.
I have been trying various combinations of database functions, countif, find and cannot get them to evaluate. I suspect that it may be beyond my reach to use a formula and I will need to use VBA with a Worksheet_Change macro to achieve what I want, or alternatively redesign the layout to store the conditions differently, however if anyone has any ideas for a formula I would much appreciate hearing from you.
A sample worksheet illustrates how the data is currently shown. The string in the record column is created using VBA and then inserted into the actual record.
View 10 Replies
View Related
Jun 27, 2012
I can't seem to get a formula to work. I am trying to count the number of times "EE" shows up in a range only if "CR" is listed in that row within another range. For simplicity Column A has a list with the following values scattered from A2 through A100: CR, LT, ST, DI. Column B has a list with the following values scattered from B2 through B100: EE, EF, EG.
I need to know how many EE's are listed in column B if CR is on the same line in column A.
View 6 Replies
View Related
Sep 18, 2006
Count unique records in Column B where.
1.)
... Column H >=A1 and <=A2
2.)
... Column H <>"" and Column I <>"expired" and <>"" and Column I >=A1 and <=A2
3.)
... Column H <>"" and Column I ="expired" and Column H+120 >=A1 and <=A2
4.)
... Column H <>"" and Column I >=A1 and <=A2 and Column J =""
A1 - user defined (start) Date 1
A2 - user defined (end) Date 2
Column B - 6 digit number (or blank)
Column H - Date 3 (or blank)
Column I - Date 4 (or "expired" or blank)
Column J - Date 5 (or blank)
View 7 Replies
View Related
Jan 7, 2008
I'm using the formula =AVERAGE(B16:L16) to give me the average.
However I have a couple of problems with this. Firstly I would like to exclude the value zero from the average. Secondly to also ignore the lowest and highest values.
Example, if the values in the cells are 0,1,2,3,4,5,6,7,8,9,10 then the current result shows 5, by ignoring the 0 and lowest value 1 and highest value 10 the average should be 4.5.
View 9 Replies
View Related
Apr 4, 2012
I'm using this formula to give me an average. some sheets may be blank and some will have results.
=IF(COUNT(WCMHT!B5+LICMHT!B5+SCMHT!B5+CAMHS!B5+Crossroads!B5+UCMHT!
B5+ADU!B5+IEDS!B5+SPS!B5+'SMHOPS CONSITON'!B5+'SMHOPS NOWRA'!
B5+'THE JUNCTION'!B5+FERNHILL!B5+WFT!B5),AVERAGE(WCMHT!B5+LICMHT!B5+SCMHT!
B5+CAMHS!B5+Crossroads!B5+UCMHT!B5+ADU!B5+IEDS!B5+SPS!
B5+'SMHOPS CONSITON'!B5+'SMHOPS NOWRA'!B5+'THE JUNCTION'!B5+FERNHILL!B5+WFT!B5),"")
So far only two sheets have results. 40% and 60%. therefore this formula should show 50% as the average because all other pages are blank ("").
Problem is it returns 100% as the average. What can i add to this formula?
View 3 Replies
View Related
Jul 2, 2014
I want to count the number of cells that have a value that is above the average of all of them. Ive tried the COUNTIF formula but no luck.
Sample attached : sample weds.xlsx
View 4 Replies
View Related
Sep 17, 2012
Is there a way to average and counta the values in the row of a each category in pivot table?
Example
Pivot Table
Fruits
Grad Total
China
UK
[Code]....
Is this possible?
Fruits
Grad Total
China
UK
USA
UAE
[Code]....
View 1 Replies
View Related
Jan 16, 2007
I want to figure a class average for a test, but I have students that have moved. My Excel is automatically counting them as a zero. How can I program/tell Excel to skip any blanks rather than count them in the average?
View 9 Replies
View Related
Apr 19, 2006
I am trying to make a excell spread sheet that will calculate my students averages for the year. I need to account for zeros in the coarse and I want this excel sheet to track the current average all year long . So I do not need to include my blanks in the average as I go. Also , how do I formulate my average accum to show this formula - Test scores, four of them count as 80% of the total grade. So each test is worth 20% / Lab 10% of total grade and homework is additional 10%. I downloaded the templete from MSN and have tweaked it to my liking except for the coding above. Please advise. I am a below par on Excel. My attachemtn is below of my templete. One note. the templete gave me the room to include 13 ros of homework - but I will not necessarily use all of them... I can make it one row for home work only ....
View 9 Replies
View Related
May 22, 2014
I have a spreadsheet that contains developer related data. I want to be able to do a weekly analysis of average construction time and costs. The problem I am running into is properties enter rehab phase and exit rehab phase throughout the week.
For example:
I want to know the average $ spent and total rehab time week 1 of april. I may have 4 homes that entered rehab that week and 3 that move out of rehab that week. Moreover, I need to ensure those homes that entered and are still in rehab for that week are counted. How do I accomplish this feat?
View 2 Replies
View Related
Jul 7, 2011
average i want to count.
Say i want to count the average from differnet selected cells say
cell A1,B4,C9,D10,E12
and some of the cells have the value 0 in them, so i don't want the formula to include these as long as they have the value 0.
I have tried to divide with a sumproduct and i can get i to work if it is cells next to each other like A1:A20, but not if i want to do it with different selected cells
View 6 Replies
View Related
Jul 10, 2008
I've got a spreadsheet that I do every month with columns of numbers that I average. This sheet has to match about 10 others similar. The columns are divided by Weekdays, Saturdays, Sundays. But some months there are no entries for certain cells on Saturday or Sunday.
I thought that if I just used the Average function, it would dismiss and not count the blank cells. Alas, apparently not. I've highlighted in yellow the one column that I'm really having trouble with.
View 7 Replies
View Related
Mar 11, 2014
I am trying to calculate the average headcount for different categories (over a certain period of time, by product, by job type etc) and I want to do it using a formula instead of a pivot table.
I have this set of data with a detailed headcount for every month. And I can find out the total HC for different categories using sumif/sumifs but if want to find out the average for a certain period, I need to split that total to the number of months where I have a HC.
For example, if the HC per month would be:
May 2013 - 12
June 2013 - 10
July 2013 - none
August - 5
in a 4 months period I would have a total HC of 27 and the average would be 9 (27/3 - July shouldn't be counted). How can I get the average in a single formula?
Month
Type
L/H
Prod type
HC
[Code].....
View 9 Replies
View Related
Jul 28, 2009
I am working on a Productivity Log and can't figure out a way to count unique values, and also average the time per assignment.
Assignment with the action code A - should take 2 hrs to complete, all other action codes should take 20 minutes.
I need to show how many of each action codes were done for the day, and what was the average time taken to complete each. Even better, how much for A action codes, and how much for all other.
View 3 Replies
View Related
Feb 12, 2009
The data:
There are a number of columns, but we'll use the first 2 first. Column A has the dates from 1/1/2009 to 12/31/2009. Column B will has numbers, mainly 0-100. One cell will have a 'report date' which is just a date.
The formula needed:
I have a few cells labeled wk1, wk2, wk3. Week 1 will start with the 'report' date. wk1 I would like the Average of Column B IF the dates in Column A are within the 'wk'. wk1 = report date TO report date + 6. wk2 = report date + 7 TO report date + 13 and so on.
My goal is to have the formulas all set so that when we move to another report date and the raw data is added, I can just change the 'report date' and everything (the wk numbers) will automatically update with the new raw data.
The furthest I got was this, but it didn't work.
=AVERAGE(IF('A3:A33>=VALUE(D3)
View 9 Replies
View Related
Aug 18, 2006
I need to monitor the average daily usage of a liquid tank for a customer. We fill this tank every few weeks. The formula I am looking for would ignore the fills and just count the daily usage.
View 9 Replies
View Related
May 1, 2008
sumif problem but it wont work with a countif or average if.
Column A has various names and Column B has amounts, what I need is to count the number of occurances "John Smith" has an amount in Column B. The previous formula I tried was
=sumif(A:A,"John Smith",B:B) but with either countif or averageif it errors too many arguements.
I wasn't sure if Dcount or an array would be suitable but have not used them before.
Pivot tables I'm sure will be the future with this but haven't got to the foot of that mountain yet.
View 4 Replies
View Related
Jan 20, 2008
I want is a field (e.g Large Parts Used) where I can enter in a number, then basically this number is subtracted from current stock field for Large Parts so I get an updated field of current stock on hand.
But what I want to do is once I've entered the number in the Large Parts used field, I can then clear that field but have the corresponding Current stock field to maintain what was last enetered.
E.g
Large Parts Current Stock = 50
(enter in) Large Parts Used = 2
Large Parts Current Stock = 48
(Clear field where 2 was entered into Large Parts used)
(Field still stays at Large Parts Current Stock = 48 although field where 2 was entered was cleared, so need it to save the information so can continually clear and re-enter amounts and have the stock continue to reduce)
View 9 Replies
View Related
Aug 21, 2006
going down are stores a, b, c, d.... what i'm filing in across is the square feet of each store and what quartr or year each store came into place. so there will either be a 0 or a number Now, I want to be able to count the number of nhew stores each quarter. how do i create a formula that just recognizes it the first time there is a number and not a zero... because i will put the square feet in subsequent quarters after it opens so i can see yearly how many square feet the store had. then also, how can create a button on the page that will say quarterly numbers and a button that is annual. so that i can hide the quarterly columns and just see an annual spreadsheet... and for the quarterly button so i can hide the annuals and just see the quarters....
View 6 Replies
View Related
Mar 26, 2009
I am trying to come up with a formula that will count everything excluding 1 in one row, while looking at another row to determine the group.
The attached example explains things a lot better.
I am going to have 2 formulas. 1 for the "Big" group and one for the "Small" The formula needs to look first at the column that has the group in it. Then it needs to count everything is column A excluding "Snake" And return the value.
View 2 Replies
View Related
