Return Maximum value for Specific Month(s)
 

Hi All,

I have a dynamic named range called "Data" that spans 10 columns and many
rows.

I would like to have the maximum value for the month of Jan (January - month
will vary) returned for column number 6. The month is a full date eg:
01/01/2006 using a custom short format "mmm" = Jan.

Column 2 = Full Date formatted as month "mmm"
Column 6 = Numeric Values - max value to be returned
Column 7 = Numeric Values - max value to be returned
Column 8 = Numeric Values - max value to be returned

Cheers,
Sam

--
Message posted via http://www.officekb.com

Return Maximum value for Specific Month(s)
 

=MAX(IF(MONTH(A2:A100)=1,B2:B100))

ctrl+shift+enter (not just enter)


"Sam via OfficeKB.com" wrote:

Hi All,

I have a dynamic named range called "Data" that spans 10 columns and many
rows.

I would like to have the maximum value for the month of Jan (January - month
will vary) returned for column number 6. The month is a full date eg:
01/01/2006 using a custom short format "mmm" = Jan.

Column 2 = Full Date formatted as month "mmm"
Column 6 = Numeric Values - max value to be returned
Column 7 = Numeric Values - max value to be returned
Column 8 = Numeric Values - max value to be returned

Cheers,
Sam

--
Message posted via http://www.officekb.com

Return Maximum value for Specific Month(s)
 

Are there any empty cells in your date column? Empty cells will evaluate as
month 1 (Jan).

Array entered:

=MAX(IF(MONTH(INDEX(Data,,2))=1,INDEX(Data,,COLUMN S($A:F))))

Copy across a total of 3 cells.

For other months replace the 1 with the appropriate month number or use a
cell to hold that number and then refer to that cell.

Biff

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:6a55d9373633a@uwe...
Hi All,

I have a dynamic named range called "Data" that spans 10 columns and many
rows.

I would like to have the maximum value for the month of Jan (January -
month
will vary) returned for column number 6. The month is a full date eg:
01/01/2006 using a custom short format "mmm" = Jan.

Column 2 = Full Date formatted as month "mmm"
Column 6 = Numeric Values - max value to be returned
Column 7 = Numeric Values - max value to be returned
Column 8 = Numeric Values - max value to be returned

Cheers,
Sam

--
Message posted via http://www.officekb.com

Return Maximum value for Specific Month(s)
 

Hi Teethless mama,

Thank you - very much appreciated.

Cheers,
Sam

Teethless mama wrote:
=MAX(IF(MONTH(A2:A100)=1,B2:B100))

ctrl+shift+enter (not just enter)

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200612/1

Return Maximum value for Specific Month(s)
 

Hi Biff,

Thank you very much. Works Great!

Cheers,
Sam

T. Valko wrote:
Are there any empty cells in your date column? Empty cells will evaluate as
month 1 (Jan).
Array entered:

=MAX(IF(MONTH(INDEX(Data,,2))=1,INDEX(Data,,COLUM NS($A:F))))
Copy across a total of 3 cells.
For other months replace the 1 with the appropriate month number or use a
cell to hold that number and then refer to that cell.

Biff

--
Message posted via http://www.officekb.com

Return Maximum value for Specific Month(s)
 

You're welcome. Thanks for the feedback!

Biff

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:6a5cfa7c3d1a6@uwe...
Hi Biff,

Thank you very much. Works Great!

Cheers,
Sam

T. Valko wrote:
Are there any empty cells in your date column? Empty cells will evaluate
as
month 1 (Jan).
Array entered:

=MAX(IF(MONTH(INDEX(Data,,2))=1,INDEX(Data,,COLU MNS($A:F))))
Copy across a total of 3 cells.
For other months replace the 1 with the appropriate month number or use a
cell to hold that number and then refer to that cell.

Biff

--
Message posted via http://www.officekb.com