## Create numbers based on numerical ranges

The image above shows an array formula in cell B3 that calculates numbers based on the numerical ranges in cell range E3:F6.

Array formula in B3:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Explaining formula in cell B3

#### Step 1 - Create a sequence

The ROW function returns a row number based on a cell reference, if the cell reference has multiple rows then the row function returns an array of numbers.

ROW($1:$21)

returns

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21}

#### Step 2 - Check if number is in sequence

The COUNTIFS function checks if a number is larger or equal to the start value and smaller or equal to the end value. If both conditions are met the COUNTIFS function returns 1.

COUNTIFS($E$3:$E$6, "<="&ROW($1:$21),$F$3:$F$6, ">="&ROW($1:$21))

becomes

COUNTIFS($E$3:$E$6, "<="&{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21},$F$3:$F$6, ">="&{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21})

becomes

COUNTIFS({1;5;11;19}, "<="&{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21},{3;6;13;21}, ">="&{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21})

and returns

{1; 1; 1; 0; 1; 1; 0; 0; 0; 0; 1; 1; 1; 0; 0; 0; 0; 0; 1; 1; 1}

Tip! Use an Excel defined Table to create dynamic cell references that you don't have to adjust if more ranges are added or deleted.

#### Step 3 - Return number if in range

The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).

IF(COUNTIFS($E$3:$E$6, "<="&ROW($1:$21),$F$3:$F$6, ">="&ROW($1:$21)), ROW($1:$21))

becomes

IF({1; 1; 1; 0; 1; 1; 0; 0; 0; 0; 1; 1; 1; 0; 0; 0; 0; 0; 1; 1; 1}, ROW($1:$21))

becomes

IF({1; 1; 1; 0; 1; 1; 0; 0; 0; 0; 1; 1; 1; 0; 0; 0; 0; 0; 1; 1; 1}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21})

and returns

{1; 2; 3; FALSE; 5; 6; FALSE; FALSE; FALSE; FALSE; 11; 12; 13; FALSE; FALSE; FALSE; FALSE; FALSE; 19; 20; 21}.

#### Step 4 - Extract k-th smallest number

The ROWS function keeps track of the numbers based on an expanding cell reference. It will expand as the formula is copied to the cells below.

SMALL(IF(COUNTIFS($E$3:$E$6, "<="&ROW($1:$21),$F$3:$F$6, ">="&ROW($1:$21)), ROW($1:$21)), ROWS($A$1:A1))

becomes

SMALL({1; 2; 3; FALSE; 5; 6; FALSE; FALSE; FALSE; FALSE; 11; 12; 13; FALSE; FALSE; FALSE; FALSE; FALSE; 19; 20; 21}, ROWS($A$1:A1))

becomes

SMALL({1; 2; 3; FALSE; 5; 6; FALSE; FALSE; FALSE; FALSE; 11; 12; 13; FALSE; FALSE; FALSE; FALSE; FALSE; 19; 20; 21}, 1)

and returns 1 in cell B3.

The array formula in cell B3 lists numbers not in ranges specified in cell range E3:F6.

Array formula in B3:

### Download Excel file

Enter your email to receive the workbook.### One Response to “Create numbers based on numerical ranges”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Hello,

Thanks for this helpful information on excel formulas.

I need help with counting the number of cells that fall outside of two numbers, so I need to get the count of numbers that are less than -0.1 and greater than 0.1. I have been searching online for a long time, but everyone talks about counting the values that fall with in two numbers, but no discussion on how to count the numbers that are outside two numbers. I have tried COUNTIFS, but it won't work, it will only work to count numbers with in two numbers.

Any help is appreciated. Thank you!