returning offset row from excel table
 

Friends,

I am working on an excel project at my office. I have a table set up
with 12 Months (Jan-Dec) running across the top, and usernames running
down the left. The data inside the table consists of each users total
spending within the given month.

Right side, (outside of the table) I want to start gathering
information. By using the MAX function "=MAX(B5:O5)" I was easily able
to calculate the max monthly amount spent in the year. However, I also
want to return the name of the month in which they spent this amount.
So, I want to return the column in which the amount was spent (just
like in the max function) but offset the returned row to be the row
containing the DATE information that runs across the top of the table.


I hope that makes some sense, any ideas?

I have messed around a bit with the COL and ROW functions, but it seems
that you cannot put in a function within these functions. For example,
it will correctly return =COL(B5) but I need it to return something
similar to =COL(MAX(B5:O5)) which it will not return (gives an error
and wont let me use the formula.

I have also looked at the OFFSET function, but it seems that the same
COL problem exists.

Thanks for the help,

Brendan


--
destrolennox
------------------------------------------------------------------------
destrolennox's Profile: http://www.excelforum.com/member.php...o&userid=36503
View this thread: http://www.excelforum.com/showthread...hreadid=562627

returning offset row from excel table
 

Assuming your MAX function formula is in cell P5, and you want the result of
the corresponding month to show in cell Q5:
=INDEX((B1:O1),MATCH(P5,B5:O5,0))
Although I am not sure why your range is 14 cells when there are 12 months.

-Simon

"destrolennox" wrote:


Friends,

I am working on an excel project at my office. I have a table set up
with 12 Months (Jan-Dec) running across the top, and usernames running
down the left. The data inside the table consists of each users total
spending within the given month.

Right side, (outside of the table) I want to start gathering
information. By using the MAX function "=MAX(B5:O5)" I was easily able
to calculate the max monthly amount spent in the year. However, I also
want to return the name of the month in which they spent this amount.
So, I want to return the column in which the amount was spent (just
like in the max function) but offset the returned row to be the row
containing the DATE information that runs across the top of the table.


I hope that makes some sense, any ideas?

I have messed around a bit with the COL and ROW functions, but it seems
that you cannot put in a function within these functions. For example,
it will correctly return =COL(B5) but I need it to return something
similar to =COL(MAX(B5:O5)) which it will not return (gives an error
and wont let me use the formula.

I have also looked at the OFFSET function, but it seems that the same
COL problem exists.

Thanks for the help,

Brendan


--
destrolennox
------------------------------------------------------------------------
destrolennox's Profile: http://www.excelforum.com/member.php...o&userid=36503
View this thread: http://www.excelforum.com/showthread...hreadid=562627

returning offset row from excel table
 

Hi!

If you have the 12 months listed starting in column B then the 12th month
should be in column M.

Assume the month names are in row 1, B1:M1.

=INDEX(B1:M1,MATCH(MAX(B5:M5),B5:M5,0))

Note: if there is more than one instance of MAX the formula will match the
first instance.

Biff

"destrolennox"
wrote in message
news:destrolennox.2b5rss_1153255207.5349@excelforu m-nospam.com...

Friends,

I am working on an excel project at my office. I have a table set up
with 12 Months (Jan-Dec) running across the top, and usernames running
down the left. The data inside the table consists of each users total
spending within the given month.

Right side, (outside of the table) I want to start gathering
information. By using the MAX function "=MAX(B5:O5)" I was easily able
to calculate the max monthly amount spent in the year. However, I also
want to return the name of the month in which they spent this amount.
So, I want to return the column in which the amount was spent (just
like in the max function) but offset the returned row to be the row
containing the DATE information that runs across the top of the table.


I hope that makes some sense, any ideas?

I have messed around a bit with the COL and ROW functions, but it seems
that you cannot put in a function within these functions. For example,
it will correctly return =COL(B5) but I need it to return something
similar to =COL(MAX(B5:O5)) which it will not return (gives an error
and wont let me use the formula.

I have also looked at the OFFSET function, but it seems that the same
COL problem exists.

Thanks for the help,

Brendan


--
destrolennox
------------------------------------------------------------------------
destrolennox's Profile:
http://www.excelforum.com/member.php...o&userid=36503
View this thread: http://www.excelforum.com/showthread...hreadid=562627

returning offset row from excel table
 

Thanks for the help guys! Your suggestions worked perfectly!

-Brendan


--
destrolennox
------------------------------------------------------------------------
destrolennox's Profile: http://www.excelforum.com/member.php...o&userid=36503
View this thread: http://www.excelforum.com/showthread...hreadid=562627

returning offset row from excel table
 

You're welcome!

Biff

"destrolennox"
wrote in message
news:destrolennox.2b5v1i_1153259413.0225@excelforu m-nospam.com...

Thanks for the help guys! Your suggestions worked perfectly!

-Brendan


--
destrolennox
------------------------------------------------------------------------
destrolennox's Profile:
http://www.excelforum.com/member.php...o&userid=36503
View this thread: http://www.excelforum.com/showthread...hreadid=562627