average based on criteria
Splendid!! Glad I could help.
"ramudt" wrote:
thanks a lot.
it is working fine.
regards
ramkumar, india
"Mike H" wrote:
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" wrote:
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" wrote:
I just noticed the header
average based on criteria
Which has nothing to do with the question in the body of your post!!
Mike
"Mike H" wrote:
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
"ramudt" wrote:
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
|