Free Microsoft Excel 2013 Quick Reference

Max Value in a Row

Hello All,

I am trying to find the largest value in a row 78 and once that value is found place the column header name (In Row 2) for that largest value found back to the cell. Then I will find the 2nd largest value in the same row... etc The following commands work (Sometimes):

=OFFSET(INDEX($78:$78,MATCH(LARGE($78:$78,1),$78:$78,0)),-76,0,1,1)
=OFFSET(INDEX($78:$78,MATCH(LARGE($78:$78,2),$78:$78,0)),-76,0,1,1)
=OFFSET(INDEX($78:$78,MATCH(LARGE($78:$78,3),$78:$78,0)),-76,0,1,1)
=OFFSET(INDEX($78:$78,MATCH(LARGE($78:$78,4),$78:$78,0)),-76,0,1,1)

In the case where there is more than one cell in row 78 with the same value the function returns the first cell that satifies the equation returning the same column name. I would like to be able to step to the next column with the same value and return that columns name. So in the case where there are three columns that have the same value in row 78, I would like to see the names of all three columns.

Any ideas? Any help would be greatly appreciated

Regards

Scott


Post your answer or comment

comments powered by Disqus
Hello.
I would like to find a maximum value across a row of 24 and return the title
I have given that column.
So, if the max value of row R is in cell R20, I would like the function to
return the name in cell A20.
Thanks,
Josh

I'm trying to format a cell to return the maximum value within a row of data that meets a certain criteria.

Ex:

A1...B1...C1...D1...E1...F1...G1...H1...I1
29...38...31....98...30...87...28...32...27

I'd like to format cell J1 to find the max. value of cells A1 thru I1 that is less than 40 (in this case the answer would be 38), discounting any cell with a value larger than 40.

I tried using DMAX, If, MAX, FOR, etc. (albeit, probably incorrectly).

Any help would be appreciated.

hi
im a biginer to the vb and facing the problem of finding the row having the max value in a column in excel using vb6.0

Hello.
I would like to find a maximum value across a row of 24 and return the title
I have given that column.
So, if the max value of row R is in cell R20, I would like the function to
return the name in cell A20.
Thanks,
Josh

Hi, i need to find the maximum value in a row and enter that value into a specific cell. I also need to do this with the minimum value in a row.

Any ideas?

thanks

I am writing a macro to find the max value in a spreadsheet and move that
value along with the value that is next to it and store it in a new cells.
For instance if row 3 contains the max value I want to move A3 and D3 to F2
and G2.

I am trying to find a way to highlight the highest value in a row.

example

wedding 1000 99 870 255 876 234 1000 456

In column 2 i have used the max formula to return the highest value in the row, now i want the cell highlighted from where the max formula has found the source.

I know i can use coding to highlight the cell but i have 10,000 rows of information and the coding only works by highlighting each row individually.

Is there a way to do this?

Please help

Phil

i am an architect and am trying to organise a drawing issue sheet.
I have drawing revisions listed along a row, with corresponding revision status at the top of each column
I would like a summary to appear - the first part to show the latest drawing reference issued (highest revision value), and the second part to return the corresponding column header.
i managed to find a formula online to return the highest text value in a row (i am not an excel expert so i am not sure how this works - but it seems fine)

=INDEX(D3:L3,MATCH(MAX(INDEX(COUNTIF(D3:L3,"<"&D3:L3),0)),
INDEX(COUNTIF(D3:L3,"<"&D3:L3),0),0))

I would now like (in a separate cell) to return the corresponding column header from this cell, but can't work it out.
I have attached my draft document which is probably easier to understand than my (rambling) description.

max text value and corresponding column header.xlsx

Any help would be appreciated.

Andrew

I'm trying to find the last non zero value in a row. My data looks like this:

# J F M A M
A 4 5 5 5 5
B 0 0 3 3 3
C 0 0 0 0 2
D 0 0 3 3 0

I want to compare the earliest value to the latest (e.g. for A, that would mean 4-5 = -1; for B that would mean 3-3=0, etc).

I have a formula that finds the first non zero value in the row, but have been unsuccessful in writing one that finds the "last" non zero value in the row.

Any ideas?

Thanks,
cparaske

Hello,

I want to get the last value in a row. I have absolutely no idea how to do this. I have search this site and Google but i am still unable to work it out

Would appreciate any assistance

Thank you
Michael

Hi guys

Can someone please help me out with finding the last value in a row?

I've tried to use: -

=OFFSET(A5,0,(COUNTA(A5:Z5)-1)

but this doesn't seem to do the job properly.

For each row, the values are in different columns. For example, there is a value in A5, D6, D7, A8 etc...

Hope I've explained well enough...

How do I return the column label from the first value in a row. My columns are labelled by week numbers and I need a formula that tells me the first week that a customer ordered?

I am trying to find out how to find the last value in a row. Here is how my spreadsheet is set up: I have dates across the top row and each row under it has returns...

A1 = Name, B1 = Latest Month, C1 = Jan 07, D1 = Feb 07, E1 = Mar 07
A2 = Mike, B2 = ?, C2 = 1%
A3 = Steve, B3 = ?, C3 = 2%, D3 = 5%
A4 = John, B4 = ?, C4 = 0%, D4 = 8%, E4 = 10%

I want column B to return Jan 07 for Mike, Feb 07 for Steve, and Mar 07 for John. Is there a function that can do this? Thanks for any help you can provide.

Does anyone know of a way to count the number of unique values in a row of data (numeric)?

Good afternoon,

I am trying to write a formula which can calculate the total value of cells with '£' values in a row with '£', '$' and 'Euro' values.

Can you help? (as you can see I am a complete novice at all this)

Many thanks,
Novice999

A B C D
1 -5 -15 40 -20
2 -50 -7 -3 60
3 50 -30 -10 -10

Is it possible to flag the 2nd highest value in a row.
example Row #1 -5
Row #2 -3
Row #3 -10 & -10 (both are the 2nd highest)
If so, can that cell be bolded or highlighted.
I do not know VBA, formulias Ok.
Thanks in advance.

I am trying to perform a simple simulation of a project network on a
spreadsheet with the aim of recording the distribution of the critical
paths (the paths, not their duration. But I use the path duration as a
criterion).

I am trying to do so by assigning a number to each path (1, 2, 3, ...,
n) so when each path takes a maximum value its corresponding path
number appears in a sell.

Path duration is a sum and recorded in a list. Next to it I use a
column with the formula =IF(MAX(critical path)=path number,path
number,"")where "path number" can take the value of the different path
numbers (1, 2, 3, ..., n). Below I use a cell recording these numbers
using the function =MAX(path number).

My problem is that in some cases, when the simulation is performed,
there may be more than one critical paths, but I am not aware of that
since I am using an add-in and not performing simulation using a table.

Hence, is there an efficient way of having Excel keep log of a tieing
critical path (i.e. the SECOND MAX value) in a different cell (so I can
simulate its distribution and then add it to the original distribution
of critical paths)?

THANK YOU

PS Should require a spreadsheet to assist you in helping me I would be
happy to provide it.

How do you display the highest numeric value in a row?

Example:

Row 1:
A 100 B 200 C 300 D displays "300", highest number on row 1.

lookup value or something????????

thanks!!!

Hi,

I used the below formula to find the max value in a column, and increment it
to the selection cell.

If Application.Count(Columns(1)) Then
MaxValue = Application.Max(Columns(1))
Else
MaxValue = 0
End If

If Application.CountA(Selection) = 0 Then
Selection.Value = MaxValue + 1
Else
If MsgBox("There are values in the selection. Are you sure you want to
replace?", vbQuestion + vbYesNo) = vbYes Then
' Check OK or Cancel, If OK, replace, if Not OK, abort
Selection.Value = MaxValue + 1
End If
End If

Example:
A1 has value 1
A2 has value 1
A3 has value 2

If I select A4, and click my macro button, it will put in value 3

My concern is like this:
If I select A3, it will prompt the mesg that if I want to replace it, then
if I click yes, it will put value 3
But I want it to put value 2, so that when find the max value, it actually
find all in the column , EXCLUDE those in the selection. How can I add those
checking in finding max value in a column excluding those in selection ?

I hope you got what I mean.

Thanks.

Regards.

I would like to sum up only the £ values in a row containing £ and units.
Ideally I would also sum £ values only within a specified date range ( I
achieved this already with SUMIF but am unable to combine the two conditions.

Ps. The data is not structured to allow the use of conditional sum wizard
Thanks

I am trying to use drop down menus to enter data in a row. This data will
co-incide with a row in a table and result in a drawing number at the end of
the row. I can do it with 1 drop down menu but not 2 using the lookup
wizard. Does any one know how to match 3 values in a row as opposed to 1 as
I have managed to do?

Say I have a series of values in a row. I need a formula that will return the relative position of the value in the row...and I will occasionaly insert a column at the front of the row of values, so the relative position may be different tomorrow.

Foe example, say I have the following values in Row 1, columns B-F: 32,42,54,67,89
Say in cell A2 is where I need my formula to return the relative position of the value for 54. Today the answer would be 3.
Then say tomorrow I insert a new value (29) in Row 1, Column B...now I have values in columns B-G...so the relative position for 54 is now 4, which will now become the value in cell A2

How do I find the position of the lowest value in a row in Excel?

Hi

I can only see some answers on how making new value in a collum
like:
Range("a2").End(xlDown).Offset(1, 0) = rew

but how do i make a new value in a row

Regards
Alvin


No luck finding an answer? You could always try Google.