Help with row, count and small

QuestionEdit

Can you please explain how this formula works? I have it in anspreadsheet and need to understand.

This formula in in column B IF(ROW()>COUNT(A:A),"",SMALL(A:A,ROW()))

This formula is in column A =IF('Open'!A5=0,ROW('Open'!A5),"")

AnswerEdit

Rudy

IF(ROW()>COUNT(A:A),"",SMALL(A:A,ROW()))

this function appears to give you a sorted list of the values in column A from smallest to largest. It is designed to enter this equation in B1 and then drag fill it down column B for as far as you will ever expect values in column A

row() gives you the row number of the cell that contains the formula. In this formula it is used twice.

first Use: since the formula will be pre-entered to handle an unknown quantity of data in column A, we want to prevent the formula from displaying an error value in cells where we have "run out of numbers" in column A to display. So the if statement says if the row containing this formula is in a row that has a row number greater than the total quantity of numbers in column A, then we will display a Null string ("") to make the cell with the formula appear blank (and supress any error display such as #NUM)

Second Use: Row is used in the third argument to the IF function: Small(A:A,ROW()). Here row() is just used to display a sequential number starting with 1 (assumes the formula starts in row 1). Small will produce the nth smallest number in a range where nth is the second argument to the Small function. The first argument is the range containing the numbers. So in row 1, Small(A:A,row()) becomes Small(A:A,1) and that means return the smallest number in column A. Formula In row 2 it becomes Small(A:A,2) to return the 2nd smallest number; formula in row 3 it becomes Small(A:A,3) to return the 3rd smallest. And so forth.

The second formula =IF('Open'!A5=0,ROW('Open'!A5),"") just lists the rows in Column A of the Sheet Open that contain 0 or are blank. This particular formula looks at cell A5 in in sheet OPEN. If that cell is displaying zero or is empty, then the formula will display the row number of cell Open!A5 (which will be 5). If it A5 of open doesn't have a value of empty or 0, the formula will display a null string and appear as empty.

Again this formula can be drag filled down column A.

So now knowing what the the numbers in column A are, we can enrich the explanation of the formulas in column B to say they produced a sorted, contiguous list of the row numbers in in sheet Open in column A where the cells are either empty/blank or contain zero.

Hope that answers the question.