How to Ignore Blank Cells in a Named Range in Excel – 5 Methods

Formula Breakdown:

•   =IF(Sell=””,””,Buy/Sell): checks whether the value in the Sell named range is blank. If the value is blank, the IF function ignores that cell and calculates Buy/Sell. It returns the values in Buy – Sell Ratio.

Formula Breakdown:

• ISBLANK(Sell): ISBLANK checks whether the value in the Sell named range is blank. If the value is blank, the IF function ignores that cell and calculates Buy/Sell. It returns the values in the Buy – Sell Ratio.

Formula Breakdown:

• ISNUMBER(Sell): ISNUMBER checks whether the value in the Sell named range is a numeric value. If the value is numeric, the ISNUMBER function calculates Buy/Sell  or ignores that cell. The IF function returns the values in  Buy – Sell Ratio.

Formula Breakdown:

• ISNUMBER($E$5:$E$13): returns TRUE for numbers, FALSE otherwise. The output will be TRUE for the non blank cells and FALSE for the blank cells.

Output: {TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}

• ROW($A$1:$A$9):  returns the row numbers.

Output: {1; 2; 3; 4; 5; 6; 7; 8; 9}

• IF(ISNUMBER($E$5:$E$13),ROW($A$1:$A$9),””) is simplified as

IF({TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE},{1; 2; 3; 4; 5; 6; 7; 8; 9},””). returns the row numbers for TRUE and blank otherwise.

Output: {1;2; “” ; 4; 5; “”; 7; “”; 9}

• ROW(A1): returns the row number of the cell in the argument.

Output: 1

• SMALL(IF(ISNUMBER($E$5:$E$13),ROW($A$1:$A$9),””),ROW(A1))) is simplified as

SMALL({1;2; “” ; 4; 5; “”; 7; “”; 9},1) and returns the 1st smallest value in the range

Output: 1

• INDEX(Sell,SMALL(IF(ISNUMBER($E$5:$E$13),ROW($A$1:$A$9),””),ROW(A1))): 

INDEX(Sell,1) returns the 1st non blank value in the range.

Output: 45860

• IFERROR(INDEX(Sell,SMALL(IF(ISNUMBER($E$5:$E$13),ROW($A$1:$A$9),””),ROW(A1))),””) now resembles

IFERROR(45860, “”) returns 45860 as the final output, blank if any error happens

Output: 45860