Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I make Excel's MIN function ignore zeroes in a set?

Tags:

excel

min

In Excel, I have the following formula =(MIN(H69,H52,H35,H18)*(1/H18))*10 that is supposed to return the MIN of a range, and divide it by the current cell (*(1/H18) ), then multiply by 10.

I am having difficulty with adding a type of NULLIF statement. I want to be able to have (the possibility for) blank rows, and have the MIN function ignore zero/blank fields while selecting the next lowest value (all are between 1.0-0.1).

Is there a modifier i can apply to the MIN function to make it not compare zeroes in the MIN set? Is there a better function than MIN to use?

Here is the arrangement: alt text

  • Please remember to include the syntax for where the MIN's set goes
  • The reason for the H69,H52,H35,H18 using commas is that these are embedded, individual cells that are arranged for visual presentation as well. Using a range, or colon/semi-colon operators don't appear to work for this purpose (see pic).
  • This is to prevent the following situation: users will need to eliminate fields that are zeros from the form, theres 2 formula edits per entry, averaging 4 entries per use, so 8 possible errors per form use...
like image 265
mfg Avatar asked Jan 07 '11 17:01

mfg


People also ask

Does Min ignore blanks Excel?

If an argument is supplied to the function as a reference to a cell, or an array of cells, the MIN function will ignore blank cells and text or logical values contained within the supplied cell range.


2 Answers

You can use an array formula:

=MIN(IF(A1:A100>0,A1:A100))

You will need to hit ctrl+shift+enter to activate this formula.

like image 57
diagonalbatman Avatar answered Sep 27 '22 21:09

diagonalbatman


You could use

=SMALL(A1:A3,COUNTIF(A1:A3,0)+1)
like image 45
mvds Avatar answered Sep 27 '22 20:09

mvds