Big Trouble with what I thought would be a simple Excel Formula

[Flip@work]

I want a cell from one column to display the value of the top of another set
columns from the highest valued cell of the corresponding row. Is that
possible?

 

[Sean Timmons]

Makign sure I udnerstadn..

You are in, say, cell A4. You want to have a forula enter the largest value
from row 4 in other columns.

If that's it, do:

=MAX(B4:Z4)

 

[Flip@work]

Hi Sean,

Thanks for your help. This is the problem as best as I could explain it:

I have 24 rows (representing 24 test questions) with each row having 6 cells
of Data entered to each column B,C,D,E,F,G (representing Total, Unskilled,
Novice,Confident,Trained, Expert). Column A is used for names like "Question
1".

The first cell (2B, "Question 1") gives the sum total of cells C,D,E,F,G.

Each of the other cells (C,D,E,F,G) have a value entered (representing the
number of reponses for that question).

I want the cell for column H to display the top (Title Cell) of the column
whos cell has the highest value for that corresponding row of columns
C,D,E,F,G.

I wish I could post the actual excel sheet. I hope that helps.

Thanks,
Flip

 

[Sean Timmons]

Gotcha.

Put this in H2:

=INDEX($C$1:$G$20,1,MATCH(MAX($C2:$G2),$C2:$G2,0))

and copy down.

 

[Sean Timmons]

Oops.. Not $G$20, more like $G$25. 24 questions plus header.

 

[Flip@work]

In the imortal words of Homer Simpson ... "Doh!"

It looked great, Very close but it did not do what I needed. I believe it's
just a matter of me explaining it.

Column A and Row 1 are used for Title Cells for example Row two = Question
1, While column B would contain the total of C,D,E,F,G which are each the
reponse to that question.

I'd like a formula that can basicly tell me in column H which column in that
row has the greatest value but show only the title of that row. Which is what
I think you did but I copied and pasted it and it continues to point to the
same title cell.

Thanks again for all your help.

Flip

 

[Sean Timmons]

To verify, you copied the formula from before to H2 and received the header
name for one of the columns. Is it not the column that matched the greatest
value?

Or is it a situation where as you copy down the spreadsheet the column
doesn't change?

If the 2nd, make sure you copy the cell after you've entered the formula.
The formula in H3 should be:

=INDEX($C$1:$G$25,1,MATCH(MAX($C3:$G3),$C3:$G3,0))

If that's not the problem you're having, coudl you explain further?

 

[Barb Reinhardt]

I think I'd change it to this.

Assumptions:
Column Titles in Row 1 Columns C through G
Values for this example in Row 3

=INDEX($C$1:$G$1,1,MATCH(MAX($C3:$G3),$C3:$G3,0))

Then copy that formula down to the end of the section of different questions.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.