Excel Question

I have not figured out how to make Excel do calculations with recursive equations.

For example, to calculate the expected waiting time for 2 consecutive sevens to be rolled you set up the equations:

a = 1 + 5a/6 + b/6
b = 1 + 5a/6

Solve for a and the answer is 42 rolls. That one is very easy but when there are several more states it gets to be a real pain in the a$$ hashing out the answer manually.

When I enter equations like the above into excel cells I get circular references errors. If I enable iteration it may give me numbers but not the right ones.

Can this be calculated in excel ? I would assume so since it’s just basic math, but I don’t know how.

Excel can do matrix operations. Look up MMULT and MDETERM. It is too involved to explain here.

Quote: Ace2

I have not figured out how to make Excel do calculations with recursive equations.

For example, to calculate the expected waiting time for 2 consecutive sevens to be rolled you set up the equations:

a = 1 + 5a/6 + b/6
b = 1 + 5a/6

Solve for a and the answer is 42 rolls. That one is very easy but when there are several more states it gets to be a real pain in the a$$ hashing out the answer manually.

When I enter equations like the above into excel cells I get circular references errors. If I enable iteration it may give me numbers but not the right ones.

Can this be calculated in excel ? I would assume so since it’s just basic math, but I don’t know how.

Ace2,

This link explains how to solve systems of linear equations in Excel:

https://www.excel-easy.com/examples/system-of-linear-equations.html

For your example, if you multiply each equation by 6 and rearrange, your system of equations becomes

-a + b = -6
5a -6b = -6

Thus, using the "A·x = b" notation from the link, your A matrix is

-1 1
5 -6

your x vector is

a
b

and your b vector is

-6
-6

Solving using the MMULT and MINVERSE Excel functions (as explained in the link above) gives a = 42 and b = 36.

Hope this helps!

Dog Hand

Ace2,

The method I showed above becomes somewhat tedious when the number of equations becomes large.

An alternative method involving the Excel Solver add-in is described near the bottom of this webpage:

http://turing.une.edu.au/~math170/Pracs/prac8.htm

If you use this method, you needn't rearrange your equations to the "A·x=b" format.

The Solver add-in is in the Data tab on the Excel ribbon, generally on the right-hand side. If you don't see it there, click on File, select Options, select Add-ins, click Go, and check the Solver box: this will install the add-in on the Data tab in the ribbon.

Hope this helps!

Dog Hand

Many thanks for the replies. Those excel functions will save me a lot of time in the future. I worked through a few examples to test them out.

I did have one matrix algebra course some time ago, but sort of like calculus I never really had any practical uses for it before.

I use VBA for anything complicated like this.