Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

G'day there,

I have a sheet that contains 365 rows, representing dates from a referred Start Date (for the following year). There are about 30 columns each of which has an agency name. The cells in the sheet contain calculated agency populations for each date.

This is a reference sheet "DailyPopulation"

In the ReportSheet the user defines a Period Start Date and Period End Date

I'm trying to construct an array formula that displays the max population for each agency within the defined period, separately (of course) I also want the min.

Ideas? I'm already bald, no hair left to pull out.

Thanks

Jim

share|improve this question
    
Have you considered using a pivot table. With Excel 2010 you can use slicers to filter for your data –  wbeard52 Aug 8 '12 at 1:15
    
Hey Beard, Have thought of that. Unfortunately I'm stuck (corporately) with Excel 2003. It's a good approach though so I'll have a play. –  Jim Aug 8 '12 at 1:30
add comment

1 Answer

Where your Start Date is in B1, your End Date is in B2, all the dates are in A2:A366 on the DailyPopulation sheet, and the data for the agency in question is in B2:B365 on the DailyPopulation sheet, you can use the following array formula.

=MAX(IF($B$1<=DailyPopulation!$A$2:$A$366,IF($B$2>=DailyPopulation!$A$2:$A$366,DailyPopulation!B$2:B$366,""),""))

Remember you must enter the formula by pressing Ctrl+Shift+Enter. If you arrange your ReportSheet with each agency in a different column, you can fill this formula to the right.

To find the minimum, just replace MAX with MIN.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.