Forum Discussion

diegol_81's avatar
diegol_81
Copper Contributor
Jan 04, 2023

Eigen vectors/values with LAMBDAs

Hi all,

 

I'm trying to calculate Eigenvectors and Eigenvalues without resorting to R, so the parent process is completed in Excel from beginning to end.

 

A search in these forums suggests this is a question that's come up a just a few times, and in all cases no response was received. This, and a quick dive in YouTube tutorials suggest the task is nontrivial and could lead to using the Solver addin and several intermediate steps. Or at least that seems to be the case using pre-DA Excel.

 

With the advent of LAMBDAs, which I'm still trying to get my mind round to, I feel this could be doable. After all, LAMBDAs do support recursion - although the more I read about the matter, the more I feel best practice should be dropping recursive LAMBDAS in favor of REDUCE or some other helper formula.

 

If anyone has attempted to solve this problem with LAMBDAs, I'd very much appreciate their input.

 

Thank you.

excel
office 365

2 Replies

  • edugca's avatar
    edugca
    Copper Contributor
    May 12, 2023

    Hi, diegol_81 !

    Yes, it is possible to use Lambda functions to calculate eigenvalues and eigenvectors. I have a project on GitHub that accomplishes that through recursion. What you need are functions EIGENVALUES and EIGENVECTORS of that project:

    https://github.com/edugca/xlMATRIX

    • diegol_81's avatar
      diegol_81
      Copper Contributor
      May 20, 2023

      Hi edugca,

       

      Thank you for your reply! And sorry for the delay in response.

      It was not until yesterday that I could have a look at the repertoire of formulae that you have developed, and I must say I am amazed! Congratulations on your terrific work!

       

      That said, the EIGENVALUES() and EIGENVECTORS() formulas, as far as I could thest them, don't seem operative enough for my application (a matrix with dimension 18, and non-integral numbers -not sure if the latter could be causing trouble though). Most of the times, they do not produce all the eigenvalues/vectors (for a given set of parameters -eigenMin, eigenMax, eigenStep-, both produce the same number of eigenvectors as number of eigenvalues. But somehow they do not produce the full set, which R does). That's one big issue. The other issue is calc time, which can get very slow.

       

      To tackle the first issue I tried playing with the eigenMin and eigenMax parameters to widen the interval the formulae sweep through; and with the eigenStep to make smaller increments, at the expense of performance, up to the point where runtime was clearly not viable (what I mean is, maybe there could be a smaller increment which would add way more steps and eventually produce the values/vectors, but I did not test thoroughly if runtime was already a no-go).

       

      As far as I gathered from your lambda coding, the eigenvalues algorithm generates a vector of many candidates for eigenvalues. It then tests each one to see if they satisfy the characteristic equation

      |A- candidate I | = 0. If it does, the candidate is an eigenvalue. But it goes beyond and also checks for sign changes in the LHS of the equation, as candidates are tested sequentially. In the event of a sign change, the candidate is regarded as an eigenvalue. I think I get the rationale, but for some reason it's not yielding all the eigenvalues.

       

      I'll keep exploring it and upload an example file if still not successful.

       

      Thanks!

       

      Regards,

      Diego

Resources