Retrieving Column Header For Largest Or Second Largest Value In Row?

May 12, 2012

I have a table showing interest levels in training courses from a group of schools, eg:

English Maths Science
School1 3 4 2
School2 7 1 0
School3 3 2 5

I want to identify the column heading for the first, second and third most popular courses. ie for School1 the most popular course is Maths, second most popular is English and so on.

I have tried using the OFFSET function, which worked if I provided the cell location of the required value. I then looked at the ADDRESS function to provide the cell location: eg For School2 find the 2nd most popular course:

=ADDRESS(ROW(A3),COLUMN(data?)+MATCH(LARGE(B3:B5,2),B3:B5,0)-1)

But I have got stuck with what I should enter for COLUMN(data?) as I do not know in which column the second largest value is.

I am sure Excel has the required functionality.

View 12 Replies


ADVERTISEMENT

Find Largest Value In A Column

Jun 15, 2013

I am trying to write a macro in order to find the largest value in a column in one worksheet, and copy and paste that value into a different workbook. I have found a code similar to what I am looking for and tweaked it, but it needs a few more adjustments. The code below opens the file I want it to, and pastes data into the correct workbook and worksheet, but I would like it to be able to paste in the next empty cell in Row 3, instead of just in the cell "C3". Also i would like for the program to find the largest numeric value in column C, instead of using an if last row statement, as this current program does not always give me the output I am looking for.

VB:

Dim wsMaster As Worksheet, wbDATA As Workbook
Dim NextRow As Long, LastRow As Long

Set wsMaster = ThisWorkbook.Sheets("Contract Metrics")
NextRow = wsMaster.Range("A" & Rows.Count).End(xlUp).Row + 1

[Code] .....

View 1 Replies View Related

Highlight Largest Value In Column

Jan 6, 2010

I need the largest value in a column to automatically highlight and the old high value to return to normal.

View 12 Replies View Related

Find Largest Value In Column

Sep 2, 2008

if c2c1 do nothing.

05 =20-5
10 =20-10
15 =20-15
20 =20-20

View 9 Replies View Related

Top X Largest Values From Column

Jan 7, 2008

How would I find the four rows with the largest value in a defined column and then export them to a separate worksheet.

View 2 Replies View Related

Finding Largest Cell In A Column

Aug 17, 2009

I want to find the largest cell in a column so I can use “auto fit selection” without cutting off type.

View 9 Replies View Related

Find The Largest Number And Then Return The Name From A Different Column

Jan 15, 2007

I'm using the LOOKUP function to find the largest number and then return the name from a different column, it looks like this:

=LOOKUP(LARGE(round1!$F$2:$F$65,1),round1!$A$2:$A$65)

but all I get is hashN/A?

View 14 Replies View Related

If Formula: Pick Out Which Is The Largest Figure Of Column A,B,C,D For Each Row

Oct 14, 2008

See attached. This is a report that I have to do every month. I cant work out a formula for it. What I need is a formula that will pick out which is the largest Figure of column A,B,C,D for each row and put the corresponding header in column G. i have manually put these in. Would anyone know a formula i could use for this.

View 3 Replies View Related

Conditional Formatting (largest Number In Column B The Hotel In Column A Should Be In Bold)

Oct 18, 2008

when the largest number in column B the hotel in column A should be in bold.

So in excel language IF(Number in B Is Max display corresponding hotel in column A as BOLD. But I can't figure out how to do this.

You can see here on the image:
additionalimage.gif

View 4 Replies View Related

Compare Multiple Rows And Pick Largest Value From A Column

Dec 10, 2013

See data in the attached image in Col A thru Col D. In Col E, I want to populate the max value for the same part # from Col C or Col D depending upon value in Col B.

I have included a sample of the expected results in Col E for illustration purposes.

For example Row 3,5,6 are all part # 1 with Col B ="Y", suggesting they are interchangeable parts, therefore, I would like the formula to have ability to pick 10 (since its largest between 3,10,0) from Col C.

Capture5.PNG

View 1 Replies View Related

Extract Cells With Largest Number Of Digits From A Column

Mar 29, 2014

I basically I have a column with numbers. All the numbers are positive integers. What I like to do is have a VBA function that extracts the integers with the largest number of digits. So for example if we have the following column:

12

123

234

12346

2345

[code].....

So basically we search for largest number of digits, and extract the numbers that fit this category, which could be just one number or multiple numbers.

View 5 Replies View Related

Return Cell Address Of Largest & Smallest Value In Column

Jun 2, 2009

How would I return a cell reference (address) to a cell that contains the largest number in a list?

I tried using "Address(large....) where I get the correct column, but the returned row # is the actual value in the cell (the highest # in the list).

I eventually will want to delete the highest number to leave the cell blank.

View 3 Replies View Related

Find Largest Number In Array And Give Result As Column Heading

Aug 9, 2013

I'm trying to find the largest number in a row and then have the column heading (text) as the result. I can find the largest number by using =max(numb1, numb2 ....) but then how do I get the heading of the column as the result. An example of what I want to happen is below

Red
Green
Blue
Orange
Yellow
Result

2
4
3
6
1

[code]...

I anticipate an issue where 2 columns have the same largest number and not sure how to over come this either with multiple answers

View 4 Replies View Related

Extract Largest Values From Column B And Corresponding Text Values In Column A?

Oct 1, 2013

There should be a formula to extract the largest values and its corresponding values. For example, if column

Products Costs
A1 Pen 200
A2 Pencil 125
A3 Radio 670
A4 Apple 1500
A5 Xbox 222
A6 TV 100

The desired outcome

Products Cost
Apple 1500
Radio 670
Xbox 222
Pen 200
Pencil 125
TV 100

The second goal is extracting the top 3 largest values using a formula.

Sample file is attached for your convenience. Desired outcome is in GREEN.

View 6 Replies View Related

Sum X Largest

May 9, 2007

I have 3 columns. The first has names of students, the second names of their schools, and the third the student's scores. I need to add together the top 4 scores from each school and return them in a table. I have tried combining LARGE and SUMIF but to no avail. Also tried a Pivot table but I cannot get just the top four scores to add.

View 9 Replies View Related

Sum The Largest 5 Numbers On A Row?

Jun 13, 2014

I have been trying to sum the largest 5 numbers that are in a row

the number are in the columns stating c1, e1, g1,i1,k1,m1,o1 ect for about and other 15-20 columns, so there is a missing, so a lot of numbers

thing to note i am missing a cell between the cells i need to count/ sum ...

View 8 Replies View Related

Determine Largest Value

Mar 26, 2009

I'm working with box quantities and would like to get a formula that would take the box quantity and return the largest value based on some parameters. Here are my parameters:

Box Quantity

- 375 pieces

Break down options

2 - 187.5 pieces
3 - 125 pieces
4 - 93.75 pieces
5 - 75 pieces


Obviously options 2 and 4 are no good as I can only work with even break downs. This leaves me with either 3 or 4 and because I want the largest, I would choose number 3. What I need is help on a formula that will take the value of the box quantity and return the largest whole number. I think for the time being I would like to leave the break down between 2 and 5 as well.

View 5 Replies View Related

Lookup Next Largest Value

Nov 14, 2006

If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. But this is not correct as the value I am seeking falls with a range of Min/Max Values therefore the return should be the Next Largest Value

I have values I would like to lookup against a table Min Max values
Col A Min Value 700,000
Col B Max Value 799,999.99

The value being sought is 745,000

I would like the return to be 799,999.99
But currently returning 700,000

Current formula is
=VLOOKUP(G2,tblMaxValues,2,TRUE)
Where G2 is 745,000
tblMaxValues is a list of values for comparison
,2, is the column to be returned

View 3 Replies View Related

Sum Nth Largest Values

Dec 30, 2006

I need a formula that would return the sum of the largest 3 numerical values out of a range of values, i.e. ignoring the lowest values in the range.

View 5 Replies View Related

Sum Of X Largest Values

Jul 11, 2007

I have a row with 30 cells and each cell has a different value. i want to count the highest 15 cells of those 30, so the sum of the total only counts the largest 15 digits.

View 2 Replies View Related

Return Value Corresponding To Nth Largest Value

Apr 17, 2008

=LARGEST(overview!$AB5:$AL5;11)

I'm using this formula to look up a certain value in "overview" sheet, is there a way to not show the result in this cell but show as result the cell in the same column in row 3?

View 3 Replies View Related

Sum Formula To Add X Largest Numbers?

Aug 24, 2014

writing a formula to sum the six largest numbers from a row of numbers. I have used the array formula "=SUM(LARGE(E2:M2,{1,2,3,4,5,6}))" and it works for rows that have six or more numbers; however, not all of my rows always have six or more numbers. Rows that have less than six result in "#NUM!".

View 5 Replies View Related

Largest And Smallest Sequences

Jan 12, 2009

I am using excel 2007 and I need help with the following if anyone would be kind enough. I have a dynamic array that consists of stream of 1's and 0's. I want to be able to find the largest sequence of 1's and the smallest sequence of 1's. So at a point in time the array mite be 111110001101111000 and I want to be able to dertermine the longest contiguous sequence of 1's. But as I say the array is dynamic and so I need to do that after each input.

View 8 Replies View Related

Find The Largest Difference

Apr 24, 2009

I have a pivot table that shows values grouped as ranges in the row, Gender as the columns and an average % value in the data.

In a cell outside of the pivot I would like to be able to produce the name of the range that has the largest difference between male and female.

View 5 Replies View Related

Select Largest Value For Each Date

May 4, 2009

I have my data arranged in 4 columns as shown below (sample data). I need the Excel to find the largest value for each date and write them somewhere along with those dates. For example, the Excel should write in the 5th column (E) below two dates and values based on the sample data I listed below -

4/1/2007 - 0.044
4/2/2007 - 0.055

I have about 17-18 values for each date between 4/1/2007 and 10/31/2007.

Sample Data
========================================
A B C D
--------- ---------- ---- -----
Date SITE POCConcentration
4/1/2007 11001002510.039
4/1/2007 11001004110.042
4/1/2007 11001004310.039
4/1/2007 24009001110.042
4/1/2007 24017001010.044
4/2/2007 11001002510.051
4/2/2007 11001004110.048
4/2/2007 11001004310.052
4/2/2007 24009001110.053
4/2/2007 24017001010.055
4/2/2007 24021003710.055

View 7 Replies View Related

Largest Group Of Non Zeros

Mar 13, 2006

I have two sets of numbers..... In column B I have measured values. In column
A I have the times for which these valus occur. I need to determine whether
the measured values in B exceeded the max limit for more then a given time
(ie do not exceed a value of five for more then 15mins)

View 13 Replies View Related

Find The Largest Value Within A Formula

Nov 26, 2008

I need to find the largest value within a range that is less than the result of a formula from another set of values.

Something like this:

=MAX(E98:E115) that is <=(P63+((1.5*(P63-P61))))

View 3 Replies View Related

Find Largest Submatrix

Nov 29, 2008

i have a matrix of 1's and 0's. How can i use excel / which functions can i use to give me the biggest submatrix with just 1's in?

View 14 Replies View Related

Match Name To Second Largest Value Of Sheet?

Oct 29, 2012

I want to match a name to the second largest value of sheet.

The trouble is the top three valus are 125,113,113.

jason got 125
graham 113
james 113

when i do indexmatch formula it returns graham everytime as he is the first person to get 113 in the table. how do i create a formula to reurn

View 2 Replies View Related

Largest Sum Of Consecutive Numbers

Dec 9, 2013

with this Excel problem? I have a set of data of 300 some odd rows of numbers. I need to find 24 CONSECUTIVE values that add up to the HIGHEST sum? For instance,

2
2
0
0
4
2
0
0
1
8
5
2

View 1 Replies View Related







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