Replicating Array Formula

[jaustin]

I recently stumbled across array formulas (specifically LINEST)which could be a big help if I can learn how to use it properly. While I can set-up a 2x5 cell region for theresults, I only need and slope and intercept. If I turn the Stat option to False this is exactly what I get and need(which are in cells 1 and 2 of row 1 of the result matrix). However, I want to apply this formula to atable in which the x values are in “DU2:EN2” and the y values are “DU3:EN3” butthe table goes from rows 3-83 (y values). Myquestion is how to apply this formula to each row (from 3 to 83) using VBA. I’ve tried various approaches but continue toget the remainder of the initial formula in rows 4-7. I would also like to make the solutionvariable so that y rows can be variable (except for row 3) as this table will have variable y rows.

I just purchased the MrExcel book on array formulas but haven't received it yet. Can't believe I hadn't known about this Excel feature before now.

Thanks in advance,

J. Austin

 

[iliace]

It depends on how you're trying to use the LINEST output, but I find it useful to leverage the INDEX function.

For example, if I type this formula:

=LINEST(INDEX($DU$3:$EN$83, 1, 0), $DU$2:$EN$2)

I am indexing the matrix of all the possible y-value, and specifying the row. In the above example, I would get the output for DU3:EN3. If I change the 1 to 2:

=LINEST(INDEX($DU$3:$EN$83, 2, 0), $DU$2:$EN$2)

that would give me the result for DU4:EN4 y-values. And so on all the way to the last row:

=LINEST(INDEX($DU$3:$EN$83, 81, 0), $DU$2:$EN$2)

Does that help?

 

[jaustin]

iliace,

Interesting approach. However, in attempting to use CSE (Control, Shift, Enter) array formula in which I need the first two cells in row one of the results array, I haven't been able to find a VBA approach to copy the formula to additional rows. A single cell approach for entering the LINEST formula gives me just the slope. When I attempt to use your formula with the INDEX function I get a #Ref error. I need the slope and intercept for each row of the table for an additional computation. Not sure I understand how to incorporate the INDEX function into an array formula. When I use the formula "=LINEST(DU3:EN3:DU2:EN2,TRUE, FALSE)" in Cell 1, Row 1 of a previously selected 2x1 results matrix (and enter the formula using CSE) I get exactly the slope and intercept for that row. All the additional approaches I've tried to in order to repeat the 2x1 formula/results matrix in additional rows, results in displaying rest of the 2x5 array formula below the single row of results that I need.

Again, thanks for the approach (and I've thought of other ways I can use it my current project), but not sure I can apply it for my use of the LINEST function as an array formula.

jaustin

 

[iliace]

If you simply copy down the 1x2 slope / intercept array, it works correctly. So where does the VBA come in? If you are trying to copy the formula, it will also work in VBA. If you are recreating the formula from scratch, use FormulaArray property of Range (instead of just Formula), to create a new formula.

Range("EO4:EP4").FormulaArray = "=LINEST(DU4:EN4, DU$2:EN$2)"

 

[jaustin]

If I highlight the 2x1 matrix that already has results in it and copy it to the next row below, I get the next two rows of the LINEST result (5x2 Matrix). What it shows I'm copying is the LINEST formula in braces. Haven't figured out how to copy. I keep getting "You can't change part of an array.

Thanks,

jaustin

 

[iliace]

When you say 2x1, is the 2 number of rows or columns? Here is an example I used, and then LINEST in EO:EP. I typed the LINEST in EO3:EP3, Ctrl+Shift+Enter, and drag down. Each now has its individual LINEST result. I cannot replicate the behavior you're describing.

																				Slope	Inter
1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	1	0
49	72	40	22	79	28	12	93	64	64	45	30	61	41	87	23	16	44	67	12	-0.73	55.10
35	99	48	42	32	36	13	27	72	92	27	26	57	26	66	98	14	29	49	45	-0.21	48.82
85	76	77	81	11	42	94	89	68	46	12	25	17	66	43	99	42	41	62	15	-1.77	73.16
55	45	64	90	65	94	33	37	28	13	84	97	58	44	95	42	20	50	48	67	-0.47	61.38
80	91	41	64	34	14	51	71	87	32	13	93	66	66	12	45	99	95	94	58	0.81	51.77
24	99	14	63	37	17	57	38	85	13	54	76	84	97	79	79	60	49	93	96	2.48	34.68
75	17	22	99	25	43	10	76	32	24	34	41	84	32	69	91	22	84	52	14	0.42	42.91
33	80	84	75	62	85	74	36	48	70	24	71	80	37	95	37	68	25	92	44	-0.57	67.03
41	39	40	19	38	93	64	74	36	76	72	34	52	92	57	90	53	68	70	10	0.85	47.03
83	62	29	45	80	28	65	27	75	64	90	26	69	49	53	38	89	99	13	44	-0.16	58.07
78	35	90	11	33	67	48	62	68	24	53	13	25	37	37	16	41	71	19	32	-1.43	57.97
36	32	27	26	17	20	34	17	21	68	88	34	44	40	58	29	25	52	62	87	1.92	20.71
48	17	74	68	58	55	93	36	42	50	58	65	38	52	84	93	60	42	66	26	0.21	54.06
54	86	35	17	75	54	37	82	47	41	67	16	55	93	55	13	26	78	64	21	-0.54	56.50
97	69	24	72	18	12	14	36	30	73	90	78	46	47	68	38	12	44	52	37	-0.56	53.72
65	10	59	22	27	83	99	74	59	32	68	24	46	44	25	93	50	19	51	65	0.05	50.19
93	21	99	22	45	46	33	39	88	10	32	11	80	74	35	82	89	54	30	32	-0.16	52.48
71	52	38	14	95	65	23	83	31	48	49	30	22	28	62	86	54	53	56	50	0.01	50.37

<colgroup><col span="20"><col><col></colgroup><tbody>
</tbody>

 

[jaustin]

iliace,

First, thanks for taking the time and effort to show that your solution works. Second, I've followed your steps and still get the remainder of the LINEST result array in the cells I've dragged over. BTW, when is said 2x1, I was meaning 2 columns wide by 1 row. Maybe I'm using a convention wrong. For the record, I'm using an Excel 365 Pro Plus subscription. I guess there may be a setting somewhere deep in Excel that is causing my behavior but I haven't really set anything different than the normal defaults. I'm going to test this file (pretty big and complex Workbook - about 150MB) on a second computer running the same version of Excel. I also have computers running standard Office 365 Home on which I will also try the same file. As a manual solution for my computing environment, I entered the LINEST array formula in the 2x1 array for all my rows. As the table of data will be updated with new data sets, hopefully, the formulas will auto-update. But, I will continue to pursue why my environment is giving different results. So far, I haven't seen any issues in Excel 2016 Pro Plus. In fact, I am completely blown away by its capabilities. Even though I'm running it on a 7th Gen i7 loaded HP, the speed at which it handles large 100,000 row Pivot Tables is astounding, at least for me. Even more surprising is that, just for fun, I loaded and executed this file on an early 4GB, i5 Surface Pro with about 108GB C:/ drive using Office 365 Home.

Again, thanks for taking the extra-ordinary efforts to pursue my issue. I've only signed up for MrExcel recently, but find it a very useful and helpful forum, particularly if you are an example of its users.

jaustin

 

[iliace]

I've only signed up for MrExcel recently, but find it a very useful and helpful forum, particularly if you are an example of its users.

Thanks, that's nice of you to say! There are indeed many users here who will go to great lengths to help users, particularly with challenges that are well-explained, interesting in nature, or happen to be their subject matter expertise.

I've followed your steps and still get the remainder of the LINEST result array in the cells I've dragged over. BTW, when is said 2x1, I was meaning 2 columns wide by 1 row.

Can you try this? After you create the formula in the first row (e.g. EO3:EP3), then drag it, and you get the undesired result - go to one of the subsequent rows, select Go To (F5), select Special, and in the dialog box, select Current Array. Does it select only the current row, or the rows above it as well?

(shortcut key for Go To Current Array is Ctrl+/)

 

[jaustin]

iliace,

Tried to post but did something wrong. If I try and use the last row of the dragged 2x5 array, it unhighlights it and Current Array is just that part of the array (2 columns on row 5). If I leave the full 2x5 array highlighted after dragging down, and select Current Array it highlights the original row 1, two columns and unhiglights the remainder of the array. If I go to a different clear area and repeat with Stats = TRUE, I only get the original 2x1 results unless I drag down and then all the stats show. Originally, I thought I got everything when using Stats = True. Must be some operator error somewhere.

jaustin

 

[iliace]

You should use Stats = False, or omit that argument altogether.