Finding MINIMUM Based On Adjacent Criteria
Sep 2, 2009
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 5 Replies
ADVERTISEMENT
Jan 2, 2013
This formula allows me to find the lowest value in column U where column N contains the text "NO".
{=MIN(IF($N$2:$N$10000="NO",$U$2:$U$10000))}
I want to add another condition so that the formula only returns the lowest value in column U where (i) column N contains the text "NO" and also (ii) column F contains the text "YES".
View 11 Replies
View Related
Jun 1, 2007
I am trying to use the DCOUNT function to count based on two different criteria in adjacent columns is this applicable.
Situation:
I have column B that contains initials and I have column C that contains a type (of store) so it'd look like this
jp RET
jp RTO
jb RET
ma RTO
Rn CI
ma Fur
tc CI
ma RET
jp RET
and I want to have a count of how many RET's there are for Initials JP ect
View 6 Replies
View Related
Mar 7, 2012
I need to be able to find a value in another sheet based on 2 criteria
The criteria is a number from a column on my first sheet where the results will reside, the second criteria is based on a fiscal year (calculated based on the year of the current year).
The formula I use is as follow:
=LOOKUP(BA2,Budgets!B:B,Budgets!I:I)
and this is what I use to get the column:
=+INDEX(Budgets!B11:B200,MATCH(Budgets!B11:B200,Budgets!B11:AB200,0),3) getting #N/A here
and I use this too:
=MATCH(IF(MONTH(TODAY()) < 7,YEAR(TODAY()),YEAR(TODAY())+1),Budgets!E:Q) getting #N/A here too
If i keep it like that no problems but I will need to change the criteria 2 every new fiscal year.....
What I need is to replace the criteria 2 with something that will find the proper column wher the data is the fiscal year is listed on E11:Q11..
I have tried index and match and quite frankly, I never made it work, always getting an error.
View 9 Replies
View Related
Apr 24, 2014
I have 2 columns of data in Excel which I have brought a small section of it below. As can be seen the values in the left column have a large spike (difference of more than 10) at certain points (in this case at 34). I wan to find the average of the numbers in the right column but only till the point where the large spike happens (in this case the average of the first 5 numbers). I've tried AVERAGEIF but it's not what I want. I want this to repeat for the entire column and give me the averages of each of these groups.
10
32.4
11
38.6
12.5
23
[Code] .........
View 2 Replies
View Related
Jun 15, 2006
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 4 Replies
View Related
May 21, 2007
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 9 Replies
View Related
Dec 11, 2008
Say I have 2 columns that in basic form look like this:
Column A Column B
Jan 1
Feb 0
Mar 7
Apr 4
May 15
Jun 2
Jul 5
Aug 4
First I want to look up the max value in this column. This is easy =max(b1:b8)
Then I want to know the minimum value that occurs after the maximum value. Thus the answer would be 2.
View 3 Replies
View Related
Nov 8, 2012
I tried several iterations of sumproduct, small, and few index matches. no luck.
Several thousand rows, column A is name, column B is city, column C is $.
I want to see the max, min, median for all first names.
=SUMPRODUCT(MAX((Name=Joe)*($)))  seems to work fine
=SUMPRODUCT(min((Name=Joe)*($)))  will not work, zeros come through, even if there are no 0's for Joe
=SUMPRODUCT((Name=Joe)*(SMALL($,1)))  wrong answer
View 5 Replies
View Related
May 21, 2013
how do I find the non zero minimum in a column of figures, I want the figure that is greater than 1 returned?
View 7 Replies
View Related
Feb 28, 2008
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 9 Replies
View Related
Apr 29, 2014
i have with noting the most recent date of a test and also the one prior to the most recent dependant on location and test type. I require most recent and previous test info to enable a comparrison to be made. All other dates are not required but need to be kept as historical data and can't be deleted.
Is there anyway i could get column E to auto populate and amend itself as more test dates and locations are added to the list?
View 1 Replies
View Related
Jan 27, 2009
I need to find the minimum difference between any two elements in a row or a column. While it's easy to do for a 34 elements by doing subtractions for all elements in the array, doing it for more elements leads to a very long formula.
For example, I need to find the difference between any two elements between C5 and C9: ....
View 9 Replies
View Related
Aug 15, 2013
Sheet 1, column B range is B4:B39 & B54:B85 Data will look like this
2077
2077
2079
2055
31251
4333
Or very similar to it, I want to be able to find in order smallest to largest across 6 cells, meaning in cell c4 I want 4333, d4 2055, e4 2077 and so on for only 6 cells as that is all I have room for on the sheet. of course if there are only 2 sets of data then 36 will be blank. but I think if I can get something working I can add that part.
View 4 Replies
View Related
Jan 13, 2008
I am trying to get an average from one column based upon criteria from an adjacent column. The number of days to close a case for race columns Black and White are listed in B5:C16 and E5:F16 and H5:I16. I need a formula to calculate the average days taken to close cases for Males and then the same for Females. Sample below: ...
View 12 Replies
View Related
Apr 18, 2006
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 2 Replies
View Related
Dec 17, 2009
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 2 Replies
View Related
May 9, 2008
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 5 Replies
View Related
Apr 4, 2013
I have a list of data:
2
140
1
660
10
140
0.92
660
2
130
0.18
660
4
510
0.44
820
4
510
I want to have a formula that finds the sum of the values in Col 1 (Qty) for the rows that equal, eg: 140, in Col 2 (Product) So that I can have a list of Products of the Qty that relates to each product. (there are products in increments of 10 from 10 to 920, that is, 92 products)
View 2 Replies
View Related
Apr 5, 2014
What I would like to do is return the total qty of items on the 'QUOTATION' tab that are upholstery and this having been determined by whether there is a Y (for Yes) or N (for No) against the equivalent item in the 'BASIC_LIST' tab.
So in the below example, the total of Upholstered items would be 182 as items 'ST11' to 'ST13A  Tray' do not have a 'Y' against the matching item references in the 'BASIC_LIST' tab.
I would like this Y/N column to be the determining factor as opposed to any other criteria or filtering. (Also the Items column in the 'QUOTATION' tab is only ordered in the way it is for ease of view for this query!)
Picture 2.png Picture 3.png
SUM VALUES OF ITEMS THAT HAVE ALREADY MATCHED ANOTHER CRITERIA.xlsx
View 4 Replies
View Related
Nov 27, 2008
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 2 Replies
View Related
Dec 10, 2009
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 5 Replies
View Related
Jul 12, 2008
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 9 Replies
View Related
Jan 4, 2010
Attached is a print screen. I'm struggling with using the min function in vba. I want it to find the minimum cumulative cost in week 0 out of the first three, and the copy the permutations of it (1,0 or 1, 1 , e.t.c.) to Week one column C & D of the model.
View 3 Replies
View Related
Jan 5, 2009
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 4 Replies
View Related
Mar 11, 2014
I would like to find min value in column B for 1 order number (they are sorted Asc). Values must be shown in column C (currently are in column C expected values).
Example table:
No.
A
B
C
1
Orders
Values
[Code]....
View 2 Replies
View Related
Aug 1, 2012
I need a code or macro which, when applied to a selected column, will merge a range of cells in that column into one cell. The criteria is to merge all cells into one cell that fall directly under the cell which contains "Items  total..." and that lines are separated by a line break (alt+enter).
Here's an example of what the column contains:
Before code:
A1: Items  total: 2
A2: fourlegged
A3: cat
A4: twolegged
A5: chicken
A6: Items  total: 1
A7: fourlegged
A8: dog
A9: Items  total: 3
A10: fourlegged
A11: pig
A12: twolegged
A13: eagle
A14: marine animal
A15: shark
After code:
A1: Items  total: 2
fourlegged
cat
twolegged
chicken
[Code] .......
View 1 Replies
View Related
Jul 7, 2009
I'm looking to use MIN to look at a range of data in L:L based on matching certain criteria in A:A. In the attached spreadsheet in Totals!C1 I would like the MIN value from List!L:L for user "CHRISTOPHERJ" in List!A:A
View 3 Replies
View Related
Jan 1, 2008
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 9 Replies
View Related
Mar 28, 2007
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 10 Replies
View Related
Apr 28, 2013
I need to return the values in Column A IF any of the adjacent cells (columns) contain a number.
Sample data and expected Results...
Sheet2 Â ABCDEFGHIJK1DateData1Data2Â Data3Data4Â Data5Â ResultÂ
201/01/20132Â Â Â Â Â Â Â 01/01/2013Â 302/01/201311Â Â Â Â Â Â 02/01/2013Â 403/01/2013Â 1Â Â Â Â Â Â 03/01/2013Â 504/01/2013Â Â Â
13Â Â Â 04/01/2013Â 605/01/2013 06/01/2013Â 706/01/2013Â Â Â Â
1Â Â Â 08/01/2013Â 807/01/2013Â Â Â Â Â Â Â Â 11/01/2013Â 908/01/2013Â Â Â Â
1Â Â Â 12/01/2013Â 1009/01/2013Â 13/01/2013Â 1110/01/2013Â Â Â Â Â Â Â Â 15/01/2013Â 1211/01/2013Â Â Â Â Â Â 2Â Â Â 1312/01/2013Â Â Â Â Â Â
1Â Â Â 1413/01/2013Â 1Â 1Â Â Â Â Â Â 1514/01/2013Â Â Â Â Â Â Â Â Â Â 1615/01/2013Â Â Â 3Â Â Â Â Â Â 17
I cannot use VBA, Advanced Filter or a Helper column, but I could use one additional cell to hold a count, so, the solution can only be a formula.
View 7 Replies
View Related
Jun 23, 2008
I want to count no of "yes" in Range Column B only if corresponding values in column A is less than 10/06/2008
Please see the attached file for refrence
View 5 Replies
View Related
Apr 28, 2009
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 2 Replies
View Related
Feb 12, 2010
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 4 Replies
View Related
Apr 30, 2014
In the picture below, I need a formula in column E to count the unique occurrences in column A (excluding blanks) if its corresponding value in column B (B1 value) matches that in column D (B2 value). Currently column E is showing the values I would want the formula to return.
Capture.PNG
At the moment I have a formula as below:
=SUMPRODUCT((B:B=$D2),(A:A<>""))
this will do a countif in column A if column B matches the value in column D, but would not weed out duplicates for me.
Modifying my formula. I have attached the sample workbook below.
Book1.xlsx
View 7 Replies
View Related
Jun 11, 2014
I have developed the following code to colour cells dependent on reference to adjacent criteria (in Col D).
Is there anyway I can simplify this code to avoid three separate "For each" statements?
Code:
For Each c In Range("E12", Range("e" & lastrow))
If c = "" And c.Offset(, 1) "" Then
c.Interior.ColorIndex = 7
[Code] ..........
View 2 Replies
View Related
Aug 13, 2008
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 5 Replies
View Related
Feb 24, 2009
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 6 Replies
View Related
Sep 26, 2011
In a table 1 column are dates and another are machine parts. The dates are when that machine part broke last and a lot of the machine parts are repeated. Something like this
Column 1......Column 2
11/19/2010..Part 1
4/13/2010.....Part 1
9/13/2010.....Part 3
1/20/2010.....Part 2
10/11/2008...Part 2
2/1/2011.......Part 3
I'd like to find the last date the part broke. so the newest date. It would be nice if excel had a =maxifs() button. Can this be done without using a sub?
View 1 Replies
View Related
Dec 24, 2009
I want in A1 to find minimum SUM if no minimum number in row.
Here is example attached: ...
View 9 Replies
View Related