# Multiply numbers in each row by entire cell range

This article demonstrates a recursive LAMBDA function and a User Defined Function (UDF) that multiplies numbers in each row with the remaining rows in a cell range.

The image above demonstrates the UDF in cell range B5:D8, it takes the first row and multiplies the numbers with all rows in the same cell range. It then continues to the second row and multiplies that row with all rows in the cell range.

Hello Oscar,I have an Excel dataset consisting of 500 rows by 7 columns. I need to generate additional data points from this dataset.I want to multiply (or other function) each row by all 500 rows, creating 250,000 new rows of data.Each cell needs to function as a constant that is multiplied by all the other cells in the same column (which are not acting as constants).How do I do this efficiently? Thanks in advance!

### What's on this page

## 1. Multiply cells - recursive LAMBDA function

The image above shows the result of a recursive LAMBDA formula in cell B6 that multiplies each row by all rows in the given cell range, read the question here.

A cell range containing 2 rows returns 4 rows. A cell range with 3 rows returns 9 rows, and so on.

Excel 365 formula in cell B6:

Recursive LAMBDA formula:

### 1.1 How to create a named range

- Go to the tab "Formulas" on the ribbon.
- Press the left mouse button on "Name manager". A dialog box appears.

- Press the left mouse button on the "New..." button, see the image above.

- Name the formula "Multiply".
- Copy/Paste the formula to the "Referes to:" field.

- Press the left mouse button on the "OK" button.

### 1.2 Explaining recursive lambda formula

LAMBDA(rng,n,result,IF(n=(ROWS(rng)+1),DROP(result,1),Multiply(rng,n+1,VSTACK(result,rng*INDEX(rng,n,0)))))

#### Step 1 - Get row by row from cell range rng

The INDEX function returns a value or reference from a cell range or array, you specify which value based on a row and column number.

Function syntax: INDEX(array, [row_num], [column_num])

INDEX(rng,n,0)

becomes

INDEX({1,2,3;4,5,6},1,0)

and returns

{1,2,3} for the first row.

#### Step 2 - Multiply rng by the row

The asterisk lets you multiply numbers in an Excel formula.

rng*INDEX(rng,n,0)

becomes

{1,2,3;4,5,6}*{1,2,3}

equals

{1,4,9;4,10,18}.

The next iteration becomes

{1,2,3;4,5,6}*{4,5,6}

and returns

{4,10,18;16,25,36}

#### Step 3 - Add calculation to result

The VSTACK function combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)

Function syntax: VSTACK(array1,[array2],...)

VSTACK(result,rng*INDEX(rng,n,0))

becomes

VSTACK("",{1,4,9;4,10,18})

and returns

{"",#N/A,#N/A;1,4,9;4,10,18}.

The next iteration becomes

VSTACK({"",#N/A,#N/A;1,4,9;4,10,18},{4,10,18;16,25,36})

and returns

{"",#N/A,#N/A;1,4,9;4,10,18;4,10,18;16,25,36}

#### Step 4 - Count rows in range rng

The ROWS function calculate the number of rows in a cell range.

Function syntax: ROWS(array)

ROWS(rng)+1

returns 3.

#### Step 5 - Delete first row in array

The DROP function removes a given number of rows or columns from a 2D cell range or array.

Function syntax: DROP(array, rows, [columns])

DROP(result,1)

becomes

DROP({"",#N/A,#N/A;1,4,9;4,10,18;4,10,18;16,25,36},1)

and returns

{1,4,9;4,10,18;4,10,18;16,25,36}

#### Step 6 - Return the result except the first row if n = rows in range rng

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

Function syntax: IF(logical_test, [value_if_true], [value_if_false])

IF(n=(ROWS(rng)+1),DROP(result,1),Multiply(rng,n+1,VSTACK(result,rng*INDEX(rng,n,0))))

#### Step 7 - Build LAMBDA function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, â€¦,] calculation)

LAMBDA(rng,n,result,IF(n=(ROWS(rng)+1),DROP(result,1),Multiply(rng,n+1,VSTACK(result,rng*INDEX(rng,n,0)))))

### 1.3 Get Excel *.xlsx file

## 2. MultiplyCells - User Defined Function

Below is a User Defined function I made, it multiplies each row by all other rows in the specified range.

The picture above shows you the UDF using values in cell range B2:B3 and returns the calculated values to cell range B5:D8.

This is a UDF (custom function), you need to copy code to the code module before you can use it.

### UDF syntax

*range*)

### Arguments

There is only one argument in this UDF.

*range* - Cell range

## 2.2. VBA Code

'Name User Defined Function and dimension parameters Function MultiplyCells(rng As Range) 'Dimension variables and declare data types Dim rng1 As Variant Dim tbl() As Variant Dim rr As Single, r As Single, c As Single, tr As Single 'Save values in range object rng to array variable rng1 rng1 = rng.Value 'Redimension array variable tbl based on rows and columns in range object rng ReDim tbl(1 To rng.Cells.Rows.CountLarge ^ 2, 1 To rng.Cells.Columns.CountLarge) tr = 1 'For ... Next statements For rr = LBound(rng1, 1) To UBound(rng1, 1) For r = LBound(rng1, 1) To UBound(rng1, 1) For c = LBound(rng1, 2) To UBound(rng1, 2) 'Multiply values and save the product to array variable tbl tbl(tr, c) = rng1(rr, c) * rng1(r, c) Next c 'Add 1 to variable tr and save to variable tr tr = tr + 1 Next r Next rr 'Return numbers in array variable tbl to worksheet MultiplyCells = tbl End Function

### 2.3. How to enter the UDF as an array formula

Enter this UDF as an array formula, if the range has 2 rows and 3 columns enter the UDF in a cell range with 4 (2*2) rows and 3 columns.

Here are the steps to enter this UDF as an array formula:

- Select cell range B5:D8.
- Type the UDF name and argument:
=MultiplyCells(B2:D3)
- Press and hold CTRL + SHIFT simultaneously.
- Press Enter once.
- Release all keys.

If you did it right the formula now has a curly bracket before and after. Like this {=MultiplyCells(B2:D3)}. Don't enter these yourself. If you are unsure, watchÂ the formula bar carefullyÂ in theÂ animated picture above.

### 2.4. Where to put the code?

- Press Alt+ F11 to open the Visual Basic Editor (VBE).
- Press with mouse on "Insert" on the top menu, see the image above.
- Press with mouse on "Module" to insert a module to your workbook.
- Copy VBA code above.
- Paste to code window, see the image above.
- Exit VBE and return to Excel.

Copy the following code to a module. How to insert a module to a workbook.

### 2.5. Explaining VBA code

The code explained here is located in the code module. You can find the code module in the VB Editor, press Alt+F11 to open the VB Editor. Press with left mouse button on "Module" found in project explorer.

**Function name and arguments**

A User Defined Function always starts with "Function" and then a name. This UDF has only one argument. Variable rng is a range object, read more about Defining data types.

Function MultiplyCells(rng As Range)

**Declaring variables**

tbl() is an array and a variant variable, tbl has two parentheses meaning it is an array. rng1 Â is also a variant. Â rr, r, c, and tr are Single variables. Read more about Defining data types.

Dim rng1 As Variant Dim tbl() As Variant Dim rr As Single, r As Single, c As Single, tr As Single

**SaveÂ values from rng (range object)Â to rng1 (variant)**

This speeds up the function considerably if you are working with large cell ranges. Excel copiesÂ all the values from the sheet and puts them in memory (array).

rng1 = rng.Value

**Build array**

ReDim dimensions the tbl array variant, it has the number of rows of the range argument with the power of 2. The values in the array are numbered from 1 to n. There are as many columns as in the range argument.

ReDimtbl(1 To rng.Cells.Rows.CountLarge ^ 2, 1 Torng.Cells.Columns.CountLarge)

**Use variables r and c to save values in array**

Variable tr keeps track of where to save the next column values in tbl.

tr = 1

**For ... Next statement**

Repeats a group of statements a specified number of times, here we want to multiply each row by all rows.

For rr = LBound(rng1, 1) To UBound(rng1, 1) For r = LBound(rng1, 1) To UBound(rng1, 1) For c = LBound(rng1, 2) To UBound(rng1, 2)

**Save value to tbl array**

The variables tr, c, rr, and r help us keep track of which values to use and where to save.

tbl(tr, c) = rng1(rr, c) * rng1(r, c)

**Add 1 to variable tr**

tr = tr + 1

**Return tbl values to function**

MultiplyCells = tbl

**End udf**

A function procedure ends withÂ thisÂ statement.

End Function

### Macro category

Table of Contents Excel monthly calendar - VBA Â Calendar Drop down lists Headers Calculating dates (formula) Conditional formatting Today Dates […]

Table of contents Save invoice data - VBA Invoice template with dependent drop down lists Select and view invoice - […]

This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]

### Excel categories

### 2 Responses to “Multiply numbers in each row by entire cell range”

### 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

If I understand the question correctly, this function would work:

First you would put your original 500 rows in a table somewhere ("Table1"), and then enter this function as an array (ctrl-shift-enter) starting in cell A1, with seven columns and 250,000 rows. It can be adapted with subtraction from row() and column() if you wish to put your data in a location other than cell A1.

-Alex

Alex,

Great comment!

It works fine, why didn't I think of this.

Your formula is small and genius.

Thank you for commenting.