Csdrhrt

I am trying to have a cell return the value of the oldest date given that it matches the Store name (A5 referenced in the formula), as well as checks to see if column one is overdue. The date is stored in Column E on the Open POs sheet. Any advice?

AGGREGATE might be a good option. It can be a formula that will perform array like calculations without being an actual array. The reason I say might, is that only certain functions of AGGREGATE perform the array calculation...14 and 15 are two of them. And the other reason I say might be a good option, is that when performing array calculation, you want to avoid using full column references like F:F within the array. Doing so may cause a lot of excess calculations to be made and can bog down your system. Array calculation should reasonably be limited to the data you are dealing with.

Lets look at the AGGREGATE function and what its parts are

AGGREGATE(function #, What to include #, Calculation, parameter)

• Function 14 and 15 will sort the results from largest to smallest while the other will sort the other direction


• What to include #, has multiple options for ignoring hidden rows, or results that give errors. I tend to use 6 which ignores errors but includes the rest. You may need to adjust to suit your needs.


• Calculation is the area you want to put you the information your a looking for and your condition checks. Your condition checks are really boolean functions that return TRUE or FALSE. If we send those through a math operation, excel will convert TRUE=1 and FALSE=0. So if aggregate is set to ignore error, and you are only interested in information that meets your condition, divide your information by your conditions. False conditions will generate a div by 0 error and the aggregate function will then ignore that corresponding information.


• Parameter is an optional value for some functions but for 14 and 15 it tells AGGREGATE which position in the sorted results of values that you are interested in. placing a 1 here will return either the smallest or largest number depending on if you are using function 14 or 15.

SO for your formula we would do something like:

=AGGREGATE(14,6,'Open POs'!E2:E100/(('Open POs'!A2:A100="Overdue")*('Open POs'!F2:F100=$A5),1)

IF that gives you the earliest date then change 14 to 15. Adjust your ranges to suit your data instead of using full column references.

Caveat: Your dates need to be stored as excel date time serial and not as strings. You can test this by ISNUMBER(F4) where F4 is one of your date cells. If you get a result of false your dates will need to be converted.