Return MAXIMUM Based On Criteria
i have a spreadsheet like the following
A B C
Country Revenue Month
1 UK 10 Jan
2 France 20 Jan
3 US 30 Jan
4 UK 25 Feb
5 US 35 Feb
6 France 5 Jan
and so on...
So where country = UK, France or US I want to retrieve the MAX revenue from all months and which month it was in. Eg UK max revenue was in Feb of 25. I am not sure how to apply the max formula with criteria. Is there any way to do this?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Return Values In Range Based Upon Relationship To Maximum Value
I am looking to select two values from a range based upon their relationship to the maximum value. In this specific case I am trying to calculate the Quality factor based upon the frequency response of a transducer. So I need to identify the Maximum impedance and its corresponding frequency. I also need to identify the 3db points either side of the maximum impedance. The 3db points are found at 2/3rds of the maximum value of the impedance (Impedance Mag in attached spreadsheet). Calculating the first 3db point isn't overly difficult as I just calculate the value for the 3db point and use the MATCH function to find the closest Impedance value and then work out the corresponding frequency from that. I am stumped as to how to calculate the second 3db point (above the Max impedance value). I have attached a spreadsheet with frequency information in column A, the other columns are used to derive the values found in columns M (Impedance Mag) and N.
View Replies!
View Related
Identify/highlight Maximum Value Based On Multiple Criteria
I have a file with telemetry data for n individuals. For each individual I have data on a number of days, over the course of a couple of years. On any given day, I have from 1 to several "fixes" for an individual. Fixes range from 1 to 3, with 3 being the "best". Note that I do not necessarily have data for all individuals on a given day. What I want to do is extract the row with best fix for each individual, on each day for which I have data for that individual. If there is more than one best fix for an individual on a given day, then I want to choose the first best fix (temporally). Right now my data is set up with columns: INDV DATE TIME FIX ....other attributes INDV = unique code identifying the individual The data is sorted by INDV, then by DATE, then by TIME. I figured I could do this using nested IF statements, where "TRUE" would be added to a new column (e.g., HIGHFIX) in the row of the first highest fix on each day for each individual, but have not been successful in doing this. I've also tried conditional formatting. Again, no success here.
View Replies!
View Related
Return A Text Value Based On Criteria
I'm trying to create a formula that will allow me to pull test from a list (auto populate if possible). Essentially you will see on the second tab, a list of projects "Cali" for example. I'm trying to find a formula that will allow me to show the Customer and Life Cycle on the first Tab. If possible the Project Name too. Essentially I want to be able to have all the data inputed into Tab 2 and let Tab 1 condense it into the designated fields. So basically what will allow me to see all of the "Cali" projects, and from that generate the Customer and Life Cycle (and Project if possible) on Tab 1. Keep in mind this does need to be automatic updating, so that as we input more information on Tab 2, it will automatically kick into Tab 1.
View Replies!
View Related
Return Text Based On Criteria
Example: I have 2 sheets, a pivot and a data sheet. When selecting a different option on the pivot i want information returned from the data sheet (which is explanations of the information contained in the pivot) I need to add 2 criteria points.
View Replies!
View Related
Return Result Based On Criteria
if Cell F42 contains 50%, then my fomula returns "wrong" =IF(AND(0%<F42,F42<50%),"F",IF(AND(50%<F42,F42<60%),"D",IF(AND(60%<F42,F42<70%),"C",IF(AND(70%<F42,F42<80%),"B",IF(AND(80%<F42,F42<100%),"A","wrong"))))) how do I get the formula calculate correctly?
View Replies!
View Related
Return Result Based On Multiple Criteria
I have been trying this for hours but to no avail. I have a table with 4 columns headers Name, Amount, Loc and Code The name may look like ABC 1, ABC 2.... The Loc may be in US, GB... and the Code may be AA, BB I need to return a result "Y" if the sum of the amount is > 100 and "N" if the sum of the amount < 100 based on the conditions of the following :if 1) Name is the same entity, such as ABC 1 and ABC 2 and 2) Loc is the same, US..and 3) Code is the same I have attached a sample to illustrates the result
View Replies!
View Related
Return Data To Another Sheet Based On Criteria
Sheet 1 Column A = Dates Row 1 = Filenames I enter a Y in the intersecting cells of the Filenames & Dates to show which files were downloaded. I need is a formula that will rearrange this layout onto Sheet 2 so that each ‘Y’ (downloaded) filename is inserted in a cell corresponding to the Date. (see attached sample)
View Replies!
View Related
Return Multiple Results Based On Criteria
Please see the attached sheets. Here's the issue: On sheet "master" I have a list of goals in column A. Each has been assigned to at least one person. Each person will have their own sheet that will capture the goals assigned to them. As an example, see Al's sheet. I am trying to get Excel to look for all of the goals assigned to AL on the Master sheet and list them nicely onto AL's sheet without out blank rows. (and for each, respectively on their own sheet). I could do this with Pivot Tables if the data were displayed differently, but I have been told that I have to display the goal assignments as shown. I could use "x"s instead of their names to mark the assignment and I could combine the "goal-achieved expectations" and "goal #s" columns if it helps. I am not supposed to use filters either. Note: Of course, a goal or an assignment could change on the master sheet, but the assignment is more likely to change. how this done for AL, I can go ahead and create the other sheets.
View Replies!
View Related
Return Data Based On Lookup Criteria
I have an array in excel, 4 columns: Month, Day, Stock, Price. I dont know the most efficient way to handle arrays. I know i can loop through an entire array to find a specific value, but if i have a large array, this doesnt seem efficient. For example, I would like to find the price in an Array (or Range), when i know the value of two columns. If this were in a database, I would write sql like this: " select Price from TABLE where month = 1 and Day = 3". The result would be 40.21. How can i do this with an array? I have attached a sample spreadsheet with the example mentioned above..
View Replies!
View Related
Return Multiple Values Across Columns And Rows Based On Criteria
I have a table with column headings of product ID Numbers (eg.1111) and row headings of Store number (Eg.1) with data showing the time each product was last sold at that store, I need something to consolidate for each store which Product ID's were sold prior to 5pm and what time they were sold. EG Store 1 1111 16:40 2222 13:00 Store 2 1111 15:05 3333 16:50
View Replies!
View Related
Formula/Function To Return Multiple Values Based On Criteria
I would like to be able to use the Vlookup function to return more then one value as a result of the criteria. I have a cloumn of dates that populates the X axis of a gantt chart with data whilst the Y axis will be populated by a site reference resulting in a program of work, the Y axis data is the result of a vlookup function. My difficulty arises however with multiple sites, for instance where two or three sites will be visited on the same day. The vlookup function will only return the first value it finds in a range to the formulated cell. The result being a missing site(s) from the gantt chart / work program. is it possible to return all values to a cell i.e. site1, site2, site3. using a vlookup or do i need to use another method of doing this?
View Replies!
View Related
Query Worksheet & Return Rows Based On Conditions/Criteria
I am trying to query a names sheet where each row may contain more than one occurence of a member ID. There are no duplicate rows ( records), because the dates are different for all rows. 1. I want to retrieve all of the rows from the names sheet that match a unique member ID. So far I only get one row. 2. And if mistype the number, it gives me a wrong record .I would like to get an error message that if I don't get a match, that it appears in the somewhere like dialog box or even entry in one of the cells that no name exits.
View Replies!
View Related
Lookup With Multiple Criteria: Return The Amount Paid And Full Cost Based On The Person's Name And The Date
I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate. Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
View Replies!
View Related
Maximum If With Multiple Criteria
I want to find the maximum in column C if Column A = b but I don't want the number if it equals 11 or 9. So, the answer should be 7, but I don't know how to create the correct formula..I'm sure I have to nest a couple of things into an array, but I just can't seem to get it to work. ABC a17 a34 b27 b711 b109 c93 c86 d78 e44 e92 e31 f27
View Replies!
View Related
MAXIMUM Function Limit: Return A Value?
What I'm trying to do is get the MAX function to return a value, but that value needs to come before the values reach zero. It's a cubic equation that basically starts, at the beginning of the data range, under 10 then hits zero and ends up near 30 at the end of the data range. The end data range max doesn't matter to me if the data range hits zero along the way. Is there any way to make the MAX function stop at a certain value, or is there another function that could accomplish what I'm trying to do?
View Replies!
View Related
Return Maximum Date Or Blank
I am currently using an array to return the MAX date value. This formula will enter 1/0/00 if the referenced cells are blank. I'd like the furmula to LEAVE THE CELL BLANK if the referenced cells are blank. {=MAX(IF(Bid_Circuits=$A45,Bid_Trim_Completed,""))}
View Replies!
View Related
Return Heading Of Column With Maximum Value
In attached example, I have 3 columns of fractions with a final row of sumtotals (taken from elsewhere). I test to see which column has the maximum value in each row and allocate the result with the column's heading. Sometimes, two or more columns have the same max value. In this scenario, I wish to select the column heading that has the largest sumtotal.
View Replies!
View Related
Return Cell Address With Maximum Value
I have two columns of numbers in column A & B. In column A I am trying to locate the maximum value and get the cell address of the number next to it in column B. I am currently using this formula to do this ..... =ADDRESS(MATCH(MAX(A1:A4),A1:A4),2,4) It works ok, but if the maximum number is the first value in the column it is returning the last cell address in column B. I am not sure what I am doing wrong.
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
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
Return Data Corresponding To Minimum & Maximum
Please see attached file which is data for horse races (this is a small example of the data i will be working with). I need to find the largest values from the MIN and MAX column (shaded Grey) range for each race. I want to be able to add a function that will allow me to quickly identify which horse has the largest number in both the MIN & MAX columns example on spreadsheet is highlighted RED. It would be handy if it would inform me if the criteria has been met by highlighting it or by placing some text value in an adjacent cell on my spreadsheet i have used the example "Y". It would also be beneficial if that race be deleted in its entirity if there are no horses which meet the criteria above or similary if all other selections within that race be deleted if ther is a horse which meets the criteria. If there is a selection within that race i will need to keep the row that includes the heading which is coloured blue on the attachment. I will be working with 20,000 or more selections at a time so it is important that they can be identified quickly for ease of use. I have tried the functions i know such as MAX and LARGE but this requires me to do alot of work when you consider the ammount of data i'm required to work with.
View Replies!
View Related
Lookup Maximum Value & Return Corresponding Row
i have a matrix index| a| b| c| 0001 |1| 2| 2.22| ( probably it will be much clear to see the attachment) I need to lookup for the largest number in B2:D2 (in this case 2.22) and return the corresponding header ("C"). I though it would be simple with hlookup() and max() but i can't make it work.
View Replies!
View Related
VBA Macro To Return Maximum Value In Text Form..
I am a novice to VBA in excel, i have a table of more then 5000 rows. I would like to find a maximum value from each row and return the column title as "GL_Weld" or "Bend" or "Header" into another worksheet cell. i.e First Row max value = 0.011510 should return "Bend" into another worksheet GL_WeldBendHeader 0.0001700.0115100.000500 0.0000000.0007800.000000 0.0002400.0110700.000480 0.0000000.0006600.000000 0.0000000.0037700.000000 0.0000000.0000100.000000 0.0002800.0108700.000480 0.0000000.0007100.000000 0.0002000.0112900.000490 0.0000000.0008400.000000 000.00002 00.000310.00001 000.00004 00.00030.00002 000.00001 00.000270.00003 000.00002 00.000260.00004
View Replies!
View Related
Lookup Maximum & Minimum. Return Corresponding Row
I have searched your forums and thought I had found a sufficient answer but could not get the vba to work. So any help is greatly appreciated. I am trying to determine a max value from a list then put that value in a cell. Next I want to determine how many times and on what day that max value occured. From there take the value and concatenate them adding a "," between them I have attached an example. I would like the values placed in cells F1 and H1 (the other is a min value and when it occurred)
View Replies!
View Related
SUMPRODUCT With 2 Criteria, But One Is A Maximum Number Of Items To Include
I have no problems figuring out SUMIF or SUMPRODUCT with multiple criteria, but I'm trying to help someone with an issue that the second criteria is actually not all-inclusive. Column B - Lots of numbers, the SUM range Column C - Yes and No flags. Y or N C1 = maximum number of matching items to include. So, =SUMPRODUCT((C2:C100="Y")*(B2:B100)) sums up ALL the Yes rows, but the guy wants to restrict it to the last X matches, and put that X factor in C1. So if C1=5, only the last 5 Y matches are included.
View Replies!
View Related
Return Only Unique Text From Another Sheet Along With Correlating Minimum And Maximum
I have, on one sheet, a list of incumbent employees that includes the following columns: Job Title [text] and Salary [number]. I am trying to automatically populate a second sheet with columns designating: A list of each unique job title (no duplicates), the maximum salary corresponding to each unique job title, and the minimum salary corresponding to each unique job title. Lastly, I'm an attorney dealing with confidential client data, so I'll have to go through a lot of trouble to post an example... but I'll do it if necessary.
View Replies!
View Related
Maximum Based On Condition
function that can find the maximum value in a range, where the range is defined by the rows containing a specific string, i.e: In column A: Alm Rød Alm Grøn Alm Blå Special Rød Special Grøn Special Sort ... In Column B I have values for each of the above string names. Now I would like to find the maximum value for the range with names starting with "Alm".
View Replies!
View Related
Finding The Maximum Value In A Column Based On A Condition
This should be simple to do but I can't figure it out. I have a database that lists operating room numbers in one column and the length of the surgeries performed in those rooms in another column. I need a formula that will give me the longest OR time for a given room. For example the room numbers are in column A and the OR times are in Column B. I've tried something like
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
Mark Maximum Value In Range Block Based On 2 Columns
I have a long list of stock values (col C) arranged in time blocks so; CREATE TABLES LIKE BELOW?B C M N 19:00 50.00 0 47.1 19:00 51.00 0 47.5 19:00 52.00 1 48.3 19:00 51.00 0 47.3 19:10 36.00 0 45.3 (nb time has changed) etc I need a routine that takes the following action; When the value 1 occurs in col M row .. (it will only occur once in each time block) and N1 on the same row has the highest value [ 1 and 48.3 above] put 1 in column O on the same row else a 0. If 1 occurs in column N (time block X) and the value in N is not the highest then store a 0. This needs to be done for each time block. It feels like this can probably done by an If or vlookup type of routine but I can't come up with one that works across blocks of times.
View Replies!
View Related
Return Maximum Of Active Cell & Arbitrary Value To Cell
I am looking for VBA that will add the value of the current active cell on the sheet to the value in cell F12. The maximum value of F12 cannot exceed 1000. So if the value in F12 = 950 and 100 is the value in the active cell the maximum value in F12 should show 1000, not 1050. It should do this on the click of a button.
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
Using Three Criteria To Return A Value
way to use three criteria - date, account, method - to identify values in a column of deposits to sum. The spreadsheet consists of the following: The first colomn is a mixed list of dates in different months deposits were made. The second column is the account the deposits were placed in. The third column is the method used to make the deposit The fourth column is the amount deposited. What I'm trying to achieve is 1. to sum all the deposits made in a particular month by the different methods into a summary cell for each month 2. to sum the amounts deposited into each account each month into a summary cell for each method I did try to copy a section of the spreadsheet using the html maker but couldn't copy it.
View Replies!
View Related
Return A Value If 2 Other Criteria Match
Rather than attempt to describe my problem here and risk cofusing people on what I want to achieve I have put a diagram together. I think this is the best way to illustrate my problem. Diagram is available here [url] There is also a copy of the document available here for any body willing to take a look. [url] Please bare in mind I need this doc to be compatible with the 2003 version of Excel.
View Replies!
View Related
Return A Specific Value From Three If Criteria
I have 3 named range columns to query and a fourth from which I wish to return a value Column 1 is called DateOE, column 2 is called NameOE and column 3 is called RunOE. The column from which I require the value is named ConcOE. I have the following formula: =IF(AND(DateOE=28,NameOE="Wayne",RunOE=1),ConcOE,"No Data") My logic dictates that the formula should return whatever was run by Wayne on run number 1 on the 28th day from the values within ConcOE or return the value No Data. The run numbers are unique, which is the identification key. Every time I try it out, I have a #Value returned and if I convert to an array, the value no data is returned, despite the fact that I know what value should be retuned.
View Replies!
View Related
Using 2 Criteria To Return 1 Sum
I have a a stock list with the following headings: Location, Part Code, Qty this is the source data for my next spreadsheet. This is baically a total stock sheet. I need to extract data into my second sheet looking by part code . I would like the formula to lookup against my master stock sheet and bring me back a sum of the total stock for part code ABCD, which is in location 1. My finished speadsheet will have 21 products over 10 locations, and the master stock sheet will be amended daily which will hopefully update this summary sheet.
View Replies!
View Related
|
TRACKER
