Return Text Associated With Maximum Value
If I have names in one column and amounts in the next column, how do I determine the maximum of the amounts and return the associated name from the other column?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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
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
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 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
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
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
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
Maximum Text From Range
I am trying to get the maximum from a range of text cells. The data in the cells consist of 00001 00003 00014 00028 00007 etc. the formula I am using is: =MAX(VALUE(Interface!E2:E20)) the answer that is returned is 15 and it should be 28. Any ideas on how I can fix this?
View Replies!
View Related
Find MAXIMUM Of Text
I want to know how to get the max of text. I am having sheet which is showing Codes for deliverables as A,B,C,D as an entry. I want to pick the latest code, means i want search the row for Max value, i.e is D and display it in cell.
View Replies!
View Related
Maximum Text Length With VLOOKUP
I use VLOOKUP with text (to return comments made by people that I have copied in another sheet). The problem is that sometimes, it doesnt copy the whole comment. Apparently there is a limit for the amount of text VLOOKUP can copy: after a LEN() test I have found that I cant copy texts longer than 255 characters. Is there a simple way to make the VLOOKUP work even if the text is more than 255 characters long ?
View Replies!
View Related
Find Maximum. Some Values Are Represent By Text
I have a column of values the values can be "T", 0.00 ->any number to the nearest hundredth. I thought I had this problems solved a while back but after having time to do some quality control I found that anytime there is a value of "T" the macro does not work. I have had some great help with this in the past to get as far as I have but I still cannot seems to get it 100%. I have attached a sample file. The result that I would like to see is in the cell BH145 is "16,17,23" without the quotes of course. When you open the worksheet you will see that the values of concern are precipitation values. These values are rounded to nearest hundredth. When there is a trace of precip however it is recorded as "T" therein lies the problem that I cannot seem to solve.
View Replies!
View Related
Finding The Maximum Occurrence Of Text Over Non-consecutive Cells
I’m trying to figure out how to show the highest occurrence of text in 10 cells which are not next to each other. Some of the cells will also be blank And the other thing is if there is a draw e.g. 5 2Z and 5 1Z I'd like it to show that, either with a word e.g. "DRAW" or anything really other than just putting whichever of the 2 "notes" in it feels like Attached is a spreadsheet (all other data deleted) that shows what I am wanting to do – I have typed the results I am after directly in to the cells in columns BE & BF – BE will need some kind of countif, but when I try that is says I have too many arguments! I have to leave the columns in between blank in this case as they have other info in them, which is really annoying as if those 10 cells were contiguous then the formula
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
Return Text From A Mixed Text/value 3d Array Result
I've been searching the forums and web trying to figure out how to extract a text value from a 3D array result, to no avail or well beyond my understanding. I've attached a file which shows an abbreviated version of the search (from the original 49 col x 400 row, which shows the name to date array which can return a sum. What I'm looking to do is identify within this array result if there has been a text entry, and return the text as opposed to a sum of the remaining values. This is essentially a double check to make certain that there aren't hours scheduled when other events such as vac/loa/etc are also booked.
View Replies!
View Related
Partial Text Lookup In Text String And Return
Cell H1 has a variable string of references for eg "FI570783AQ3516346EQ3516346FXVB123456" I want to return the reference beginiing with "FXVA" so it would ignore the rest and only return FXVB123456 - this should always be 10 characters. Just to add another complication to the mix, there may be 2 "FXVA" references in the string and i want to get both (these can be in the same string so FXVB123456FXEL123456 - but this might not happen regularly.
View Replies!
View Related
Return Text Based On Text Existing In Range
I am trying to Lookup a country to see if it is classified as a "Developed Country". My formula would be in cell AA4. I want to see if the country in cell B4 is on a list of developed country's on another worksheet. IF the country is found on that list cell AA4 displays Developed. If the country is not on the list, cell AA4 displays Emerging. I have tried an IF statement using the Match function and it does not work. =IF(MATCH(B4,'Developed Country List'!$A$2:$A$37,0), "Developed", "Emerging")
View Replies!
View Related
SUMPRODUCT To Return Text
The below table is a small extract from my data. I want the formula in C3 to return the contents of A4, that is, where Amount = D3, Policy = E3, Status A3. The following formula returns 0, because I'm trying to return a text string rather than a numerical value:....
View Replies!
View Related
SUMPRODUCT Use To Return A Text
If recently learned how to use the sumproduct function as a lookup for multiple criteria and it was working great, but I realize now all the values that I was returning were numbers. Is there a way to use the sumproduct under the same circumstances but to return a text value? COL ACOL BCOL C A1X A2F A3G B1E B2S I would want the lookup with conditions of ColA=A and ColB=2, so the return should be "F" =SUMPRODCUT(($A$1:$A$5="A")*($B$1:$B$5=2)*$B$1:$B$5).
View Replies!
View Related
If A Cell Has Certain Text Then Return Value?
How do i write an If function where if a cell has a certain text in it, it would return a value to onather cell. For instance: If cell B1 has "red" in it the cell B4 must be "6" If cell B1 has 'blue" in it, then cell B4 must be "7" and so on with certain text returning a certain value?
View Replies!
View Related
If Statement That Return A String Of Text?
not sure if an "IF" staement is appropriate? I have 3 columns containing data: column A has text in it, example: A2="red", A3="green", A4="blue" column B has 3 letter month in it and they could have repeats example: B2="Jan", B3="Feb", B4="Jan" C2="Jan" in D2 list anything column A that matches C2 example: D2="red, blue"
View Replies!
View Related
Return A Value If Cell Contains Rather Than = A Text String
I want a formula or function that will return a count value if a cell contains a string rather than returning a value if the cell equals a value. For example, =COUNTIF(A$1:A$19703,F1) returns a value to cells in column G if the value in column F equals any exactly matching value in A. I need a formula that works like the filter option "contains" so that if any cells in col A merely contain a string rather than exactly match or equal a string from column F I can get a count value returned.
View Replies!
View Related
Vlookup With Multiple Text Value Return
I'm trying to create a formula that returns with all the text values for all the conditions. So similar to a sumproduct where the formula returns a summed value of ALL the conditions i need the same but for text. hello no hello no hello yes goodbye no formula would return with (no, no, yes).
View Replies!
View Related
Array Formula To Return Text
I have an Array formula that returns a value from several rows below: =MAX(IF($P$8:$AQ$8="Today",$P13:$AQ13)) Row 8 contains only text and only one occurrence of the word "Today". Row 13 has only numbers. The corresponding value directly below the column containing the word "Today" is returned successfully. I now need a similar formula to return a text entry located in the same column but on the previous row (row 12) directly above the value just returned and this row contains only text entries. Using the above formula and referencing $P12:$AQ12 returns 0 as expected.
View Replies!
View Related
Look For Two Values And Return A Text String
Say I have a table like this: Value 1---Value 2---Name 1---------1---------Potential client 1---------3---------Loyal client 2---------5---------Happy client Now I have two separate columns with values for "Value 1" and Value 2". How can I tell Excel to look in the table above and return the name?
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 Lowest Based On Text
I am looking for a formula that will return the lowest value in a range, expressed as a letter. The relative values of these letters are: F=0 P=1 M=2 D=3 For example, If in cells A1:A4 we entered the values F, P, M & D in A5 the result would be F (as it is the lowest value in the A1:A4 range). I have included an example worksheet with fictional data and the intended result in the Final Score column.
View Replies!
View Related
Return Text Depending On Another Cell
I have a very long excel sheet. What I want to do is, depending on the input in one column (text), return a another text in another column, for the whole sheet. I tried to with Select Case Statement and if-then, but couldn't figure it out since I don't have much VBA experience.
View Replies!
View Related
Return Text Based On Lookup Value
I have a database of customers that are all sorted by a customer number. I have a form that has a number of fields that display customer info. When I use a blank of this form and put the customer number in it's correct field, all the other info fields do a vlookup on the customer number in the database file and return the info, such as phone numbers, name, address, etc. Here's my question; How can I check for a blank (using ISBLANK, I assume) to check to see if the customer number exists in our database and, if it's NOT blank, operate on it with the following example which is used to return their insurance expiration date. =IF((VLOOKUP(B9,'[carrier list.xls]Sheet1'!$A$2:$P$1276,3,FALSE))<TODAY(),"EXPIRED",VLOOKUP(B9,'[carrier list.xls]Sheet1'!$A$2:$P$1276,3,FALSE)) I realize that this may be a sledgehammer approach and that Access is probably a better tool, but my company has not shelled out the $$ for Access...yet.
View Replies!
View Related
|
TRACKER
