Return oldest date with some dates excluded

**Stromming** · 05-30-2014, 05:57 AM

Hi all,
What I want to do is return the oldest date to another sheet, but I only want to return the oldest date if the backlog value is higher than 0. So example below has oldest date with 0 as backlog value, I don't want to return that date, but go to the oldest one with an actual backlog value.

So I have the following raw data in Sheet1

Please Login or Register  to view this content.

Now I want to put this raw data in Sheet 2
I use this formula to pull the data =SMALL(Sheet1!C3:C5,1)) but this returns the B4 value to me, which I don't want as A4 is 0.
Ive been thinking about somehow using VLOOKUP or IF to sort this out but I am stuck.

Hope someone here can put me on the right trackk.

**XOR LX** · 05-30-2014, 06:03 AM

Hi,

If you're using Excel 2010 or later (you don't list your version in your profile):

=AGGREGATE(15,6,1/(A2:A4>0)*B2:B4,1)

Otherwise, array formula**:

=MIN(IF(A2:A4>0,B2:B4))

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

**Stromming** · 05-30-2014, 06:20 AM

Works perfectly, not sure how it works though..

I understood that 15 is SMALL function and that 6 means ignore error values, but i am not sure what the last part does, I am using excel 2013

Now I have another issue related to this
I am doing this in groups so I am getting the oldest date returned several times, but sometimes there is ntohing to return so I get #NUM!, and when I try to get the oldest date in that group, it just gives me the error one... not sure what formula can be used to fix that,

im only using =SMALL(D3:D10,1) for the total

**XOR LX** · 05-30-2014, 06:32 AM

You're welcome.

When array-coerced (and AGGREGATE is such a function):

(A2:A4>0)

resolves to:

{TRUE;TRUE;FALSE}

When we take the reciprocal of this array, we have:

{1;1;#DIV/0!}

which, when multiplied by the corresponding array B2:B4, gives:

{41782;41780;#DIV/0!}

We then take the smallest excluding errors (and all values which did not have a column A value > 0 will be errors), as required.

Regards