MrExcel Publishing
Your One Stop for Excel Tips & Solutions

solve a formula when the knowns could vary

Posted by wayned on August 13, 2001 1:27 PM

say I have a formula a+b=c. The user opens the spreadsheet and knows values for, say, a and b and the spreadsheet solves for c. BUT he may know a and c and want b, OR he may know b and c and want a. How can I set up three cells so that no matter which two values are known, the third value is calculated. (In the real world I have a 5-variable formula, with ten different lines; one for each combination of 3 known values, but this is very klunky & confuses the users). Putting a formula in each cell works once but the first entry wipes out the formulae.

Posted by Damon Ostrander on August 13, 2001 1:40 PM

Hi Wayne,

I'm emailing you a workbook that demonstrates how to do this. What makes this possible is the use of the Worksheet_Change event in the worksheet's event code area (right-click on the worksheet's tab, and select View Code). This is also a great demo of the use of interactive graphics in Excel--I think you will enjoy it. It solves a simple geometry problem in which there are 5 parameters, but specifying the values of any 2 determine all the others. The program computes the remaining three depending on which 2 you choose to enter.

Watch your email.