MACRO to insert and adjust LINEST arrays

[ad9051]

Hi guys, first up many thanks for taking the time to look at my problem.

Essentially I want to be able to insert a linest Array on some pre generated data, the linest Array will reference data with an N number above or equal to 5. The easiest way to get a feel for the problem at hand is to take a look at the attached example. Where I go into detail, with examples.

Thanks again for looking at the problem!

All the best

Alan

[ad9051]

Ok this hasn't really had 900+ views, I had a refresh script on the page to see if anyone replys. Since i need a macro to do this quite urgently. BUMP

[ad9051]

Bump again, would really appreciate any help with this one. Many thanks

Alan

[shg]

Assuming the data of interest always starts in row 18, and that there is always a value <5 following the data of interest in col AZX, then define this dynamic range with WORKSHEET scope on each worksheet:

tbl refers to: =Sheet1!$AZW$18:INDEX(Sheet1!$AZZ:$AZZ, MATCH(TRUE, Sheet1!$AZX$18:$AZX$1048576 < 5, 0) + ROW(Sheet1!$AZX$18) - 2)

Then the two formulas are

{=LINEST(INDEX(tbl, 0, 3), INDEX(tbl, 0, 1), , TRUE)}

{=LINEST(INDEX(tbl, 0, 4), INDEX(tbl, 0, 1), , TRUE)}

I'd take the same approach using VBA, depending on whatever code you already have. The information above is sufficient to record a macro.

[ad9051]

Cheers SHG, I will give it a try, many thanks

Alan

[ad9051]

Hi SHG thanks for your suggestion, I cannot get the first formula to yield any results, i get #N/A returned. Also am i correct in thinking this method would only work assuming the Linest always started in row 18? The further examples provided show that it can start in any row, to produce the longest string of N numbers that are >=5.

Assuming the data of interest always starts in row 18

I thought this referred to the area of interest for finding the largest >=5 array not every linest.

Please let me know which is the case.

Many thanks

Alan

[shg]

How does the data come to appear on the sheets?

[ad9051]

Each value within the table is referenced from the result of a logistic regression macro, so each cell within the table is a formula referencing a macro generated value.

Alan

[shg]

If the data is coming from a macro, why not put out only the data you need, and put it in consistent ranges?

[ad9051]

Because the macro in question is far beyond me, it is a logistic regression macro, which at one point I had you take a look at. The table contains the two constants it creates, the N number for the dataset, and the array size from which the logistic regression macro had been runin this case 3-22.

[watersev]

hi Alan, please check attachment, press Update button after changing N-values column.

Assumptions:
1. The code omits row 17 even if it has value >=5. 17th row is the first one as in the sample provided.
2. If there are several groups with the same quantity of numbers that are >= 5 the first one will be used.

[ad9051]

Thankyou Watersev! Works perfectly as far as i can tell so far!

Thanks again

Alan