Solved

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

Posted on 2012-03-31
3
295 Views
Last Modified: 2012-03-31
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
Comment
Question by:mcnuttlaw
  • 2
3 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37792261
Try

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

Author Comment

by:mcnuttlaw
ID: 37792263
Still returns a zero (using the attached sample worksheet).
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 37792266
Sorry change it to

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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now