Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Retuning a column number for a specific value
I'm trying to figure out how to have a formula return the column number from
a maximum value in a row. I've tried using =column(max(a1:c1)) to return the column number but it errors out. Can anyone offer a suggestion? Thanks |
#2
|
|||
|
|||
try
=sumproduct(--(range =max(range)),Column(range) note if there can be several equal maxs this won't work. in this case try =match(max(range),range,0)+(column number at start of range)-1 "slot guy" wrote: I'm trying to figure out how to have a formula return the column number from a maximum value in a row. I've tried using =column(max(a1:c1)) to return the column number but it errors out. Can anyone offer a suggestion? Thanks |
#3
|
|||
|
|||
the "--(" changes the logical true false to a numerical 1 0.
if you had column(C3) the response would be 3 since column C is the 3rd column in the formula sumproduct(--(A1:B1=max(A1:B1),Column(A1:B1)) would be similar to (A1=max(A1:B1)*Column(A1) +(B1=max(A1:B1))*column(B1 orif B1 were greater than A1 (0)*(1)+(1)*(2) = 2 "slot guy" wrote: bj, Thank you for the formula. I uesed the first one since I'm looking for unique date with no repeats. Can you please tell me what the "--" does in the function? Also, I don't understand how the column number is actualy returned using the column function. The function description just states that "column" returns the column number of a reference. It doesn't ask for any criteria to use to pick from a reference. Can you explain? "bj" wrote: try =sumproduct(--(range =max(range)),Column(range) note if there can be several equal maxs this won't work. in this case try =match(max(range),range,0)+(column number at start of range)-1 "slot guy" wrote: I'm trying to figure out how to have a formula return the column number from a maximum value in a row. I've tried using =column(max(a1:c1)) to return the column number but it errors out. Can anyone offer a suggestion? Thanks |
#4
|
|||
|
|||
bj,
Thank you for the formula. I uesed the first one since I'm looking for unique date with no repeats. Can you please tell me what the "--" does in the function? Also, I don't understand how the column number is actualy returned using the column function. The function description just states that "column" returns the column number of a reference. It doesn't ask for any criteria to use to pick from a reference. Can you explain? "bj" wrote: try =sumproduct(--(range =max(range)),Column(range) note if there can be several equal maxs this won't work. in this case try =match(max(range),range,0)+(column number at start of range)-1 "slot guy" wrote: I'm trying to figure out how to have a formula return the column number from a maximum value in a row. I've tried using =column(max(a1:c1)) to return the column number but it errors out. Can anyone offer a suggestion? Thanks |
#5
|
|||
|
|||
bj wrote...
.... note if there can be several equal maxs this won't work. in this case try =match(max(range),range,0)+(column number at start of range)-1 .... Or =COLUMN(INDEX(range,MATCH(MAX(range),range,0))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running total w/2 columns - Excel | Excel Worksheet Functions | |||
Get Column Letter from Column Number | Excel Worksheet Functions | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
Count number of occurences in 1 column only if something in anothe | Excel Worksheet Functions | |||
how do I find an average number of specific words in a column | New Users to Excel |