Return Text Associated With Maximum Value

Jan 8, 2008

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 2 Replies


ADVERTISEMENT

VBA Macro To Return Maximum Value In Text Form..

Jul 15, 2009

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 4 Replies View Related

Return Only Unique Text From Another Sheet Along With Correlating Minimum And Maximum

Sep 16, 2009

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 4 Replies View Related

Lookup Maximum Value & Return Corresponding Row

Nov 28, 2006

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 2 Replies View Related

Formula To Return Maximum Of Certain Data

Jul 4, 2012

Formula to return the max of certain data.

This is data for an electric car. T= Trip and C=charge.

Column F is the parking time between Trips (T).

For each "T" event in column E, I would like to return the MAX parking time available before the last "T" event for that day (date). I've highlight the last "T" events in red.

I have attached an example : forum2.xlsx

Column G is an example of the output that I would like to achieve.

Note: For the last "T" event of the day it can just return the actual parking time (shown in green).

View 3 Replies View Related

MAXIMUM Function Limit: Return A Value?

May 8, 2009

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 5 Replies View Related

Return Maximum Date Or Blank

Dec 9, 2009

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 5 Replies View Related

Return MAXIMUM Based On Criteria

Aug 13, 2008

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 5 Replies View Related

Return Maximum Value Among Multiple Records With Same Key Using VBA

Mar 1, 2013

I am writing a VBA function that will search for a value in a column(say column B) in a table. This column can have duplicates. So my search needs to get all the records that match the value (including duplicates) and from these records i need to return only one value among records fetched that has maximum in another column in the same sheet(say column D).

Eg :

sno dept name marks r1 cs sush 55 r2 ece ram 68 r3 cs harish 77 r

So if i give "cs" value to search it must give me 77 as output. I tried to write my own Lookup function but isn't working and getting #value.

View 6 Replies View Related

Return Data Corresponding To Minimum & Maximum

Nov 26, 2008

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 9 Replies View Related

Lookup Maximum & Minimum. Return Corresponding Row

Nov 2, 2006

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 4 Replies View Related

Return Heading Of Column With Maximum Value

Sep 16, 2007

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 4 Replies View Related

Return Corresponding Cell For Maximum In Another Column

Oct 22, 2007

* I hav two columns B and N having some data. From column N i need to find the Maximum valued cell.

* And now i need the content of a cell in column B in the same row , which matches with maximum value.

View 2 Replies View Related

Return Values Either Side Of Maximum Value

Jan 23, 2008

How can I select specific values either side of a peak value?

In the attached file I have a peak value of 10 and would like to select both values of 5 so that I can then identify the frequency they occur at.

View 5 Replies View Related

Return Heading For Maximum Value Of Each Row & Color Max Row Value

Mar 8, 2008

I have inventory in 9 cities. Cities names are in B2-J2. Line items are rows 3-100
In column K, I have the max formula for that row. What I need is, in column L, the name of the location city from row 2 where the max qty is located also color the max qty in the row.

View 2 Replies View Related

Return Cell Address With Maximum Value

Mar 13, 2008

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 2 Replies View Related

Return Cell Address Of Maximum Number?

Jan 20, 2009

i'm looking to return the cell address of the max number in a given range?

View 3 Replies View Related

How To Return The Maximum Value In A Multiple Results LOOKUP

Dec 11, 2008

What I am try to do is look up the highest (maximum) date in a lookup up that has multiple results.

Please see attachment.

Column in question is 'U' (FinishDate)
Maximum date that I want to recover is in column 'P' (DATE_COM)
LOOKUP VALUE is found in column 'A' (QUOTE_NUM)

View 6 Replies View Related

Return Maximum Value For A Range If Word Is Found

Mar 13, 2014

I have an Excel Worksheet with 80,000 lines on it. The Columns are arranged thus:

Col A
Col B
Col C
Col D
Col E
Col F

Code
Description
Cost
Rate
Mobile

0.13

1234
Australia Mobile
0.12
Not Mobile

[Code] ........

I am looking for an Excel Formula that will look up Australia & Mobile and then return the highest value of the range of cells it finds that meets that criteria and enters that value in the Rate column next to mobile (F3).

Then I just want a variant of the same formula that will look up Australia but exclude Mobile and then return the highest value of the range of cells it finds that meets that criteria and enters that value in the Rate column next to Not mobile (F4).

View 1 Replies View Related

Return Column Number Of Maximum Value From A Table

Jan 2, 2010

I have a got a table ( 10x30) with random numerical values.

How can I return a column number with maximum value?

Values will be changed periodically so the maximum number could be in different column each time

Example:
A-B-C-D-E-F-G
1-2-3-4-5-6-7
2-6-8-18-1-1-8
2-5-6-9-12-1-7

The results will be 4.

View 9 Replies View Related

Return Maximum Value In Range Stated In Cells

Nov 18, 2007

What is wrong with this formula

=MAX(ADDRESS(VALUE(D2),1),1 & ":" & ADDRESS(VALUE(D1),1))

when D2 is 2, D1 is 6 and

=ADDRESS(VALUE(D2),1),1 returns $A$2

View 9 Replies View Related

How To Search Dataset And Return Maximum Value Based On 3 Using (array)

Dec 6, 2012

I want to search a data set and return the maximum value based on 3 criteria using (array) formulas.

Say the data set contained the following:

Row 1: column headings (date, name, data 1, data 2, data 3, etc)
Column A: dates
Column B: names
Columns C through G: data

I want to find the maximum value for a given name, in a certain month, and a nominated data column (e.g. data 2). For example:

Month = June
Name = Geoff
Data column = Data 2
Max value = ????

All of the data in the data columns is numeric.

View 4 Replies View Related

Return Values In Range Based Upon Relationship To Maximum Value

Jan 22, 2008

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 5 Replies View Related

Find MAXIMUM Of Text

Jun 21, 2007

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 9 Replies View Related

Maximum Text From Range

Mar 17, 2009

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 9 Replies View Related

Require Maximum Number Value Containing Text?

Jul 12, 2013

I've got a table where I want excel to generate the next number. With the following table excel must generate an invoice number (column B). I've got the formula to work correctly until a payment is done as shown in B12,B13 and B14. I need a formula to lookup the maximum invoice number (SC1-0007) and in cell B15 the serial number SC1-0008 must be shown. [URL]

View 1 Replies View Related

Maximum Text Length With VLOOKUP

Dec 7, 2009

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 9 Replies View Related

Maximum Of Text & Numbers - Alphanumerics

Jul 31, 2008

Is there a vba function that can determine that "1B" is > then "1A"? Max only works with numbers apparently, but I know that MS Access' version of Max does this.

View 2 Replies View Related

SUM MAXIMUM Or Index/Max: Count Number Based On The Maximum Time??

Oct 23, 2008

I was hoping that my formula would give me the count number based on the Maximum time (latest time) and the Name field...My result is a 0 instead of 62 (the correct answer).

=SUM((Download!$H$2:$H$10=A4)*(Download!$D$2:$D$10=MAX(IF(Download!$H$2:$H$10=$A4,Download!$D$2:$D$1 0)))*Download!$I$2:$I$10)

Would a Index/Match/MAX function be more efficient?

View 4 Replies View Related

Maximum Text String In A Column And Index Against It

Sep 24, 2013

I have trying to find the maximum date value in a series of campaigns with the same name [Combnined Campaign (Campaign)] , the aim is to pick the lowest version of the same name if the campaign is connected and pull the max end dates and number of weeks [Week Number Combined Campaign] that overall campaign has been running. A spreadsheet below explains this too.

What I need is to search for all the campaign names in column [Combined Campaign (Campaign)] and find the matching week number. What I am currently using is this formula:

=IFERROR(IF(E2="Yes",INDEX($A$1:$K$26,MAX(MATCH(F2,$G$1:$G$26,0),1),COLUMN($K$1:$K$26)),""),"")

Yet Max doesn't work with text.

Campaign Appeal Code
Launch Month
Market
Product
Combined Campaign (Yes/No)
Combined Campaign (Campaign)
Channel
Start Date
End Date
Week Number
Week Number If Combined Campaign

AAB13
Jan

Yes
AAB13
Mail
01/01/13
31/01/13
Complete

[Code] .........

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved