I'm tracking stats for stores performances in various departments. Each
criteria has been assigned a value and the Total row sums the total points
for each store. I would like another area/page to show the top store for
each month. I have entered a formula to have it show the max score for each
month but I would like it to show the column heading for the top store
instead, which is the store number. How do I do this?
--
Mary

Assume
Store number in A2:A10
Date in B2:B10
Score in C2:C10

=INDEX(A2:A10,MATCH(MAX(IF((MONTH(B2:B10)=2),C2:C1 0)),C2:C10,0))

ctrl+shift+enter, not just enter



"mary" wrote:

I'm tracking stats for stores performances in various departments. Each
criteria has been assigned a value and the Total row sums the total points
for each store. I would like another area/page to show the top store for
each month. I have entered a formula to have it show the max score for each
month but I would like it to show the column heading for the top store
instead, which is the store number. How do I do this?
--
Mary

There is no "Month" row, but when I added one, the formula did not return the
correct results (wrong store number matched to the highest score). Without
the date, Exel gives me a message telling me I don't have enough arguments
for the formula. I appreciate your quick response, but it's not working for
me.
--
Mary


"Teethless mama" wrote:

Assume
Store number in A2:A10
Date in B2:B10
Score in C2:C10

=INDEX(A2:A10,MATCH(MAX(IF((MONTH(B2:B10)=2),C2:C1 0)),C2:C10,0))

ctrl+shift+enter, not just enter



"mary" wrote:

I'm tracking stats for stores performances in various departments. Each
criteria has been assigned a value and the Total row sums the total points
for each store. I would like another area/page to show the top store for
each month. I have entered a formula to have it show the max score for each
month but I would like it to show the column heading for the top store
instead, which is the store number. How do I do this?
--
Mary

MONTH is a function which extracts from the date in B2:B10 the relevant
month. Example: if the value in B2 is October 10, 2007, =MONTH(B2) will
return October, or 10 depending on how the cell is formatted. The formula
provided will work if the columns are laid out per the assumptions. If these
assumptions do not pertain to your data, post back with information about how
your data are laid out and someone can adjust the formula provided.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"mary" wrote:

There is no "Month" row, but when I added one, the formula did not return the
correct results (wrong store number matched to the highest score). Without
the date, Exel gives me a message telling me I don't have enough arguments
for the formula. I appreciate your quick response, but it's not working for
me.
--
Mary


"Teethless mama" wrote:

Assume
Store number in A2:A10
Date in B2:B10
Score in C2:C10

=INDEX(A2:A10,MATCH(MAX(IF((MONTH(B2:B10)=2),C2:C1 0)),C2:C10,0))

ctrl+shift+enter, not just enter



"mary" wrote:

I'm tracking stats for stores performances in various departments. Each
criteria has been assigned a value and the Total row sums the total points
for each store. I would like another area/page to show the top store for
each month. I have entered a formula to have it show the max score for each
month but I would like it to show the column heading for the top store
instead, which is the store number. How do I do this?
--
Mary

Could you tell us what format to apply to a cell containing =MONTH(B2) to
get it to return October, in the situation you describe? The cell contains
the number 10, so if you format it as mmmm it will return January. What
format will return October, Dave?
--
David Biddulph

"Dave F" wrote in message
...
MONTH is a function which extracts from the date in B2:B10 the relevant
month. Example: if the value in B2 is October 10, 2007, =MONTH(B2) will
return October, or 10 depending on how the cell is formatted. The formula
provided will work if the columns are laid out per the assumptions. If
these
assumptions do not pertain to your data, post back with information about
how
your data are laid out and someone can adjust the formula provided.

"mary" wrote:

There is no "Month" row, but when I added one, the formula did not return
the
correct results (wrong store number matched to the highest score).
Without
the date, Exel gives me a message telling me I don't have enough
arguments
for the formula. I appreciate your quick response, but it's not working
for
me.

"Teethless mama" wrote:

Assume
Store number in A2:A10
Date in B2:B10
Score in C2:C10

=INDEX(A2:A10,MATCH(MAX(IF((MONTH(B2:B10)=2),C2:C1 0)),C2:C10,0))

ctrl+shift+enter, not just enter

"mary" wrote:

I'm tracking stats for stores performances in various departments.
Each
criteria has been assigned a value and the Total row sums the total
points
for each store. I would like another area/page to show the top store
for
each month. I have entered a formula to have it show the max score
for each
month but I would like it to show the column heading for the top
store
instead, which is the store number. How do I do this?
--
Mary

David: apparently my excel has a custom number format that I had forgotten
about, which I use when I use the MONTH function: [$-409]mmmm

This returns the month written out, as October, or whatever other month is
relevant.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"David Biddulph" wrote:

Could you tell us what format to apply to a cell containing =MONTH(B2) to
get it to return October, in the situation you describe? The cell contains
the number 10, so if you format it as mmmm it will return January. What
format will return October, Dave?
--
David Biddulph

"Dave F" wrote in message
...
MONTH is a function which extracts from the date in B2:B10 the relevant
month. Example: if the value in B2 is October 10, 2007, =MONTH(B2) will
return October, or 10 depending on how the cell is formatted. The formula
provided will work if the columns are laid out per the assumptions. If
these
assumptions do not pertain to your data, post back with information about
how
your data are laid out and someone can adjust the formula provided.

"mary" wrote:

There is no "Month" row, but when I added one, the formula did not return
the
correct results (wrong store number matched to the highest score).
Without
the date, Exel gives me a message telling me I don't have enough
arguments
for the formula. I appreciate your quick response, but it's not working
for
me.

"Teethless mama" wrote:

Assume
Store number in A2:A10
Date in B2:B10
Score in C2:C10

=INDEX(A2:A10,MATCH(MAX(IF((MONTH(B2:B10)=2),C2:C1 0)),C2:C10,0))

ctrl+shift+enter, not just enter

"mary" wrote:

I'm tracking stats for stores performances in various departments.
Each
criteria has been assigned a value and the Total row sums the total
points
for each store. I would like another area/page to show the top store
for
each month. I have entered a formula to have it show the max score
for each
month but I would like it to show the column heading for the top
store
instead, which is the store number. How do I do this?
--
Mary

My page looks somewhat like this:

3903 3904 3913 3915
whatever 0 1 1 0
whatever 1 1 1 0
whatever 0 0 1 0

Total 1 2 3 0

I can write the formula to return the max amount to me, but what I want is
for it to
tell me the heading, rather than the amount. Example: 3 is the max, I want
it to
tell me 3913(a store #) so I know which store had the max.

Mary

--
Mary


"Dave F" wrote:

MONTH is a function which extracts from the date in B2:B10 the relevant
month. Example: if the value in B2 is October 10, 2007, =MONTH(B2) will
return October, or 10 depending on how the cell is formatted. The formula
provided will work if the columns are laid out per the assumptions. If these
assumptions do not pertain to your data, post back with information about how
your data are laid out and someone can adjust the formula provided.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"mary" wrote:

There is no "Month" row, but when I added one, the formula did not return the
correct results (wrong store number matched to the highest score). Without
the date, Exel gives me a message telling me I don't have enough arguments
for the formula. I appreciate your quick response, but it's not working for
me.
--
Mary


"Teethless mama" wrote:

Assume
Store number in A2:A10
Date in B2:B10
Score in C2:C10

=INDEX(A2:A10,MATCH(MAX(IF((MONTH(B2:B10)=2),C2:C1 0)),C2:C10,0))

ctrl+shift+enter, not just enter



"mary" wrote:

I'm tracking stats for stores performances in various departments. Each
criteria has been assigned a value and the Total row sums the total points
for each store. I would like another area/page to show the top store for
each month. I have entered a formula to have it show the max score for each
month but I would like it to show the column heading for the top store
instead, which is the store number. How do I do this?
--
Mary

=index(b1:e1,match(max(b5:e5),b5:e5,0))

If the headers are in B1:E1 and the totals are in B5:E5.

mary wrote:

My page looks somewhat like this:

3903 3904 3913 3915
whatever 0 1 1 0
whatever 1 1 1 0
whatever 0 0 1 0

Total 1 2 3 0

I can write the formula to return the max amount to me, but what I want is
for it to
tell me the heading, rather than the amount. Example: 3 is the max, I want
it to
tell me 3913(a store #) so I know which store had the max.

Mary

--
Mary

"Dave F" wrote:

MONTH is a function which extracts from the date in B2:B10 the relevant
month. Example: if the value in B2 is October 10, 2007, =MONTH(B2) will
return October, or 10 depending on how the cell is formatted. The formula
provided will work if the columns are laid out per the assumptions. If these
assumptions do not pertain to your data, post back with information about how
your data are laid out and someone can adjust the formula provided.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"mary" wrote:

There is no "Month" row, but when I added one, the formula did not return the
correct results (wrong store number matched to the highest score). Without
the date, Exel gives me a message telling me I don't have enough arguments
for the formula. I appreciate your quick response, but it's not working for
me.
--
Mary


"Teethless mama" wrote:

Assume
Store number in A2:A10
Date in B2:B10
Score in C2:C10

=INDEX(A2:A10,MATCH(MAX(IF((MONTH(B2:B10)=2),C2:C1 0)),C2:C10,0))

ctrl+shift+enter, not just enter



"mary" wrote:

I'm tracking stats for stores performances in various departments. Each
criteria has been assigned a value and the Total row sums the total points
for each store. I would like another area/page to show the top store for
each month. I have entered a formula to have it show the max score for each
month but I would like it to show the column heading for the top store
instead, which is the store number. How do I do this?
--
Mary

--

Dave Peterson

Hi Mary

With your table in A1:E6

=INDEX(A1:E1,MATCH(MAX(A6:E6),A6:E6))

--
Regards

Roger Govier


"mary" wrote in message
...
My page looks somewhat like this:

3903 3904 3913 3915
whatever 0 1 1 0
whatever 1 1 1 0
whatever 0 0 1 0

Total 1 2 3 0

I can write the formula to return the max amount to me, but what I
want is
for it to
tell me the heading, rather than the amount. Example: 3 is the max,
I want
it to
tell me 3913(a store #) so I know which store had the max.

Mary

--
Mary


"Dave F" wrote:

MONTH is a function which extracts from the date in B2:B10 the
relevant
month. Example: if the value in B2 is October 10, 2007, =MONTH(B2)
will
return October, or 10 depending on how the cell is formatted. The
formula
provided will work if the columns are laid out per the assumptions.
If these
assumptions do not pertain to your data, post back with information
about how
your data are laid out and someone can adjust the formula provided.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"mary" wrote:

There is no "Month" row, but when I added one, the formula did not
return the
correct results (wrong store number matched to the highest score).
Without
the date, Exel gives me a message telling me I don't have enough
arguments
for the formula. I appreciate your quick response, but it's not
working for
me.
--
Mary


"Teethless mama" wrote:

Assume
Store number in A2:A10
Date in B2:B10
Score in C2:C10

=INDEX(A2:A10,MATCH(MAX(IF((MONTH(B2:B10)=2),C2:C1 0)),C2:C10,0))

ctrl+shift+enter, not just enter



"mary" wrote:

I'm tracking stats for stores performances in various
departments. Each
criteria has been assigned a value and the Total row sums the
total points
for each store. I would like another area/page to show the top
store for
each month. I have entered a formula to have it show the max
score for each
month but I would like it to show the column heading for the
top store
instead, which is the store number. How do I do this?
--
Mary