Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










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:
Dcount Function/Formula: Count Based On Two Different Criteria In Adjacent Columns
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 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 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 on-the-fly 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 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 The Minimum Value In A Column That Comes After The Maximum Value
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 Replies!   View Related
Finding Minimum Difference Of All Elements In One Row Or Column?
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 3-4 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 Replies!   View Related
Average From One Column Based Upon Criteria From An Adjacent Column
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 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
Using MINIMUM Based On Conditional Data
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 Replies!   View Related
Count Adjacent Cells Meeting Date Criteria
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 Replies!   View Related
Minimum Function In VBA: Find The Minimum Cumulative Cost In Week 0 Out Of The First Three
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 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 =B20-Bxyz, where xyz is the row number of the first reading that is more than 24 hours, i.e the first row xyz where A20-Axyz >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 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 Certain Criteria And Moving It To Column
I need to look at column B find anything that Starts with BBX (ie; BBX124343, BBX1348383 ) and move it at the same location only to column A


View Replies!   View Related
Finding The Nth Greatest Value Meeting Criteria
I can get the result. It shows #num! in cell F2


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 Last Row In Data Row That Matches Criteria
have two worksheets. sheet1 has order information on it with orders, dates, customer names. sheet2 has customer name list. How can I (via vba) search through the order sheet and find the most recent order date for each customer in the customer name list. post that most recent date next to the customer name on sheet2.

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.
18-MAY-2007 600
01-JUN-2007 500
02-JUN-2007 400
28-JUN-2007 600
12-JUL-2007 700
17-JUL-2007 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
Find Minimum SUM If No Minimum Number In Row
I want in A1 to find minimum SUM if no minimum number in row.

Here is example attached: ...

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
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 124-Nov-0820-Nov-08Person 226-Jan-0927-Jan-09Person 32-Jan-082-Jan-08Person 430-Dec-0829-Dec-08Person 46-Nov-0830-Oct-08

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
Select Cells Based Value In Adjacent Column
How would you select cell values from differing rows based on the name in an adjacent column. ie, if it says John, add that data to the source data, if not move on.

View Replies!   View Related
Value Of Cell Based Adjacent Drop Down List Value
The easiest way for me to explain it is to give an example: if the list contains Red,Blue,Green,Yellow. i need a formula for the adjacent cell that inputs £1 if red is chosen, £2 if blue is chosen, £3 if green is chosen, £4 if yellow is chosen

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
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
Gathering The Sum Of Cells Based On The Text In The Adjacent Cell
I have a column of dollar amounts (variances) and next to each is the shift they belong to.

I am looking to get a break down of the variances for each shift.

Attached is an example of what it looks like.

View Replies!   View Related
Module To Find & Move Based On Cell Adjacent's Set Value
ABCDE
FGHIJK

If the above is in different cells, I want to be able to move the data in "G" to the cell beside "E" (up a row and over 4 columns) IF the cell to the left of "G" equals what I specify is in "F" (for example use "product"). I would want to apply this to an entire sheet, not just once. If the macro (not sure what its called) could also then delete the row that contained "G" after it moves it, that would be even better! The output in the example above would be:

ABCDEG
F HIJK

View Replies!   View Related
Count Unique Cells Based On Adjacent Column
I have a worksheet with following data in 1 tab.

[TABLE]
Seq Class
SE110
SE270

SE110
SE370
SE310

SE110
SE170
SE370
[/TABLE]

In a different tab I want to calculate the unique count of sequence for a specified class.

In above eg: for class 10 it should return 2

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
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
Copyright © 2005-08 www.BigResource.com, All rights reserved