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.

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

1 Answer 1

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

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.