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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Finding Top 2 Values Per Group Based On Multiple Criteria
I am trying to find the top two values per group based on multiple criteria. The list I'm working with is not sorted and would be better for it to not have to be sorted as onthefly sorts will likely often occur from the raw data and I wouldn't want that to mess up the results I'm looking for here. As an Example, here's what I'm trying to do: Make Model Rating Ford Bronco 64 Chevy Corvette 94 Dodge Intrepid 83 Chevy Chevette 34 Dodge Viper 72 Ford Escape 21 Ford Expidition 53 Chevy Impala 67 Ford Fairmont 11 Dodge Dart 33
View Replies!
View Related
Finding Data Based On Row & Column Criteria
I have a main soure data which consist of row & column information. What i want to do is search the data from the source data into my result data as per the attachment file. Example: I want to information of Jan & banana from the main source file to appear in the XXXX Result data(criteria base on Month & type) JanApril BananaXXXX Apple Orange
View Replies!
View Related
Finding Minimum Value Using Multiple Criterias
I have managed to make a work queue and lots of other stuff for the model, but I can't get it to take orders in the way I want it. Each order has a order number (from 1 to 100) and the orders come in almost randomly e.g. 3, 5, 11, 2, 7, etc. What I want to do is to take the smallest available order that has not been processed in. The available orders column and processed orders look something like this: A B C D Time, Available, Processed, Start processing 5 2 0 2 10 0 0 0 15 0 0 0 20 0 0 0 25 5 0 0 30 0 0 0 35 0 0 0 40 0 0 0 45 4 2 4 50 0 4 0 55 7 0 5 60 6 5 6 Zero means no new orders or no processed orders. Now the Start processing column should select the smallest not processed order if previous order has been processed. A have, for now at least, all other problems solved, but can't figure out how to get start processing column check for the smallest not processed order line. I have tried combination of Min and Max functions with If, but it soon requires too many Ifs to make any sense out of it. I also tried the Dmin function, but it wasn't up to the task becouse the model requires ~1000 lines and as Dmin only takes criterias vertically I ran out of columns . So how could find minimum from row one until current row excluding values processed so far and only checking orders available so far?
View Replies!
View Related
Finding Data In Adjacent Cells
I'm using this formula (in conditional formatting) to compare a cell with the previous cell (e.g. E11 compares to D11). If it is higher/ lower/the same, E11 is coloured accordingly. =MATCH(E11,Grades,0)<MATCH(D11,Grades,0) =MATCH(E11,Grades,0)>MATCH(D11,Grades,0) =(D11) However…. sometimes the data is not in D11 but C11 or B11. How do I tell excel to first of all look in D11 and only if the data is not there, to look in C11, then B11?
View Replies!
View Related
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
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 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 The Minimum Time And Maximum Time
NameTime InTime OutAlan08300930Alan10001030Alan12301630Tony11301230Alan09450950Tony10301115 I would like to find the minimum time in and maximum time out for each person. The data type of Time In and Time Out are general. I.E NameTime InTime OutAlan08301630Tony10301230 Therefore, I would like to know what function in excel will enable me to perform such task. Furthermore, can this function use with VBA?
View Replies!
View Related
DSUM Adjacent Criteria
You can see there is an equation for: The total profit from apple trees. The maximum profit of apple and pear trees. But, there is no example for the total profit from pear trees. I have an excel spreadsheet that I need DSUM to work not only for A1:A2 (apple tree), But for A3 (pear tree), A4 (another tree), ext. But I don't want it to sum A1:A4 (A1 + A2 + A3 +A4), I just want A4's sum. Or from the example, I want just A3's sum for just pears, but when I put in A1:A3, I get the some for both apples and pears.
View Replies!
View Related
Calculate Price Based On Minimum
Trying to create a Math formula that allows for Taking advantage of sale pricing. In Excel the following: Qty to Order (G3), Std Package(I3), Price per Unit(J3), Sale Price(K3), = Extended Price(L3) Example 2case X 40peices per case,1.00Reg Price,.90Sale Price = 72.00 Not quite sure how to handle the Sale price taking over regular pricing
View Replies!
View Related
Minimum Returned Based On Matching Values
I am trying to do a spreadsheet for my boss and one piece of it has me stumped. Based on same "die name" I need to return the minumum quantity available. So, in column D i have a multitude of die names. Some of them match for those that match, I need to return in another cell the smallest quanity available in our die bank (column p).
View Replies!
View Related
Copy Data From Another Sheet If Adjacent Cell Meet Criteria
I have a have excel problem Im trying to figure out. I have 2 sheets. Sheet1 and Sheet2. I want to copy into sheet1 cell B2 the contents of sheet2 column C row x iff sheet2 column B row x = 1. There will be only one cell in that column equal to 1, so I will be copying that cells(colB; rowx) adjacent cell (colC;rowx) contents. This is what I have so far but it doesnt work. =IF('Sheet2'!B:B=1,DONTKNOW WHAT TO PUT,""))
View Replies!
View Related
Finding A Cell Adjacent To A Blank Cell
Ok, I've come close to the solution using the Match function, but somehow just can't quite nail it. What I need is a formula that will search down Column B until it finds the first blank cell. Then report what is directly to the left of that cell. So If the first blank cell is in $B$5, it will report what is in $A$5.
View Replies!
View Related
Maximum And Minimum Formulas Based On Date Range
I have a spreadsheet with two columns  column A contains dates and column B has the corresponding data (always numbers). The data is not uniform (i.e there may be lots of entries in any given day and none over the next few). The data is always sorted in chronological order. I want to set up a summary table to show the min, mean, median, max for each week. I have set up an array to calculate the mean, but I am having trouble working out a good way of gettting the others short of having a set of columns that manually define each cell...for instance =min(b3:b27) or =min(b28:b30).
View Replies!
View Related
Variable Cell Reference Based On Minimum Difference
I have a monitoring system that records a data point with a date/time stamp several times a day at random intervals. For each reading I want to calculate the change compared to the first reading that was more than 24 hours ago, which could be anywhere from 1 to 20 rows above the current one. Hence with the timestamp in col A and the value in col B, the formula in col C, for example cell C20, needs to read something like =B20Bxyz, where xyz is the row number of the first reading that is more than 24 hours, i.e the first row xyz where A20Axyz >1.
View Replies!
View Related
Finding Average With 2 Criteria.
I have 2 rows of data 1. title 2. value eg Row 1: Title1, Title2, Title1, Title4, Title2, Title1 Row 2: 2 , 3 , 0 , 1 , 2 , 6 In the above I am looking for the average of Title1, where the value is greater than 0. There are 3 occurances of Title1, but only 2 have values. The answer i am after is Sum is 8, average is 4 (ie 8/2 and not 8/3)
View Replies!
View Related
Finding Median With Criteria
how would I find the median of a group of numbers? I have a database with a group of numbers associated with a part number. I need to find the median of each part. Here is what I have tried, but it doesnt work =MEDIAN(IF(A:A=A2,B:B))
View Replies!
View Related
Finding The Maximum Value Of All Those Meeting A Certain Criteria
Problem: Range A2:C10 contains the login and logout times of various ID's. Each ID could log in and out a number of times a day. How could we find the first time a specific ID logged in and the last time that same ID logged out? Solution: For each of the ID's in range A2:A10, enter two Array Formulas. To find the first login time (Column B) enter the following formula: {=1/MAX((A14=$A$2:$A$10)*($B$2:$B$10 To find the last logout time (Column C) enter the following formula: {=MAX(($A$2:$A$10=A14)*($C$2:$C$10))} ID______Login Time______Logout Time 1 ______02:40___________03:10 2 ______00:15___________03:20 1 ______06:20___________09:30 3 ______09:14___________11:05 4 ______11:00___________19:30 2 ______04:05___________06:55 3 ______12:08___________17:17 1 ______10:00___________16:20 2 ______08:12___________12:33 ID______First Login Time______Last Logout Time 1 ______2:40__________________16:20 2 ______0:15__________________12:33 3 ______9:14__________________17:17 4 ______11:00_________________19:30
View Replies!
View Related
Finding Specific Row With Criteria
I have a very large database of quotes. I have created a user interface with several textbox inputs, combobox inputs, and checkboxes. When the commandbutton is pressed I need a list of quote numbers to be generated based on the criteria the user input. I found an example program from here that is for ADVANCED EXCEL FIND. It only uses combo boxs and goes to those rows on datasheet. I have text input and checkbox inputs as well and I don't want it to take the user to the rows, I want just the quote numbers from the rows to be sent back to a textbox. I also read over one based on filtering data in a listbox. This is my first program in VB, but I did quite a bit in C++ before. I can pretty much understand what all the coding says, I just am overwhelmed with it being so large and not sure how to put it all together.
View Replies!
View Related
Finding The Nth Value If Another Column Meets A Criteria
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.
View Replies!
View Related
Delete Columns After Finding Criteria In Row
I am trying to delete columns or clear their contents after I meet a certain criteria in row 4. For example: I have one " Total" in row 4 that can be in any column. I wanna be able to delete whatever comes after "Total" in any row or column. Is there a macro that can do that?.......
View Replies!
View Related
Finding The MAX In A Group Of Cell Depending On A Criteria
I'm trying to find a way that I could get the MAX value in a group of cells depending on a critieria. The situation is that I have a list of numbers and a list of dates parallel to it. I would like to find the MAX for, say, the numbers corresponding to the month of June. I would like to be able to change the month and get the MAX for each month on request. Eg. 18MAY2007 600 01JUN2007 500 02JUN2007 400 28JUN2007 600 12JUL2007 700 17JUL2007 200 I need formula that will give me "600" if the month I want is June. Ideally I would not want to have to filter the data, so SUBTOTAL would probably not work for me.
View Replies!
View Related
Sum Based On Adjacent Column Value
I have a spreadsheet with a list of account numbers and values(sheet1) and on a seperate sheet (sheet2) a list of all unique account numbers that appear in the transaction data sheet. What I am trying to do is get a total sum for the values of each account number. What I am getting well I'm not quite sure what I'm getting. The problem appears to be when the xSubtotal variable is reset to 0 at the end of calculating all the values for the account number.
View Replies!
View Related
Conditional Formatting Based On Adjacent Column Value
I'm trying to highlight cells a certain color based on the value of the cell 1 column to the left. How would I be able to do this in conditional formatting? Example) Highlight the cells in "Visit 2" column if it is greater than the "Projected Visit 2" column. NameProjected Visit 2Visit 2Person 124Nov0820Nov08Person 226Jan0927Jan09Person 32Jan082Jan08Person 430Dec0829Dec08Person 46Nov0830Oct08
View Replies!
View Related
Sumproduct Based On Adjacent Cell ID Code
I am trying to get the sumproduct of a column of amounts based on the adjacent cells ID code. For example column B will have codes 1FHD, 2FHS, 3FHG etc. these codes will be mixed otherwise I would have just did a sum of the cells. Column C would have dollar amounts. Sumproduct based on adjacent cell ID Code
View Replies!
View Related
Combining Cells Based On Value Of Adjacent Cell.
I have been reading through trying to find a similar post but the nearest i could find was one dealing with numbers rather than text. This is how the workbook looks. Column A contains a list of numbers based on vehicle types. Column B contains a list of vehicle names. A B 1 Car, 1 Taxi, 2 Bus, 2 Lorry, 3 Skateboard, 3 Bike, 1 Motorcycle, What I am looking for is the best way to combine the text from column B depending on the value of column a. the finished results would then end up as: "Car,Taxi,Motorcycle,", or "Bus,Lorry," etc. This is just a simple list.. The one I am working with has around 2500 entries. I have been trying to do this using the CONCATENATE function and IF statement but it's just not working..... My head is ready to explode
View Replies!
View Related
Calculation Value Based On Month Name In Adjacent Cell
I have a spreadsheet spread over with dates in column A from September to December and I have a fuel rebate column as well. The fuel rebate is different for each month and is calculated as a certain amount of cents per dollar added to the total amount in Column B September: 26c October: 13.5c November: 43c December: 66c e.g. If there is an invoice for 10/9/07 (september) for $100, then the fuel rebate would be $100*.26 which equals $26 fuel rebate. I want to calculate column B (this is the column with the dollar amount in it), but I want the calculation to be different depending on what date is in column A, so if in column A the dated month is October, then the calculation would be something like B2*0.135. give me the calculation I am certain you don't need VBA for this right (just nested IF statements?)
View Replies!
View Related
Indent Data Based On Adjacent Symbol
I am trying to do some automatic formating of a list of data. I can accomplish 90% of what I would like to do via a simple keystroke record macro. What I cannot do is shift the contents of Column B based on the contents of Column A. Column B will have to be shifted to the right 1, 2, 3 . . . cells. Below is an example. Column A symbols as it relates to number of cells to shift Column B Symbol......Amount of shift []..........0 ( ).........1 [].........1 ( )........2 []........2 ( ).......3 I have included a sample .xls showing the Original Data and the Desired Output
View Replies!
View Related
Populate Adjacent Cell Based On Pull Down Choice
I have three hidden columns (Text only) one column with Parts, then Description, then Cost. I have a pull down menu (i.e. Cell 3, A) listing all of the choices in the hidden Parts column. I want to automatically populate Cell 3, B with the corresponding Description when a Part is chosen from the pull down menu, and the same in another column for the Cost. I am using Excel X for Mac, in OS 10.5.2
View Replies!
View Related
Delete Rows Based On Adjacent Blank Cells
I would like a macro that deletes rows based on having two blank cells in adjacent columns. I have achieved this with the following code however i need it to only delete rows below a certain row. How would i achieve this? Sub DeleteBlankARows() Dim r As Long For r = Cells(Rows.Count, 30).End(xlUp).Row To 1 Step 1 If Cells(r, 3) = "" And Cells(r, 4) = "" Then Rows(r).Delete Next r End Sub
View Replies!
View Related
Find Value Specified & Calculate Based On Adjacent Cells
I am really stumped at this particular point in my coding. What I am trying to do is to have a user input an ID number. When they click OK, I want to find the ID number and then subtract 1 from a number that is 13 columns over. So if the ID number is "123456789" and that matches the value in A3, I want to subtract 1 from the amount in M3 (leaving the new value in cell M3). So, if M3 was 30, now it would be 29.
View Replies!
View Related
Index & Match Multiple Items/Criteria: Finding The Nth Occurrence
I've read the how to for finding the nth occurrence using index/match but the example given does not really help solve my issue. The file I've attached is a condensed version of the actual file, which has more columns but I deleted all but the necessary ones for clarity. What I am trying to accomplish: On sheet1 there are three columns, Business, Amount, and Closing Date. Not all the business names have a closing date and the spreadsheet is sorted alphabetically by business name, so sorting by closing date, and using the method used in the topic " find the nth occurrence in excel", is not an option. On sheet2, I would like to see ALL the business names that have a closing date in the respective month, as opposed to just the first. Then to the right of the business names I have the sum of all the amounts in that month, but I figured out how to get that one already.
View Replies!
View Related
Sum Range Based On 1 Criteria Of Column & 2 Criteria Of Another
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct((A1:A10="Yes"),(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10) I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))} I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message Function Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String) Customer = Sumproduct((Service = Outcome),(Service2 = Outcome2), Result) Didnt get thru this bit to start building on the Function! keep giving me #Value!
View Replies!
View Related
Conditional Formatting Date Cells Based On Entry In Adjacent Cell
I have a spreadsheet with 'due date' for returns in column L and 'received date' for returns, in the adjacent column M. I would like to get the due date to turn red when it is overdue, ie. the date in the cell has arrived and no return was submitted. By trawlling the forum, I managed to accomplish this with the following in conditional formatting: Cell value is less than or equal to =TODAY() All's great.....but  I would like the date to turn back to black if I enter a date in the adjacent received column, column M. In other words, I want to flag a problem if the due date has passed, but once I receive the return and enter the date I receive it, i would like the due date entry to revert back to the original formatting ie. black. So only the outstanding returns are highlighted.
View Replies!
View Related
Compare Dates In Table & Adjust Adjacent Value Based On Result
I have built a macro that tests to see if a date in a range is before the Period Start Date, and if it is, then the labor rate in that same row (3 Columns Over) should be escalated by the appropriate AWI. My code below works, but I am wondering if there is a better way than using offset(0,3) to prevent a code breakdown if columns are inserted between the Date and Rate columns. The date range is names "DATES" and the rate column in names "RATES" Sub CountTheCells() Dim cell As Excel.Range Dim i As Integer For Each cell In Sheet2.Range("DATES") If cell.Value < Sheet2.Range("POPS") Then i = i + 1 End If Next MsgBox ("You have " & i & " Rates that will be escalated b4 the POP Begins") yesno = MsgBox("Would you like to Escalate the Dates and Rates?", vbYesNo) If yesno = vbNo Then MsgBox ("Not Done") Else..........................
View Replies!
View Related
