Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

Find MIN, MAX and AVERAGE values in an array based on criteria and excluding zeros

In the screeshot below, I want to find the minimum value for 'apples' but ignore zeros.  In this case, the result should be 4.

Also need the max and average values for the same criteria.

Attempted to use this array formula but it returns zero.

=MIN(IF(C1:C14<>0,A1:A14))*(A1:A14="apples")

Open in new window

screenshotBook1.xlsx
0
mcnuttlaw
Asked:
mcnuttlaw
  • 2
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Try

=MIN(IF((C1:C14<>0)*(A1:A14="apples"),A1:A14))
0
 
mcnuttlawAuthor Commented:
Still returns a zero (using the attached sample worksheet).
0
 
Saqib Husain, SyedEngineerCommented:
Sorry change it to

=MIN(IF((C1:C14<>0)*(A1:A14="apples"),C1:C14))
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now