I want to calculate the average of a range...if it meets a certain text criteria.
For example, if the product is a "Course", then take the average of pages all those courses together.
ProductNumber of PagesExam316Course46Exam232Course32Exam245Course53Exam155Course246Exam118Course154Exam82Course434Exam80Average # of Pages for Courses = Average # of Pages for Exams =
Is it posssible to use the xirr function with conditions. For example, an array formula that will only calculate xirr if a column meets a certain criteria or if the dates are within a range?
View 9 Replies View RelatedIn the attached Exel work book I have work sheets named
Material Usage – Usage of materials
Estimate - calculate the Total material cost for a job
Material Cost – defines the material cost for each material type
In “Estimate” worksheet Job number is repeated but sub jobs falling under a particular job number is unique. Materials used for each sub job is different.
Once the job number is selected from the list box , I need to calculate the total material cost for each job. I tried sumif function but I don’t know how to get it to look up for each material type and get the sum .
IN column J(on sheet 1) i want it to return text (OB) if Sheet 1 column A1 equals Sheet2 Columns A1:A500. And if Sheet 1 column A1 do not equal Sheet2 Columns A1:A500 return text(IB).
View 1 Replies View RelatedI'm trying to find the correct formula to sum text that meets two criteria. If C2:C1023 is Equal to TEXT from J8 and F2:F1023 is equal to TEXT J4 add up the total times J4 shows up in C2:1023
=SUMPRODUCT(Sheet3!C2:C1023=J8,Sheet3!F2:F1023=J4,Sheet3!C2:C1023)
I have been using this function to count the number of unique text values in a data set:
=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))
It works great if I want to count number of unique text values overall. However, I want to count the occurrences of unique text values if they meet specific criteria. (Like a countifs function would if it could count unique text).
I'd like to calculate an average over a variable range. In col.A there are grades from A4:A21. In col.C there are the values for the start row of the range and in col.D the values for the end row of the range.
For instance the value in C4=4 and D4=9. In cell F4 I want the average calculated over A4:A9. Value in C5=10 and D5=15. In cell F5 I want the average calculated over A10:A15.
I am trying to calculate average for values in a range of data that fulfill a certain condition viz. >1000 &
View 5 Replies View Relatedhow to do a weighted average with a text criteria. I have 3 columns and many rows. The first column has either "buy" or "sell". The thing is I need to find the weighted average based on the product of the other 2 columns for buy and sell separately e.g.:...............
View 5 Replies View RelatedIt is a database in which each row represents a unique individual.
Columns A - G contains information which is irrelevant for this question
Columns H - CQ contains answers to the individual's questions (Yes/No or "-" for N/A)
Column CS contains a array formula which determines the % of "Yes" answered only in Question 1 (Columns H to X).
The formula finds all "S1*" (within H1 to CQ1) and if the corresponding "S1" has a "Yes" (within eg. row 2 - H to X) it adds it and divides by the total number of "S1" (17 in this example excluding the "-")
Eg:
In Row 2: S1 = 17, Yes = 7, "-" = 0, % = 7/17 =35.29%
In Row 3: S1 = 17, Yes = 8, "-" = 1, % = 8/16 = 50%
So I have the array formula in column "CS" which does the above.
What I want to achieve:
I want to get the average of all the individual rows in column "CS". This is shown in "CS1 (48.32%". I am looking for a way I can achieve this with just one formula - A formula which first calculates the individual %s and averages the %s within the same formula.
I am having problems getting a formula to calculate the max if it meets 2 criteria. MAX of time listed by 1/past a certain date. 2/are of a specific client.
Here is my formula...
{=MAX(IF(All_Tickets_07012014!$D$2:$D$155685>1/1/2014,IF(All_Tickets_07012014!$O$2:$O$155685=$A2,All_Tickets_07012014!$G$2:$G$155685)))}
The data...
$D$2:$D$155685 - is the list of dates per ticket.
$O$2:$O$155685=$A2 - Are a variety of clients, where $A2 is a name of one of the clients.
$G$2:$G$155685 - Being a range of time stamps.
The formula runs, however it only shows the MAX of all entries, not within the specific date range...
In theory I know what I should do, but I don't know the syntax. So here it is:
There are 1450 unique records in my XLS, every record contains 45 different rows(these are the phases) with their position(1....45). Every row has a status (Not Started, In Progress, Complete)
COLA(uniqueid)....COLi(it is a number, it is the position).....COLN(status)
id1....1......status
id1....2......status
id1....3......status
.
. .
. .
id1 ....45.....status
id2.....1.....status
.
.
.id(n)
basically I would like to get the last "in progress", If not found, the last "Complete, If not found then the first "Not Started". and put a "Y" right next to the row to a new column for all the groups(45 rows)
s/s has 325501 rows. Column C contains names of people (whether present or not -I enclose small attachment to illustrate).
Column J contains scores (if present). I need column N to list the last row number where each column C name scored points (not just when heshe was last present). I think I need macros which I can fill down both columns (??).
For those of you that love difficult ones, here's one..
I have a sheet that has a column of names.. Across the top row I have dates.. let's say from the 1st through the 31st.... in the body of the report i have different sales numbers and what I am trying to is the following...
1/1 1/2 1/3 1/4 1/5
Johnny 3 4 2 2 1
Becca 3 4 1 1 1
So let's say in this instance, mind you my real report is about 342 rows of names and stats.. Is look for Johnny between 1/3 and 1/4 and average those numbers between those 2 dates...
I've tried everything from sumproduct to vlookups and can't seem to get it right...
here is a sample of the data
150
21
352
422
53
69
755
852
1224
13268
119
242
30
444
543
67.................
I know if I use dmax for only where first column equals 13 I get 460 but how do I get the second highest value for only those rows that have 13 in the first column (expect the answer to be 268). Then I want to do the same for 3rd, 4th highest etc.
I know large does it for one column and not only when the first column matches a designated criteria.
s/s has 325501 rows.
Column C contains names of people (whether present or not -I enclose small attachment to illustrate).
Column J contains scores (when present).
I need column K to list last row where column C name scored and column L to list last score.
I think I need macros which I can fill down both columns (??).
i am trying to get my excel formula to say if 2 date ranges in my excel workbook are the same (in seperate worksheets) to put a value from a certain cell into another cell in another worksheet. This is all in the same excel application...
The first step i have taken are:
1) if the 2 date ranges are the same to say 'true' if not 'false'
I am trying to get a forumula to say "if it is true to put a value from a different cell into another cell (both in different worksheets) but the same excel application...? but if its false to move on to the next date
I want to create a sumif formula that will sum the data if it meets five different criteria. I tied to do an “Or” statement in the formula, but it doesn’t work. For example, I want to sum all the rows that contain: Apples, Bananas, Cherries, Pears, and Plums. How do I write the sumif formula so that it will do this?
View 9 Replies View RelatedI 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)
I have gotten a code. It goes into Sheet 1 and picks up all the Rows (entire row) in which Column A = 1.
Instead of copying the entire row, I need to just copy the row but only specific columns (i.e. Row 2, Column B,C,D,E,G).
Is there a way to specify where it pastes values in Sheet 2 (say from column D) because Column A-C have pre-filled data which do not change. And when it pastes, it has no gaps (despite a gap in the copied columns (i.e. Column F in sheet 1 was skipped)).
VB:
Sub cond_copy()
'assuming the data is in sheet1
Sheets("Sheet1").Select
Sheet2.Range("A2:G5000").ClearContents
[Code] .....
I am trying to add the tuition to be reimbursed if the claim date is on or after a certain date and then deducting the total from a set amount. I have included a simple example. I tried a formula from the pierson examples but Im doing something wrong.
View 4 Replies View RelatedI am trying to come up with a simple formula to count a single cell if it contains either a 2 or 3.
The cell can contain numbers ranging from 0-8.
Even better would be if I could some how evaluate single cells based on the contents and then count the number of cells where the criteria for contents is true. The problem is the criteria differs from cell to cell (i.e. D2 could = 2 or 3, but E2 needs to be counted only if it contains a 4 or 5).
I know COUNTIFS only evaluate a range but it would be perfect if I could somehow get it to work for single cells. COUNTIFS(D2,2,D2,3,E2,3,E2,4,F2,2)
I'm using the following on the Worksheet_Activate event to update rows with the date, (Cell AD1 contains the current date):
Code:
Range("X2:X2000").Formula = "=IF(IF(ISBLANK(W2),,$AD$1)=0,"""",IF(ISBLANK(W2),,$AD$1))"
Is there a way I can amend it so it only updates those cells which currently have no data?
At the moment it is changing every cell with the date which overwrites existing dates - I want it to update the new stuff only.
Say I have a column of cells filled with numbers that are ascending. What I would like to do is to pick out the first cell that meets my condition. For instance, these cells contain irrational numbers that increase from 0 to 100. I would like a formula to pick out the first cell that exceeds 10.2, and to return the row number of that cell.
In the case of descending numbers, if I would like to pick out the first cell that goes below 10.2, would the formula be the same?
I want to scan all sheets in a workbook and to delete a first row where a value in cell(1,1) is "table".
Sub DeleteFirstRowInWorksheet()
Dim SheetName As Worksheet
Dim i As Integer
For Each SheetName In Sheets
If Range("A1") = "table" Then
Rows("1:1").Select
Selection.Delete Shift:=xlUp
End If
Next SheetName
End Sub
It delete only in an active sheet. What's wrong?
Have a worksheet Pricelist, require to pull data from the columns to a new worksheet only if qty is more than 0, and delete empty rows afterwards. Required result is in worksheet order. Original file is about 10K rows.
Attached sample file : example.xls
As per attachment, see data : 2604 Stock.xlsx
On sheet 1 column A and B. On sheets 2 I would like to make a seach tab - so if the people type part of the word or code it must return all the data from sheet 1 to sheet 2 that meets that criteria in full or part thereof. I have tried but can't get it to work, might it be that it is on 2(two) sheets ?
I do not understand code so I really do not know what to do where. Basically it should work as a Ctrl-F (find-all) function.
We were so close!. But it appears that the assumed correct answer only works if there are no repeating N. The repeating N gets the same count as the last Y and it throws off the sum ....
View 14 Replies View RelatedUsing Excel 2003.
In column Y, I have a list of employee numbers (there are a random number of blank spaces in between each number)
In column AE, I have a list of clock in times (there are a random number of blank spaces in between each clock in time)
In column AY, I have a list of the same numbers from column Y (there are no blanks in this list)
Column BB. I am looking for a formula to place in cell BB2 and copy/paste down that will match the number in column Y and return the first non blank cell from that point in column AE.
Example:
10062 is employee number in cell AY2. Her employee number (10062) is also located in cell Y5. Her clock in time is located in cell AE7.
10099 is employee number in cell AY3. Her employee number (10099) is also located in cell Y14. Her clock in time is located in cell AE16.
What formula can I place in BB2 so that it returns the clock in time of employee 10062. Then copy and paste so that BB3 returns the clock in time of employee 10099?
Any quick way to extract data from a table. I need to extract a value from a column that meets criteria from two different columns. I thought I could get this to work with vlookup, but have had no success. Sample data below in table 1 and I would like to get my data into table 2.
elevation
type
grade
percent
weight
5000
5000
5000
5020
[Code] ..........