Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

The array formula I have will give me the smallest date from a Service table, based on the following criteria:

1) The value in the item column matches the item # in cell A1

2) The value in the amount column > 0

=MIN(IF((IF(Service[Item]=$A1,TRUE)*IF(Service[Amount]>0,TRUE))>0,Service[Date],"z"))

Adding "z" was the only way I could get it to exclude 0/FALSE, which would always claim itself as the smallest value. Is there a cleaner way to calculate this? It will be part of a more complex formula.

share|improve this question
up vote 4 down vote accepted

Well, you should be able to use the formula without the inner IFs:

=MIN(IF((Service[Item]=$A1)*(Service[Amount]>0),Service[Date],9^99))

(Service[Item]=$A1) If this is true, it automatically returns true. Same for the next statement. And when you multiply both, you get a series of 0s and 1s which excel treats as false and true respectively.

And using I'm not sure if using z is working for you. It's returning me #VALUE! apparently...

share|improve this answer
1  
Thank you - this works perfectly, plus it is cleaner and easier to read. I changed the 9^99 to "z" as it is part of a bigger formula and either needs to be 0 or the minimum date value. The "z" works because MIN excludes anything that isn't a number. – user2137354 Oct 2 '13 at 17:31
    
@nethy Okay, as long as it works for you! ^^ – Jerry Oct 2 '13 at 18:28
1  
In fact you don't need "z", either, you could simply leave that out, i.e. =MIN(IF((Service[Item]=$A1)*(Service[Amount]>0),Service[Date])) - with that version FALSE is returned for rows where criteria doesn't match and FALSE will be ignored in this context – barry houdini Oct 3 '13 at 13:49
    
TRUE! haha...get it... Thanks for the tip, the formula works great and no need for the "z". – user2137354 Oct 3 '13 at 15:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .