| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average based on criteria
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
average based on criteria
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 "ramudt" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
average based on criteria
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
average based on criteria
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
average based on criteria
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
average based on criteria
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
average based on criteria
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| need to average based on criteria | Excel Discussion (Misc queries) | |||
| average one column based on criteria from another | Excel Discussion (Misc queries) | |||
| Need to average division of two columns based on criteria | Excel Discussion (Misc queries) | |||
| Calculating an average based on 2 and 3 criteria | Excel Worksheet Functions | |||
| caluculate an average based on 2 criteria | Excel Discussion (Misc queries) | |||
Linear Mode
