average based on criteria

[ramudt]

i am having the values as follows

PRODUCT MFGDATE
====== =======
AAAA 01-SEP-08
BBBB 02-SEP-08
CCCC 03-SEP-08
AAAA 04-SEP-08

if i enter the AAAA, i need the max date 04-SEP-08
if i enter in a cell BBBB, i need the max date 02-SEP-08

how to do this?
any one can help me?

Regards
Ramkumar

 

[Ashish Mathur]

Hi,

Array enter (Ctrl+Shift+Enter) the following formula

MAX(IF(($A$1:$A$4=A6),$B$1:$B$4)). Please format the cell as date.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Mike H]

Hi,

Try this
=MAX(IF(A1:A20="aaa",B1:B20,FALSE))

This is an array so commit with CTRL+Shift+Enter not just enter. If you do
it correctly then Exce; will put curly brackets around the formula{}. You
can't type these yopurself.


In practice I'd use a cell reference for the aaa bit
=MAX(IF(A1:A20=C1,B1:B20,FALSE))

Mike

 

[Mike H]

I just noticed the header

RE: average based on criteria

Which has nothing to do with the question in the body of your post!!

Mike

 

[ramudt]

sorry

it is not working

the data values are as follows

A B C D
= = = =
AAA 01-Sep-08 AAA 5-Sep-08
BBB 02-Sep-08 BBB 5-Sep-08
CCC 03-Sep-08
AAA 04-Sep-08
AAA 05-Sep-08
BBB 03-Sep-08

here d column has the formula as follows

=MAX(IF(A$1:A$10=C3,B$1:B$10,FALSE))

but all the values are showing max of column D.

i need max of AAA or max of BBB

how to do this

output should be as follows


A B C D
= = = =
AAA 01-Sep-08 AAA 5-Sep-08
BBB 02-Sep-08 BBB 3-Sep-08
CCC 03-Sep-08
AAA 04-Sep-08
AAA 05-Sep-08
BBB 03-Sep-08


here BBB is 3 sep

Regards
Ramkumar

 

[Mike H]

Hi,

There's nothing wrong with the formula, did you enter the formula correctly.
It's an ARRAY.

To enter an array copy the formula into the formula bar then..VERY important.
Press and hold down CTRL+Shift and then tap the Enter key.

If you do it correctly Excel will put curly brackets around the formula {}
You can't type these yourself.

Mike

 

[ramudt]

thanks a lot.
it is working fine.

regards
ramkumar, india

 

[Mike H]

Splendid!! Glad I could help.

thanks a lot.
it is working fine.

regards
ramkumar, india