How to find intersectin of two arrays

**joeriess** · 11-07-2008, 01:13 PM

What's the best way to find the intersection of two polynomial arrays, preferably in a user defined function so that I don't have to use lots of cells on a worksheet? Specifically, I have two x,y arrays, one which slopes from bottom left to upper right, the other that slopes from upper left to bottom right. I thought I'd use the LINEST function to find the polynomial coefficients of each array (m1, m2, ..., mn), and iterate to find the "predicted" y value based on assumed x for each array until both y values are within some reasonable error.

The second part of this problem is, I can't get teh LINEST function to work in a user defined formula in VB for a polynomial to find the polynomial coefficients (m1, m2, ...mn). I think I'm having a problem with the use of brackets, as shown below:

Please Login or Register  to view this content.

Or, should I be using a regression formula to find the intersection of these two arrays?

**Andy Pope** · 11-08-2008, 08:26 AM

For the Linset part.

Please Login or Register  to view this content.

**joeriess** · 11-08-2008, 05:10 PM

Thanks for the tip, Andy, but I still get the same problem when I try to raise the array to a power. Any other tips?

Also, is it possible to call Solver from a macro?

thanks!

**shg** · 11-08-2008, 07:37 PM

Post the workbook?

**Andy Pope** · 11-09-2008, 11:17 AM

This works for me whether using ranges or variant arrays loaded from range.

Yes you can use VBA with solver. Try searching and post a new thread is still stuck.