Solver Question

[Greg Toews]

My equation calculates an interest rate

A1*C1^B1 + A2*C1^B2 + ... + A11*C1^B11 = D1

Columns A, B and D are given. The only unknown is C1 which I want to solve
for. I can do this using solver but I would like something that updates when
columns B and D change.

Can I do this without using the solver or have the solver automatically
update?

Thanks,
G

[Daniel CHEN]

Most likely you need VBA code to do this.
Let the program captures that you make changes to B or D, and then run
solver automatically.


--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download

=================================
"Greg Toews" <[email protected]> wrote in message
news:DYCSf.153816$H%4.135827@pd7tw2no...
> My equation calculates an interest rate
>
> A1*C1^B1 + A2*C1^B2 + ... + A11*C1^B11 = D1
>
> Columns A, B and D are given. The only unknown is C1 which I want to solve
> for. I can do this using solver but I would like something that updates
> when columns B and D change.
>
> Can I do this without using the solver or have the solver automatically
> update?
>
> Thanks,
> G
>

[Daniel CHEN]

Most likely you need VBA code to do this.
Let the program captures that you make changes to B or D, and then run
solver automatically.


--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download

=================================
"Greg Toews" <[email protected]> wrote in message
news:DYCSf.153816$H%4.135827@pd7tw2no...
> My equation calculates an interest rate
>
> A1*C1^B1 + A2*C1^B2 + ... + A11*C1^B11 = D1
>
> Columns A, B and D are given. The only unknown is C1 which I want to solve
> for. I can do this using solver but I would like something that updates
> when columns B and D change.
>
> Can I do this without using the solver or have the solver automatically
> update?
>
> Thanks,
> G
>

[MrShorty]

Two possible approaches, depending on your abilities and specific needs:
1) VBA code associated with a worksheet_change or calculate event that calls solver for you
2) If you are familiar with programming numerical methods, you could code a UDF that will use a Newton-Raphson algorithm (or other numerical algorithm) to solve the problem. UDF would then be called from C1 of the spreadsheet, and, if coded correctly, will fit into the dependency tree and will update automatically as values change.

[Greg Toews]

Thanks, I will go with the VBA route since my sheet already includes some
code.