Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database question
Sample database:
Name Zone Agency Month Amount John Doe West Smith Jan 150,000 Bill Smith North Johnson Feb 175,000 Given a database like above with hundreds of entries, what array formula would one use to find the following info: 1. Which person from the North Zone had the highest value sale in the month of Jan? 2. Which person from the North Zone had the 2nd highest value sale in the month of Jan? How about the 3rd highest? If you can give me the direction to go I think I can figure out how to answer other similar questions. Thanks alot!!, Jim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database question
One way (array-entered):
=INDEX($A$2:$A$1000,MATCH(LARGE(($B$2:$B$1000="Nor th")*$E$2:$E$1000,1),($ B$2:$B$1000="North")*$E$2:$E$1000,0)) Change the 1 in the LARGE() function to 2, 3, etc. In article , "Jim" wrote: Sample database: Name Zone Agency Month Amount John Doe West Smith Jan 150,000 Bill Smith North Johnson Feb 175,000 Given a database like above with hundreds of entries, what array formula would one use to find the following info: 1. Which person from the North Zone had the highest value sale in the month of Jan? 2. Which person from the North Zone had the 2nd highest value sale in the month of Jan? How about the 3rd highest? If you can give me the direction to go I think I can figure out how to answer other similar questions. Thanks alot!!, Jim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database question
Thanks for the answer:
One way (array-entered): =INDEX($A$2:$A$1000,MATCH(LARGE(($B$2:$B$1000="Nor th")*$E$2:$E$1000,1),($ B$2:$B$1000="North")*$E$2:$E$1000,0)) Change the 1 in the LARGE() function to 2, 3, etc. But how do I limit to a second criteria: 2nd highest in the month of Jan (column D) Thanks "Jim" wrote in message ... Sample database: Name Zone Agency Month Amount John Doe West Smith Jan 150,000 Bill Smith North Johnson Feb 175,000 Given a database like above with hundreds of entries, what array formula would one use to find the following info: 1. Which person from the North Zone had the highest value sale in the month of Jan? 2. Which person from the North Zone had the 2nd highest value sale in the month of Jan? How about the 3rd highest? If you can give me the direction to go I think I can figure out how to answer other similar questions. Thanks alot!!, Jim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database question
Postman Jim Thanks for the answer but I didn't see how to limit to Jan or the month. Thanks *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database question
Reread my answer, starting with "Change..."
In article , "Jim" wrote: Thanks for the answer: One way (array-entered): =INDEX($A$2:$A$1000,MATCH(LARGE(($B$2:$B$1000="Nor th")*$E$2:$E$1000,1),($ B$2:$B$1000="North")*$E$2:$E$1000,0)) Change the 1 in the LARGE() function to 2, 3, etc. But how do I limit to a second criteria: 2nd highest in the month of Jan (column D) Thanks "Jim" wrote in message ... Sample database: Name Zone Agency Month Amount John Doe West Smith Jan 150,000 Bill Smith North Johnson Feb 175,000 Given a database like above with hundreds of entries, what array formula would one use to find the following info: 1. Which person from the North Zone had the highest value sale in the month of Jan? 2. Which person from the North Zone had the 2nd highest value sale in the month of Jan? How about the 3rd highest? If you can give me the direction to go I think I can figure out how to answer other similar questions. Thanks alot!!, Jim |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database question
Yes I understand the 1 for the first, 2 for the 2nd,etc. My question
where does the Month(Column D) come in. I need to know the 1st, etc. in Jan. Then I need to know the 1st, etc. in Feb, etc. Sorry I was unclear. Also can the ranges include blanks? Postman Jim *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Database Question? | Excel Discussion (Misc queries) | |||
HELP!! excel database question | Excel Discussion (Misc queries) | |||
Database Analysis Question | Excel Discussion (Misc queries) | |||
Member database question! | Excel Worksheet Functions | |||
Template & Database Question | Excel Worksheet Functions |