Excel calculations help

[Sepukku]

I'm mildly competent in excel but my latest project is a little harder than usual, I need to plug 3-9 sets of with at least 3 variables (xyz) data and have it calculate all formulas or functions that would work for the sets of data for Ax+By=z type data ... Would anyone know how to set this up in excel?

[Buffy]

Can you post an example from the spreadsheet? I'm not great with excel, but I've been messing around a whole lot with vlookup and pivot tables lately. I'd be willing to try to help.

[Sepukku]

Will type up an example once home, hard to type one out on phone

[Sepukku]

So I would need to take 3-9 sets of data with 2 or more variables (3 is the bare minimum I feel will be sufficient, but 2 is a starting point), such as:

2x+y = 15000
x+y = 10000
-x-y = -10000
x-y = 0
2y = 10000

And Excel would need to calculate the possible sets, if any, for X and Y that would work for that.

It would get progressively more complex, however, where I'd have something like:

1200a+500b+c+d+2e+f-g = 3400
1500a+1500b+c+2d+e+f+g = 12,000

etc
etc

[Buffy]

So will you be creating the formulas manually and just want the data to already exist?

Cause if so, you could create the formula, then create a source page and make the variables = source page.

I do that all the time with some stuff

edit: if true, sounds like you need to combine a formula with a vlookup

[Sepukku]

I do not know how to use vlookup, and yes I'd be entering something like a formula (they're not a formula when information is received but would be processed into a usable formula as needed) and I would need excel to be able to run for if there's any possible solution for the formulas (wouldn't always be one, sometimes there would be more than one). Like in my first example there's x=5000 y=5000. But in some of the more complex ones there'd be a decent chance at no solution or many solutions.

[Buffy]

Well it should be possible if the all the data is the same and it is only the formula that is changing.

I'm messing with an example now in excel, but I'm realizing I haven't put a formula in a vlookup before and I'm trying to see if you can combine the two. Instinct tells me yes, but I haven't actually done before.

[Sepukku]

Any luck Buffy?? Don't sweat it if not; at least now you've helped me narrow down which specific functional item to look up in excel and I can google a tutorial on that as needed today. Appreciate your help!!

[Xerlic]

I've never used Excel for this, but you can use Solver to figure something like this out. Google "using solver to solve linear equations" and you should be able to find a tutorial.

[RKenshin]

I can't help but think that this isn't easy to do in excel, at least not in a pretty way or without macros/scripts, plugins, or an obscenely complex set of formulas. Not that I know everything there is to know about excel, but I'm familiar with most of the more complex formulas and options.

The only way I can think of that might be easy (ish), is to use the RAND() function to assign a random value to all but one of the variables, and then solve for the remaining variable.

The RAND() function returns a random number from 0-1, and unfortunately is the only option to generate a random number in excel. You could either multiply the result by one of the static numbers, or some other mathematical operation, or combine this with the LOOKUP() function to create a dynamic table whose range will be based on the known variables of the equation. Could also combine it with the ROUND() function to keep the random numbers from getting too ridiculous.

Keep in mind that recalculating formulas (hitting F9 or changing the data in any cell of the workbook) will change the result of your RAND() funciton, unless you specifically turn off the automatically recalculate values on change option (which is also annoying to not have on otherwise, because cells well never update unless you hit F9 - easy to forget).

I could potentially try and put together an example later if needed, or otherwise feel free to ask any questions/extra details of it sounds greek.

[SDSD]

This is actually fairly straightforward in excel assuming the system has one answer. The real trick is in making the formula scalable for 1, 2, 3, ..., n variable equations, and also accounting for systems with multiple soln's.

The link below is a good general guide to accomplish what you're looking to do on a simple basis. You'll probably have to make some minor adjustments depending on the characteristics of your system but nothing you can't figure out. I wonder though, is there any reason you're tied to doing this in Excel?

If you're going to be repeatedly doing these sorts of calculations, try to see if you can get your hands on Matlab. Super over-powered but matrices (linear systems) are a joke with it.

http://www.excel-easy.com/examples/s...equations.html

[Sepukku]

My work requires Excel for this project as we can easily transfer data from the incoming stream into it....