formula friday Archives

Happy Friday peeps its time for another formula on a Friday. Here is a question I have been asked a few times now, and the answer is not quite what you think. How to find the minimum value in a data range, BUT we want to ignore the zero (0) values. You would automatically (perhaps :-) ) think that you could use the MIN (minimum) function, but this will only find the MINIMUM, so if we have zero in our data range that is what it will return. See my example below. The data range is in cells C4:C16, and we have used the formula =MIN(C4:C16) and this indeed returns zero which is the minimum value in the data set. So which formula could give us the result we want?. We can use the SMALL function as we can choose the smallest, the second smallest, the third smallest and so on in our data range with this function. We can tweak this formula to count if there are zeros in the data set, then instruct Excel to return the next smallest number after zero (if there are zeros). OK lets get building the formula. We can start off with the COUNTIF formula, the syntax being =COUNTIF(RANGE,CRITERIA) ..where range if our data range and criteria being zero as we are looking the count the number of cells that contian zero. The SMALL function, whose Syntax is SMALL(array,k) ....where array is the data range and k is the represents the 1st, 2nd, 3rd smallest, this is what the COUNTIF function is working out... Where the countif is counting the zeros in the range (+1) and is used to tell SMALL to return the k-th smallest value. The formula looks like this.... =SMALL(C4:C16,COUNTIF(C4:C16,0)+1) We can see correctly that Excel has returned 50 as being the smallest value in our data range ignoring the zero as the 50 is the second smallest value in the range. If you want to download the workbook with this example you can do so here.[ddownload id="8268"] If you want more awesome formula fun the sign up the the How To Excel At Excel Newsletter for 3 FREE tips every 15 days. Sign up and receive my FREE top 50 Excel Tips Ebook. Formula Friday- How To Pick A Winner Using Excel Formula Friday- Find The k-th Largest Value In Your Excel Data Set Using The LARGE Function Formula Friday- Use the LEN Formula To Highlight Issues With My Data Import Other Places you can find me.......