Find Minimum With Criteria - MINIF
I have following problem I have some table - 2 columns - in first is text in second is value.
example:
A 10
B 5
C 3
A 15
C 8
D 7
A 9
I need to use formula, which shows me minimal value for choosen text. I tried matrix formula like {=min(if(text table=choosen text; second column;0}, but it's not working
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
MINIMUM If With Multiple Criteria
I am trying to find the minimum date in column B based on the value in column A and the Maximum date in Column C. I already have a calculation for column C that ignores certain values, i am fine using that date.... On the first sheet, I have.... the "summary of data" The date in column C is the Maximum value ignoring 3 certain dates (10/10/2030, 11/11/2011, 12/12/2012) I used this formula to find that value. =MAX(IF((Sheet2!A$3:A$16=A4)*ISNA(MATCH(Sheet2!C$3:C$16,{47766,41255,40858},0)),Sheet2!C$3:C$16)) Now, based on that I need to find in column B the Minimum value based on both the value in Column A (item) and the maximum date already found in colum C (Max)
View Replies!
View Related
Minimum Value With 2 Conditions/Criteria
Really struggling with this one. Can't seem to figure this out on my own. I'm trying to populate a value from a table based on two given values. The first given value will be an exact match, and the next given value sholud fall within a range. In real life terms, given the category of a security and the months to the maturity date, I'm trying to return a percentage rate to use for another calculation. The rate is given in a Table (Column I in the attached). See attached for example
View Replies!
View Related
Match Criteria According To MINIMUM And MAXIMUM
I need a macro that will search MIN and MAX columns and identify which horse has the maximum number in both the MIN and MAX columns an example is highlighted yellow in the attached spreadsheet. If there is a selection i want the macro to keep the horse that matches the criteria along with the race date and time e.t.c (header) but delete all other horses in that race. If there are no selections that meet the criteria in a race i want it to delete all details for that race including headers and go to the next race.
View Replies!
View Related
Finding MINIMUM Based On Adjacent Criteria
What I am attempting to do is find the MIN value in Column C where values in Column A are equal. The data would look like this A B C D (D:D is where the "MIN" Formula will be) Scope1 NameA $100 Scope1 NameB $145 Scope1 NameC $115 $100 (I want the min value to show up here) - (this would trigger a break between scopes, and provide a conditional format separator) Scope2 NameE $450 Scope2 NameG $345 Scope2 NameX $415 $345 - So every time I put a "-" I would like the MIN formula to trigger in (Row#-1,D)
View Replies!
View Related
Find A Minimum Query
I have a set of worksheets all of which are identical and covered by first:last sheets What I am trying to do is come to up with a minimum figure for cell G30 With the proviso that it is a minimum of 30 as some of the entries in G30 are less than that figure. =MIN(first:last!G30) is not the answer as it gives an answer of zero.
View Replies!
View Related
Find Minimum Value Corresponding To Maximum Value
I am having trouble with large data sets of rents by market: trying to find the peak rent in a time-series, and then look for the next trough rent in time. I tried using the Newsletter 3 techniques which feels like the right direction, but I am not getting the nesting right or some of the parameters in the functions. So, within each time-series I need to find the MAX value (peak rent), wherever that is in the array. Then I need to look to the right of that exact MAX value and find the exact MIN value (trough rent), whereever that is in the array (again, to the right of the MAX value). A small sample of what the data set looks like: 2008 Q22008 Q32008 Q42009 Q12009 Q22009 Q3 Atlanta771773768761749756 Austin770786784779776776 Baltimore947948949950950953 Boston1,6471,6601,6541,6481,6301,613 Chicago998998996985984986
View Replies!
View Related
MAXIF And MINIF Functions With Wildcards
I have a list of bank transactions in a spreadsheet for example.... 1/7/09 Sainsburys Petrol 34856385 4/7/09 Paypal 92017465 6/7/09 Sainsburys S/Market 38264059 8/7/09 Halfords 93862830 11/7/09 Sainsburys S/Market 34957372 13/7/09 Paypal 92037454 14/7/09 Barclays BCC 173256 and wish to use formulae similar to MAXIF and MINIF to find the last date and the first date of Paypal transactions (for example) if I have just the word "Paypal" in cell D1 I've tried =SUMPRODUCT(MAX(($B$1:$B$7=$D$1)*$A$1:$A$7)) but obviously this is looking for exact match of "Paypal" I have successfully used a wildcard in SUMIF functions elsewhere... eg, $D$1 & "*" but for some reason =SUMPRODUCT(MAX(($B$1:$B$7=$D$1 & "*")*$A$1:$A$7)) does not work Does anyone have any suggestions?
View Replies!
View Related
Find Minimum Value From A Data Group
I have been trying to solve this problem for the past couple of weeks and I have not been able to do so. I have come to do the operation manually, and it really takes a lot of my time, so if you guys could help me, I would really appreciate it. Basically the problem is this: In column "A" I have what I call point numbers for all the data entries. In column "B" I have the station (or location) at which I vary slightly a parameter (represented by column "E") to give me a value shown in column "D" (the "Z" value, as shown in the sheet). So basically you can see that my station 0 has 11 different "E" parameters (0, -1, -2.62, -5, etc) and each of them has a "Z" parameter (390.76, 390.775, etc). What I am trying to do is to come up with a table that shows a summary of the following: A column with each of the stations (0, 20, 40, etc -- notice that I have some weird numbered stations like 411.89). Another column with the "E" parameter corresponding to the lowest (or minimum) "Z" parameter within the station range. And a final column with the minimum "Z" parameter corresponding to its "E" parameter, which is included in the station range You can see in yellow the way I have been doing this and you can see that it would take me so much time to accomplish the task.
View Replies!
View Related
Conditional Count (Find The Minimum Value In The Range)
I am trying, without success, to create a formula that will refer to a column of data and do the following in one step: 1. Find the minimum value in the range, and then 2. From that minimum value, count back “up” the column the number of occurrences, including the minimum value, until a zero is reached. The zero should not be counted. For example, if the values in A1:A6 are 1, 0, 2, -1, -2, 1, the minimum value is -2 and the count would return 3. (i.e. 2, -1 and -2)
View Replies!
View Related
Find Minimum/Maximum Excluding Zeros
I have inherited some legacy templates (Standard, Leave and Exception) which cannot be changed. I need to summarise them (Total) selecting the earliest start and the latest finish. (Sample attached). The templates are 90 columns wide and about fifty lines deep so named ranges isn't practicle (I think). I'm running 2003.
View Replies!
View Related
Find 2 Corresponding Values & Multiple The Minimum
Look in Column A and find the cell that contains "Std A 1" and the cell that contains "Std A 2". Compare the corresponding values in Column B, and multiply the lowest value by .2 I've tried using various methods (find, if cell.value.instr) but I keep getting errors. Can anyone point me in the right direction?
View Replies!
View Related
Find Minimum Value In Column Corresponding To Specific Text
I have a table that contains various aspects of information about customer cases, and I want to replicate a user 'picking up' the case by a simple press of a button. Users have access to only one Country, so I want to be able to search a particular column for the lowest value, but check that the Country for that row matches the user's access. If it doesn't, I then want to find the next lowest value in the column, and this is what's perplexing me??? As mentioned, I want to click a button to trigger this, and therefore want to use VBA code.
View Replies!
View Related
Lookup Functions Find The Address Of The Minimum Value In That Range
I have a range of cells (say A1:D8). I wanna find the address (say $B$2) of the minimum value in that range. This command does it but it only works on a column. it does not accept a range spanning more than one column. =CELL("address",INDEX(B2:B7,MATCH(MIN(B2:B7),B2:B7,0))) this will tell me the address of the minimum value.. but in a column... I need a range of many columns. So I tried converting things to a Table.. but it never worked. basically the MATCH command is my problem. It only accepts single-column-ed ranges.
View Replies!
View Related
VBA Solver: Find Minimum Value Of Cell By Changing X Cells With Constraints
I'm trying to use solver to find the minimum value of a cell by changing 5 separate cells. These cells are subject to a set of constraints that are formulae. On top of that I have a large number of these solve's to run (The spreadsheet is creating randomly distributed data to form a Monte Carlo simulation). Therefore I am referencing the formulas using the ActiveCell, Offset() function. For some reason when running this code none of the values are changed. Also I changed the UserFinish to false so I could see what solver was doing at each solve, It stops at the correct cell (one after the final cell) but without me having to confirm it each time. Here is the ....
View Replies!
View Related
If Minimum Below Zero Then Return Minimum
I have had a lot of luck finding what I need from the search areas, and I even found some information on the formula I am trying to build. The problem is I don't understand it and I need some help. First let me set it up for you. (I do not know the formula) If cell L125 is has a value >0.00, I need to locate the smallest value the range of cells C125:F125, I then need to subtract L125 from that number, otherwise enter nothing. This really has me baffled. I tried and I tried but it will not find the smallest value then subtract L125.
View Replies!
View Related
Find Average With Criteria
In column A is the staff name, column B is the start date, column C is the end date and column D is the difference. I need to find the average number days for task completed for each staff for the week. I need a formula that will look for a particular staff name in column A ( which appears many time randomly in column A for each day of the week) and calculate the average days for the total task completed for the week.
View Replies!
View Related
Find And Sum Based On Criteria
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!
View Replies!
View Related
Find And Isolate Duplicate Criteria
I have a worksheet that tracks errors (10 different error types are tracked) that may occur on anywhere from 20 to 200 different jobs. What I need to be able to do is find all the jobs for any given day where a specific error has occurred two or more times for that day. Often a specific error will occured many times on the same job on the same day. What I need to end up with is only one row for each job that shows the Date/Time (m/d/yyy h:nn), JobNumber (number), ErrorNumber (text field) by either hiding or deleting those rows that do not meet the criteria.
View Replies!
View Related
Formula To Find The Filter Criteria
I have a worksheet which has in column A2:A9 a label and in column B2:B9 a value, like so: A1="LABEL" B1="VALUE" A2="LabelGreen" B2=452.47 A3="LabelBlue" B3=-87 A4="LabelRed" B4=9 A5="LabelGreen" B5=-7 A6="LabelBlue" B6=1888.97 A7="LabelRed" B7=144 A8="LabelGreen" B8=-0.02 A9="LabelBlue" B9=87002 I use the autofilter on column A to select only certain labels. in cell D1 I use the subtotal formula to give me the sum of all filtered values. D1=SUBTOTAL(9,B2:B9) This works fine, for example, if I filter on column A for "LabelGreen", the formula in D1 calculates 452.47 - 7 - 0.02 = 445.45. What I am trying to do now and what I have not yet found a solution for is: In cell C1 I want to display the criteria I have filtered for. For example, when I filter column A for "LabelGreen", I want "LabelGreen" to be displayed in cell C1. I was thinking something similar to SUBTOTAL might do the trick, for example: C1=SUBFIRST(9,A2:A9) But this only gives me (and I am not surprised) an error "#NAME?"
View Replies!
View Related
Find Data Depending On Two Criteria
I Have want two find the dates and the data of these dates depending on the two criteria i have note"the two criteria would be left(b3,4) and right (b3,4) ex in f3"date" i want to find the dates correspond with these two criteria and then find the data of these dates in G3 Depending on the two criteria too. Sheet1 ABCDEFG2DateData Criteria1Criteria2DateResult31/1/2008M111 GG1G M113 M191M1131/2/2008M191 G1GG M11341/2/2008M191 G1GG M113 1/5/2008M191 G78GG M11351/2/2008M111 GG1G M113 1/8/2008M191 G1G9G9 M11361/3/2008M141 GG5G M113 71/4/2008M118 GG6G M113 81/5/2008M191 G78GG M113 91/5/2008M141 GG5G M113 101/6/2008M111 GG2G M113
View Replies!
View Related
Find Range Of Cells Using A Criteria
I have a Range of cells in 1 column..E.g. Below A B B B A A B B A It continues on to a few 100k. Now I have a criteria which i will use to search for my range of cells. Criteria e.g. A A B B So i want to search for this particular pattern in my Long list of cells and locate the exact location of the pattern i specify. How can i Do this in Excel.
View Replies!
View Related
Find The Last Row Which Meets 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 (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 (??).
View Replies!
View Related
Multiple Criteria To Find A Value On A Worksheet
I am using 2 criteria on one spreadsheet to identify an get a value on another spreadhsheet. The criteria are Sex (Male or Female) cell C8 and Age (ranging from 0 -100) cell D8. I want the formual to Look Up the Life Expectency (from the other worksheet) The worksheet has colums that contain age, sex and life expectency. I can only get VLOOKUP to work with one criteria; =VLOOKUP(D8,"Mortality Table"!$B$10:$E$109,5,False) How can I find the Life Expectency utilizing 2 criterial, age and sex?
View Replies!
View Related
Find A Value In A Table Based On Other Criteria
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 Replies!
View Related
3 Criteria Find & Match
I have three sets of columns with addresses. I'm looking for a match/index/vlook up formula that can search all three columns and list the matched addresses in another column. Also note that one or two of the columns are formatted differently (although there is enough information to match closely to the other columns where relevant).
View Replies!
View Related
Count With Multiple Criteria And Find Permutations
I have a few calculations I cannot solve for life of me. I know you guys can figure this out. In the example attached you will see the list of the numbers that were drawn (More numbers will be added as time continues). I was able to use " countif" to find the total number of time each number was drawn. My delima, I need: 1. Count the number of times each number was drawn by person. John starting in cell J7 Mary starting in cell J11 2. I need to type in 4 digits in cell J19, and find all possible combinations from the list of numbers drawn List what combination of numbers that was drawn including the date it was drawn by person starting at cell I20 using the format as shown
View Replies!
View Related
Find Closest Match Based On Two Criteria
I 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.
View Replies!
View Related
How Do I Use Lookup To Find Data Based On 3 Criteria
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.
View Replies!
View Related
Find Last Row And Score Which Meets 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 (??).
View Replies!
View Related
Find Smallest Number With Multiple Criteria
I need a formula that will give me the smallest number based on multiple criteria. For example, I want to find all: 1. Product A 2. 1 Liter 3. Smallest Price In other words, out of all 1 Liter Product A's, I want to see the smallest price point. I tried a sumproduct but I can't get it to work. Something like =sumproduct((A1:A100="Product A")*(B1:B100="1L")*(small(C1:C100,1))) How can I get the small formula working within the sumproduct? Or is there another workaround?
View Replies!
View Related
Find A Value Based On Criteria In Columns And Rows
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?
View Replies!
View Related
Vba Find For 2 Criteria- Then Offset Other Information
I have a code where I am trying to do a find for 2 criteria. If both criteria are found in the same row I want to then offset more data on that row. My find code is not working. Here is what I have: Sub test() Dim str As String Dim str1 As String str = Sheets("Pt2Filter").Range("Repair_Description1") str1 = Sheets("Pt2Filter").Range("DateOfInvoice1") Sheets("Invoiced Pt 2").Activate Find_Range = ActiveWorksheet ‘search For items Set found_range = Find_Range("str", Columns("D"), xlValues, xlPart) For Each Cell In found_range If Intersect(Cell.EntireRow, Columns("A")).Value = _ "str1" Then... End Sub Whenever I try to run the code the part that I have as bold returns a Type Mismatch error.
View Replies!
View Related
Find Rows With Cells Matching Criteria
I want to read information from the "Mapping" sheet, then find information in the "GLEX" sheet that correspond to the read information, then copy the information that was found in "GLEX" to the "Calc" sheet and add it all together and display the sum in a particular cell in the "Report" sheet. The "Mapping" sheet is a mapping of information to show what makes up a particular total and where it must be entered in the "Report" sheet. The "GLEX" sheet is the output from a financial system and the source of the information that must be "reformatted" to the "Report" sheet. The information is only an extract as the whole totals to around 10MB. I have also deleted the "Report" sheet as it is fairly big on it's own, but the principal will be the same, I think. (If i should upload the "Report" sheet,) Step 1: "Column A" and "Column B" of "Mapping" are the starting points. The macro must read the values in "Mapping" "Column A" and "Column B" and remember them. Then it must find the row in "GLEX" "Column A" where the information from "Mapping" "Column A" matches and where the information from "Mapping" "Column B" matches with the information from "GLEX" "Column E". Step 2:......................
View Replies!
View Related
Use Of MATCH And INDEX To Find First And Last Instances Meeting Criteria
I am trying to write a formula which finds the last instance of a number greater than 30 in a column (B). The values are not sorted from smallest to largest as they correspond to a time series (A) which needs to be preserved. Ultimately, I want the formula to return the time at which this value occurs, but I think I can do that bit using INDEX I have a formula to give the first instance (row number) of a number above 30, which seems to work, but beacuse I don't fully understand how it works I can't modify it to give the last instance. The formula is: =MATCH(1,INDEX(--(B2:B883>=30),0),0) Having only just learnt how to use MATCH and INDEX, I thought each should have 3 arguments, so I'm confused about how the 4 arguments here work. Also I can't find much on using logic functions within INDEX. Can someone explain this formula, and suggest how I might go about finding the last instance please?
View Replies!
View Related
Find Top X Percentages With Duplicate Criteria Lookups
I have a matrix of suppliers which stores quality ratings against various service parameters. Customers then order their priorities and my intention is to then generate a report which lists the suppliers that meet their parameters. a method of generating this report (on ClientRecommendation in the attached) which lists the top 5 suppliers under each weighted parameter, together with their scores? I realise that the answer must lie in some combination of INDEX and MATCH, but I cannot get my brain around how to generate the row numbers, as top 5 suppliers often share the same score for a particular parameter. For example, if I set up a row lookup on 80% I can so far only get one occurrence of 80% and no others. This is probably not clear from my explanation, so I have attached the full example, from which it should be more obvious. I have also seen a very clever VBA solution (#60695), which I think may be the neatest method, but I am not knowledgeable enough to make it work for my case.
View Replies!
View Related
Find Data Based On Horizontal And Vertical Criteria
I have a spreadsheet that I am trying to create a formula for that will bring back the data found when you compare an X and Y axis. A sample is attached as the data is huge and I figured what ever you all created I could modify. I need it to bring back the data found when I run my finger down the column till I hit the appropriate row.
View Replies!
View Related
Find Value Matching Multiple Text Criteria And Max Date
I have the following dataset: [Date] [Category] [Currency] [ExchangeRate] [.....], etc. 1-3-09 A USD 0,8 1-6-09 A EUR 1 1-7-09 A USD 0,7 1-8-09 B USD 0,9 1-9-09 B USD <formula> I'd like to have the value of <formula> looked up in older records. Currency and category should match and it should pick the exchange rate with the maximum date. Which formula and what syntax should I use to have this done? I use Excel 2007.
View Replies!
View Related
Macro Code: Find Cell Or Row Multiple Criteria
I can't seem to find the answer to this. In VBA, how do you Find a cell or row based on multiple criteria? All I need is to find it's location. If it helps, here's an example: Sheet 1 contains source data: Date Name Time Complete Sheet 2 contains an interactive worksheet using this data. On Sheet 1 there's an entry: 9/12/2006 George 03:30 PM Sheet 2 displays those records with adjacent cells for entering data. When data is entered, I need the system to go back to Sheet 1, find the entry that matches this one, and put an "x" in the Complete column.
View Replies!
View Related
|
TRACKER
