Using Linest in VBA

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Click the top left button to select your file.
Click the Upload button on the right.
Close the attachments window and click Submit Reply.

Originally Posted by twa14

I can't find this "browse" button. There is an "attachments" button which causes my screen to refresh but nothing else! what am I missing?

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Thanks for letting us know. Please share the solution here for the benefit of all.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Thanks for the clarification.

Would you remind us of the Q.

So far I understand the requirement is to abolish the sheet altogether and generate the Q&R column data automatically.

The process would be to create a VBA array by looping through x iterations and using the formula you currently have in the sheet to populate the array in VBA. You mention that column d increase by 5mm so that could be used in the loop counter but that's not the only value that changes. Are all the other precedents to Q&R ultimately dependent solely on column D. I've not checked them all.

Does the above also apply to the other nine pairs of columns AG:AH etc....?

What's the eventual output. Is that 20 columns of data (10 pairs of data)?

How are the Cway width for the 10 pairs of coordinates arrived at. Are they able to be calculated in some way or are must they be preset as constants which will be input by the user before runtime?

I posted a reply on 31/12/18 but it seems to have disappeared (or is it in moderation)?

My post was to clarify the C\way width column. These widths are offsets from the master string on the radial cross section. They are fixed widths and represent locations which define the shape of the cross section. The coordinates calculated for these points can be used in constructing the shuttering for the bridge deck. Going back to my first post, the Linest command uses data on the sheet itself (the orange columns). This data is generated on the sheet. I do not see a way within VBA to have this data generated so that Linest can use the generated data, also within VBA. My reference to the HP programmable calculator Σ+ button was to show an example of how Linest might possibly work.
It would be easy to generate the data with VBA if Linest would absorb the data line by line. I don't know if this is possible.

FYI - There are no posts in moderation and there is no post by you listed at all for yesterday - perhaps you composed the post, but left the page before saving it?

Your spreadsheet is pretty large with a lot happening in it, and I feel like there is a lot of "extra" stuff happening here that is preventing us from focusing in on your exact question. I think a simpler spreadsheet that removes some of the distractions related to the clothoid and focuses on the actual programming question might help. For now, I am ignoring your sample spreadsheet.

I do not see a way within VBA to have this data generated so that Linest can use the generated data, also within VBA.

I do not understand what difficulty you are having here. The inputs to the LINEST() function when called from VBA should be simple VBA arrays. You have not provided any sample VBA code showing how you are trying to generate these arrays, so it is difficult for us to know what is going wrong. Steps I would expect:

1) Read x and y arrays from spreadsheet and store in VBA arrays (if I assume the data are in columns, I would expect two vertical nx1 VBA arrays).
2) (My instinct thinks that this might be the problem) xvalues^{1,2,3} is perfectly good spreadsheet syntax, but VBA has no idea what this means. You need a loop that will translate this syntax into something VBA can use to generate the full known_x array.
3) Call Linest application.linest(yvalues,xvalues,true,false) and store results in a new VBA array.
4) error checking and whatever needs to happen after performing the regression.

Untested pseudocode

Please Login or Register  to view this content.

My guess is that step 2 is where the problem lies, in that you have previously assumed that you could use the same Excel syntax in VBA. The key is to get VBA appropriate syntax for generating the input arrays.

Does that help?

Hi and thanks for your input. I am probably trying to do something that is not possible. Each row of the sample spreadsheet generates coordinates firstly along the main axis or master string and thereafter at right angles to this master string ±90 degrees left and right to create offset strings at fixed distances from the master string (orange columns) . Linest then fits third order polynomials to the XY data in the orange columns. In my first post i referred to the Σ+ key on HP programmable calculators which take keyed in data and accumulates this data in 6 inbuilt registers until the last line of data is entered. The regression calculation can then be carried out on the accumulated data which still takes up only 6 registers. I was hoping to mimic this behaviour with linest by creating code to calculate and read the data row by row, within VBA and accumulate this data in Linest, in this way ultimately to get the regression result. There is no point in recreating all the data on the sheet within VBA, either it can be recreated within VBA, row by row or it cannot. If not then my question is also answered and I am trying to do something that is not possible.

The problem is not that what you want may not be possible; it surely is. The problem is that you posted an example with a lot of irrelevant clutter and no one can tell what you're trying to do.

A lucid, minimal example, sans clutter, would help. You can add the clutter back later.

So you have one LINEST formula that does a third-order polynomial regression of col R onto col Q, and another that does col AH onto col AG. Why is that not sufficient? Why do you need VBA? Because the number of rows can vary? Something else?

It still feels like we are trying to pin down exactly what part of this you need help with. How much of your question is about calling LINEST() from VBA, and how much is all of the calculations that come before calling LINEST()? This

I Thought that if I could switch the whole process over to VBA I could get rid of the sheet completely.

sounds to me like your question is more about translating those Excel functions into VBA. Outlining the process that I would expect to see:

1) I would expect to translate the A -> Q:R calculations into VBA. I have not looked at this sequence of calculations in detail, but it looks like basic functions and operations that should have VBA equivalents. (Also translate S -> AG:AH into VBA).
2) Nest the sequence of calculations from step 1 into a loop that will accumulate the results into suitable arrays. The R (known_y) calculation will accumulate into a 1D (either nx1 or 1xn) array, while the Q (known_x) calculation will accumulate into a 2D array (either nx3 or 3xn). Similar for the S -> AG:AH sequence.
3) As before, once you have appropriate arrays containing the known_y and known_x values, you can call LINEST() to get the polynomial coefficients.

I have not yet looked at the details, so I have not worked out specific coding, yet. Which part of that sequence of steps do you expect to have trouble or questions with?

My understanding is this. Columns A/B/C/D/F columns are your input data; everything else is calculated from that.

	A	B	C	D	E	F
4	E	N	brg	L	Ch	Lvl
5	0.000	0.000	278.2891	34.000	83.816	15.297
6	0.000	0.000	278.2891	33.995	83.811	15.297
7	0.000	0.000	278.2891	33.990	83.806	15.297
8	0.000	0.000	278.2891	33.985	83.801	15.297
9	0.000	0.000	278.2891	33.980	83.796	15.297
10	0.000	0.000	278.2891	33.975	83.791	15.297
11	0.000	0.000	278.2891	33.970	83.786	15.297
12	0.000	0.000	278.2891	33.965	83.781	15.297
13	0.000	0.000	278.2891	33.960	83.776	15.297
14	0.000	0.000	278.2891	33.955	83.771	15.297
15	0.000	0.000	278.2891	33.950	83.766	15.297
16	0.000	0.000	278.2891	33.945	83.761	15.297
17	0.000	0.000	278.2891	33.940	83.756	15.297
18	0.000	0.000	278.2891	33.935	83.751	15.297
19	0.000	0.000	278.2891	33.930	83.746	15.297
20	0.000	0.000	278.2891	33.925	83.741	15.297
21	0.000	0.000	278.2891	33.920	83.736	15.297
22	0.000	0.000	278.2891	33.915	83.731	15.297
23	0.000	0.000	278.2891	33.910	83.726	15.297
24	0.000	0.000	278.2891	33.905	83.721	15.297
25	0.000	0.000	278.2891	33.900	83.716	15.298

Everything else is calculated from that.

A user-defined function could accept the data in those columns, replace the calculations in all of those other columns, and spit out the coefficients of the regression -- or whatever. is that correct?

If so, it would be straightforward, if a little tedious, to code.

If that's indeed correct, my suggestion is that you post in the commercial services column, and offer whatever you think three or four hours of time is worth. I don't post there, but someone could knock this out without difficulty once they manage to get you to be a little more concise in your explanations.

If your question is, can you regress a range say, 6 rows at a time, of course; that can easily be done with formulas.

I'm bowing out of this thread; good luck.

No, LINEST() cannot do regressions that way. LINEST() works on the full data set at once.

It has been a long time since I used one of those calculators for regressions. As I recall, those calculators worked by updating and storing values for sumx, sumy, sum(x^2), sum(y^2), (sumx)^2, etc. with each press of the "add data" button. Then, when you requested the slope and intercept, it knew how to calculate slope and intercept from those various summations (you should be able to find those formulas from a good stats text or internet tutorial). While LINEST() cannot work this way, it should be fairly straightforward to create some variables to store these summations and then compute slopes and intercept at the end.

The main potential disadvantage I see to doing it this way is that this kind of algorithm seems like it will be more susceptible to floating point and other numerical instability errors. Microsoft has tried to follow best practices in programming the LINEST() function to use more stable algorithms that are less prone to floating point errors. I don't know what advantages you are expecting from this kind of "point by point" algorithm or the disadvantages you expect from using LINEST(). Sometimes, this is the kind of programming decision we must make. I guess I would suggest that you be careful here. You may be able to find a way that reduces memory storage requirements (in a day when memory is almost never a limiting factor for this kind of calculation) but that is more prone to numeric errors. Making this kind of judgement call is beyond my programming expertise, but it may be important to making this programming decision.

This ?

This is what I meant by incremental regression:

	A	B	C	D	E	F	G	H
1	x	y		x^3	x^2	x	b
2	65	91
3	37	91
4	91	2
5	60	93
6	27	71
7	94	52		0.00128	-0.27152	16.80335	-214.75058	D7:G7: {=LINEST(B2:B7, A2:A7 ^{1,2,3})}
8	62	66		0.00214	-0.41285	23.55138	-311.87741
9	51	4		-0.00366	0.64761	-34.81011	607.82755
10	88	32		-0.00254	0.45323	-24.65749	452.69293
11	89	63		-0.00224	0.42030	-24.16734	459.01487
12	3	79		-0.00149	0.23984	-10.13116	106.79080