Solving for any blank variable from a formula

I've tried searching for this but couldn't find anything that matched this problem.

I'm trying to figure out a way to enter in a single formula, one which has been solved for zero. And have excel solve for any variable that has been left blank.

So as a simple example take the equation "2X + 4Y - 8Z = 0"

I would like to enter this formula into excel and then fill in two of the variables and have it solve for the third. So if I enter values for X and Y it gives me a value for Z. Or if I give it values for X and Z, it gives Y. And if given Y and Z it solves for X.

Now this is a simple example, so I know it would be easy to rearrange the equation for each of the three variables and type all of them in to different cells. But the reason I would like to know if there's a way to do this is because I routinely deal with formula's that have 20 or more variables easily. I'd rather not have to rearrange that many times. It would be great to just solve for zero, plug that one equation in, have all my variables listed. Then whichever value i leave blank it solves for that unknown. As you can imagine with that many variables I've hesitated from even thinking about doing nested IF statements. I shudder at even trying to keep that straight.

Is there any way that this can be done automatically in excel?

And yes, i can do simple enough VBA (not a master of that, but I get by). So if that's an option I'd love to hear any ideas.

Originally Posted by FDibbins

Hi and welcome to the forum

I was starting to think that you could use an approach like this....

=IF(A1="",formula to solve for that,IF(B1=0,formula to solve for that,formula to solve if C1=0)

But that kinda got blown out of the water when you said you have 20+ variables (Might still be food for thought though?)

Yeah originally that was my thought too but I got a headache imagining writing that out. I'm a student in petroleum engineering and some of the equations we deal with in material balance of a reservoir can get a LOT of variables involved.

So I was really hoping someone knew a simpler option. Heck even a VBA routine that could be called for different equations would be great.

Originally Posted by martindwilson

goal seek will do it for one changing variable

Thank you. I had been thinking of a formula based approach i could enter in a cell or a VBA solution. Hadn't considered Goal Seek. It's not automatic in that i would have to run it each time to solve for a different blank variable. But it does do it. So it's much better than IF statements for sure!

If anyone has any other ideas for a different approach i'd still be interested.

Pl see the attached file .I feel it satisfies all your needs.

With SUMPRODUCT formula also.