I have a data set where I am trying to find the nth largest value based on one criteria? How would one use do this using the large function?
Example:
Dept. Name / Widgets
ABC / 10
ABC / 8
XYZ / 9
ABC 7
ABC / 3
XYZ / 4
If I need the third most widget production of Dept ABC, the formula return 7.
I wanted to present the 10 largest numbers from a list (P4:P104) only if they had the words Tauck or Collette in C4:C104
When I have that list I will also need to present the supplier name which is listed in A4:A104
I used this formula to get the 10 largest first
=SUMPRODUCT(--(Data!C4:C104="TAUCK")--(Data!C4:C104="COLLETTE"),LARGE(Data!P4:P104,ROW()-ROW(A$6)))
I have 3 columns. In 1st there is a name of the product.In 2nd there are different types of product (6 in total) and in the 3rd column a figure attributed to the correspondend type of product. I need to create a summary for every type. For this I want to show the 5 largest figures(based on the name of product) for every type.
Like this:
AAA GK 40
BBB CD 50
CCC FD 55
DDD GK 60
EEE FD 70
.....
I've a two column dataset with one column for person name and one column for sales.
I'm trying return the nth largest value where the person name equals the value in a referenced cell.
My dataset may grow or shrink over time so I'm trying to avoid array formula.
Basically I'm looking for something like the below that actually works.
I am trying to write a macro in order to find the largest value in a column in one worksheet, and copy and paste that value into a different workbook. I have found a code similar to what I am looking for and tweaked it, but it needs a few more adjustments. The code below opens the file I want it to, and pastes data into the correct workbook and worksheet, but I would like it to be able to paste in the next empty cell in Row 3, instead of just in the cell "C3". Also i would like for the program to find the largest numeric value in column C, instead of using an if last row statement, as this current program does not always give me the output I am looking for.
VB:
Dim wsMaster As Worksheet, wbDATA As Workbook
Dim NextRow As Long, LastRow As Long
Set wsMaster = ThisWorkbook.Sheets("Contract Metrics")
NextRow = wsMaster.Range("A" & Rows.Count).End(xlUp).Row + 1
[Code] .....
I have a pivot table that shows values grouped as ranges in the row, Gender as the columns and an average % value in the data.
In a cell outside of the pivot I would like to be able to produce the name of the range that has the largest difference between male and female.
I need to find the largest value within a range that is less than the result of a formula from another set of values.
Something like this:
=MAX(E98:E115) that is <=(P63+((1.5*(P63-P61))))
i have a matrix of 1's and 0's. How can i use excel / which functions can i use to give me the biggest submatrix with just 1's in?
View 14 Replies View Relatedif c2c1 do nothing.
05 =20-5
10 =20-10
15 =20-15
20 =20-20
I want to find the largest and smallest value for a number... E.g.
68734 the largest value is 87643 and the smallest value is 34678
39823 the largest value is 98332 and the smallest value is 23389
43089 the largest value is 98430 and the smallest value is 3489
Any way to determine which is the best selling single of that week and print a 1 in the row associated with that CD.
View 1 Replies View RelatedI don't know too much about standard deviation, but I was wondering if somebody could help me with a function to find 1 standard deviation (68%??) from the largest value (not the mean value) within a column of values. What i'm really after is the 'standard deviation boundaries' either side (i.e. 34% of values above the largest value and 34% below the lowest value) as a highlight.
im using excel mac 2008 so no vba im afraid.
here is an example of real data: ...
I have a sheet with 3 columns - Line, Product and amount. I have to calculat the amount based on which line it is, and which product.
I. e I want to calculate all the amount in Line 1 except if the product is 3,23,31 (just an example - the range can be longer).
In workbook added i have shown the problem. I have tried the sumproduct formula, but cant get it right!
I have huge sorting job in Excel.I have a excel file from each year.And I put these together to a huge excel file. And I need to track down the last activity if the customer is listed multiple times.I could just go through and delete the rows, but the last row.But, that takes a lot of time.
Format:
Last activity (year only) - Customer number - Customer name
2010 - 1001 - Company A
2011 - 1001 - Company A
2012 - 1001 - Company A
I'm using the LOOKUP function to find the largest number and then return the name from a different column, it looks like this:
=LOOKUP(LARGE(round1!$F$2:$F$65,1),round1!$A$2:$A$65)
but all I get is hashN/A?
How to automate the process of finding the highest number in a dynamic range meeting a criteria?
New data added/updated every day (new come, old gone).
The attached file has a properly displayed data.
I am having some data running into 60 (C:BJ) columns and 200 rows. It is the record of dates query raised and answered with refernce to query number in the A coulmn. I have zero value as entry for the unused cells in this range due to some constraints. I need to find the smallest and the largest non-zero date in this range for analysis purpose. I plan to use the difference between these 2 dates to calculate turn around time for a query.
Problem is to find the smallest and the largest non zero date within range C6:BJ6 which would be used as input in the Networkdays formula.
I'm trying to find a suitable formula that looks at two cells (J3 and V3) in Sheet 1 (a person's employment grade (e.g. 18) and their performance percentage e.g. 92.5%), finds those values in a table on Sheet 2 and then places the corresponding value from that table in Sheet 1 at cell Z3.
View 2 Replies View RelatedI have a table of shipments made to my company broken down by style, color, and delivery date. I need to find the most recent delivery date for each style/color combination.
This is my table
ABCStyleColorDelivery Date1ABCRed1/1/20122ABC
Blue2/15/20123123Red3/12/20124123Blue1/30/20125ABC
Red2/27/20126ABCRed3/1/20127123Blue3/20/2012
and my desired results are as follows:
StyleColorMost Recent DeliveryABCRed3/1/2012ABCBlue2/15/2012123Red3/12/2012123Blue3/20/2012
The style and color columns are already filled in, I just need a formula for the date column.
I have a workbook with a number of sheets, the first 5 are data sheets populating the other 30 (more will be added in the future).
These worksheets are renamed with a macro when the data is refreshed, allowing the sheets being used to vary week on week
The code i currently have running does a number of things, however i need it to run only for the relevant worksheets
The sheet names are either 4 digits long, or 1 digit if unused.
I need a bit of code that says run my macro for all sheets with a name of 4 characters and ignore the rest.
I have a large list of students (Col a,b) who do several sports (up to 10) each, which are arranged as sport name (Col c) followed by score (col d), repeated on pairs of columns for the other 9 sports.
I need a macro to find the 4 highest scoring sports for each student in turn and to highlight the score and sport cells. I have tried to find permutations using the LARGE function but I cannot see how I can do this.
I am trying to create a macro that works with a range of cells and identify the five largest numbers in the range. The following are the steps.
1. I select a range of cells and run the marco.
2. The macro will identify the largest number in the selection and make the cell color red.
3. Then it will find the cell with next largest number and turn the cell orange...and so on till the 5th largest number.
I have a bit of a challenge. You'll have to take a look at the attached files to understand what I am trying to do. I am asking for your advice. I am creating multiple spreadsheets (QPPcompany.xls) that will refer back to a master data(QPPdata.xls) file.
I am trying to figure out how to best set up the data file so that I can do lookups on the data from the referencing files. Here's the challenge. I need to be able to find in the data page the company name in Column A and then how many L's (or C's or P's) they had in a specified month. So for instance I need to be able to find the number 3 in cell E6 and place it in B15 of the referencing file. The required criteria for lookup are Company Name, Month and then L, C or P.
So my question is, is this possible with how my data file is currently set up? I think it may be a problem to have the month/year in row 4 refer to 3 different colums for each month (L, C and P). I don't mind making changes to the data file to make these lookups possible and I think it may be necessary.
I have a workbook in which I have two sheets. One sheet is a report and the other is a data dump. The data dump has headers in in column A starting in cell A6 and headers in row 5 starting in cell B5. There is then data going from B6:J20.
In my report I then I have same setup with headers in column A and row 5. The difference is that the headers are not in the same order as the dump. What formula could I use that would look for the two headers in my report sheet and then match it with the value in the data dump that uses the same two headers?
Ok so let me set this up. I have 2 columns: .....
View 14 Replies View RelatedI have a sheet of pump test results, placed in rows, where the test rig is set at a specific speeds and flow and it records pressure. At each stage of the test a number of snapshots will be taken (Usually between 3-5 at each stage), so I have 3-5 rows of very similar data. I only need to use the best row from the selection available, so I am hoping to find a formula or code (Don't care which) that can look at the results and select the row of readings where the speed AND the flow are closest to the targetted data.
For example: In cells G6:G8 I have flow result values 0.129, 0.151, 0.156 and in H6:H8 I have speed result values 72.536, 71.82, 72.13. These are actuals. In cell N6 I have the target speed (75) and in O6 I have flow target (0.15), but these could be moved anywhere to suit really. What I was hoping to do, but am struggling big time, was to just flag up the row of readings where these two values are closest to the targets by placing a check mark in column L:L which I have formatted in monotype sorts and would like to place a "4" (Which gives me a check mark) on the 'best' row. From that I can do more with some simple IF statements.
Is this feasible, or am I trying to do something beyond Excels capability, it is certainly beyond mine at the moment. I was looking at the check mark method, but that is not essential, so if anyone has any suggestions on an alternative means of identifying the best row of data, that might make it easier to do, then I am listening.
I need to create a macro that will examine a range of cells, for example D12:T12, find the cell with the largest number value in that range then set the font for that cell to bold (or red, or change the cell background color).
View 2 Replies View RelatedI have a table with subtotals that I need to find the largest value for the subtotal results and then return the cell contents for the corresponding row.
I have attempted to use the hlookup function, but keep getting a #ref error (probably because I am just not that familiar with the entire formula requirements).
I attempted to nest in the 'largest' function to the lookup function, but have so far been stymied....
automate a data table I set up in excel. I need the formula to look through a bunch of raw data I have, and add quantities in each row, but only from the rows that have the same machine numbers. I attached the document of raw data I've collected, and on a separate tab, an organized table. I would like this table to automatically be updated with the appropriate data, as I enter it as raw data.
View 3 Replies View RelatedIm looking to find the max or highest time in column G based on the criteria (date selected and name) So if the date is the 10/02/2014 in G10 it would look up that date and the name and bring back the time in column H. I have put an example answer in H12.
View 9 Replies View Related