Return an array

WarfritLive

Board Regular
Joined
Sep 27, 2016
Messages
149
Hello.

If I have a cell with a formula that calculateds an array, is there a way for me to display that entire array so other cells can us it without having to remake the same calculations? I'm trying to keep this workbook light.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
Hi

You can display the values with 1 only formula applied to several cells. Just select the cells and apply the formula.
 

WarfritLive

Board Regular
Joined
Sep 27, 2016
Messages
149
My intention is excel only calculates the formula a single time, and each of the (thousands of) other cells need only reference the one rather than redo the entire thing

Edit: this is for EXCEL to do fewer calculations and run more smoothly. I'm not trying to save myself time entering
 
Last edited:

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
The way I said in my post the formula is calculated 1 only time, the formula returns an array that is displayed in several cells.

Can you post your formula?

This is an example of a formula that returns an array.

You want to display the 3 smallest values in a range in 3 cells.

- Select C2:E2
- in the formula bar paste: =SMALL(A1:A10,{1,2,3})
- confirm with CTRL-SHIFT-ENTER

You'll see 3 smallest values in the range A1:A10 displayed in C2:E2
You are just using 1 formula and the result array is displayed in the 3 cells.

Does this help?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,870
Members
414,106
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top