Creating A Self Solving Table

Hey,
I'm a college student that has just recently started using excel. I have a rather low understanding of it I suppose. I have been using excel has a substitute to my graphing calculator and it has been doing a decent job (aside from one flaw which I don't understand). Anyway, I would like to create a table that will help me solve complex equations for one of my classes. I know how to do the problems, I just don't know how to do what I want in excel.

Note: I would like to mention that this is a long read including the excel document which is necessary. Although it would long I really do need help. If anyone could help me with the questions I'm asking it would quite literally impact my grades in a positive way and in so I would be extremely grateful. Please give my questions a chance.

Basically I am solving Acid/Base problems in my chemistry class that involves a system like the following.

HA --> H + A
Initial
Change
End

Basically the table would be 3 by 3, one for HA, H, and A with the 3 rows Initial, Change, End. I got that no problem. However, I want excel to automatically fill out values for the rest of the table depending upon what I type in but there are also some restrictions that I will need to do (I presume with an if/then clause which I don't know how to use in excel).

I'm going to attach what I've done so far because it is neater and easier to understand. I will also include questions in the excel document, yet again because it is neater.

Everything technical requiring extended explanation is in the attachments. I have a few quick questions that I'd appreciate having answered too.
Q1) When I use the Cos functions (tan, and Sin as well) in excel the answers are wrong. I know why but do not know how to fix it. I need to perform my cos calculations in degrees but excel is performing them in radians. How do I fix that?
Excel Cos(50) = 0.964966028 Calculator Answer = .64
Q2) How do I use variables? Like in physics g=9.8. Is there a way to define g as 9.8 and simply enter g in my equations?
Example: =(g*2)
If that was typed in a cell it would display the following 19.6 because 9.8 * 2 = 19.6

I have a few more questions but have to run to class.

Thank you so much who ever actually helps.

Your spreadsheet was a little confusing. Is there a set equation for each of the 9 cells in your 3x3 table? I know you wanted them to be dynamic and change equations based on inputs in other cells, which I only know of doing using a series of if() statements. Maybe you can use a format like this to better explain what each cell should do:

A1|B1|C1
A2|B2|C2
A3|B3|C3

A1= ...
B1= ...
...

This might help the rest of us understand what you want.

Originally Posted by ChemistB

I'll look at your spreadsheet next. First

Q1. = COS(RADIANS(50))

Q2. You can put "g" into a cell and then in the name box (where it currently has the cell ID on the ribbon (i.e. A1) type in G and ENTER. then you can use G and it will always refer to that cell. Does that work for you? Alternately you could use a defined name.

It wouldn't help necessarily but I would appreciate knowing how. By the way this is via phone

Originally Posted by madadd

Your spreadsheet was a little confusing. Is there a set equation for each of the 9 cells in your 3x3 table? I know you wanted them to be dynamic and change equations based on inputs in other cells, which I only know of doing using a series of if() statements. Maybe you can use a format like this to better explain what each cell should do:

A1|B1|C1
A2|B2|C2
A3|B3|C3

A1= ...
B1= ...
...

This might help the rest of us understand what you want.

I don't understand what u mean by your format example. My 3x3 table is just book keeping essentially. I just want it to be displayed. I will reply in more depth when I get home. Please ask any questions needed.

I

Originally Posted by ChemistB

Defining a constant as a name in Excel 2010
On the ribbon, under Formulas Tab is "Define Name"
In the dialog box,
Name: g
Refers to: 9.8
Now in any cell, put in = g and it'll display 9.8

That is only for the current excel document right? Not for any and all future documents.

Originally Posted by madadd

Your spreadsheet was a little confusing. Is there a set equation for each of the 9 cells in your 3x3 table? I know you wanted them to be dynamic and change equations based on inputs in other cells, which I only know of doing using a series of if() statements. Maybe you can use a format like this to better explain what each cell should do:

A1|B1|C1
A2|B2|C2
A3|B3|C3

A1= ...
B1= ...
...

This might help the rest of us understand what you want.

Ok, now that I'm home I can type.

The master formula according to your grid system is the following.
A1 = B1 + C1
Row 2(A2 through C2) is the change or delta.
If A1 is given (For example lets say .05) but B1 and C1 are 0/empty then you need to take from A1 to give to B1 and C1
Hence, Row 2 will consist of the change. This step is strictly book keeping but it should read as follows.
A2: -x
B2: +x
C2: +x
It is simply + or - x depending upon which way the formula is going. (Also, if B1 or C2 for example is actually 2B or 2C the formula will become A1 = 2B1 + C1. Meaning that B2 would be + 2x instead of just +x.)

Row 3 (A3 through C3) is another book keeping step. Since A1 was defined as .05 in my example we know that is the only value.
A3: .05 - x
B3: x
C3: x
Since we assume there is no B or C to start the values are simply X. We then solve for X, for example lets say after we do a quadratic we find X to be 0.0025
There should be a 4th row so I will make one. (Note: there has to be a cell that will tell me that X is .0025, just a separate cell somewhere.)
A4: .05 - .0025
B4: .0025
C4: .0025
A4 should just be the answer, I just don't feel like doing it in my head at the moment.

Originally Posted by ChemistB

That is correct.

Thanks.

Any additional help/answers would be greatly appreciated as well.

Originally Posted by MarvinP

Hi Slaya,

I think the problem here is that Excel is so big a product and does so many things it is hard to understand what you want from it. Perhaps you should see what it can do already by a little study. Here are a few links where Excel and Chemistry come together.
http://www.santarosa.edu/~oraola/S09...xcel%20lab.pdf
http://spectrum.troy.edu/~cking/ChemFormat/
http://writing.software.informer.com...as-with-excel/

There are even books on the topic at:
http://www.amazon.com/Spreadsheet-Ap.../dp/0471140872

I'm not trying to impress you but just say we are here to help you with a problem you have with excel. Not to give yoiu a solution but help you create your own solutions.

Study a little more and get stuck, trying to get Excel to do something and then give us a better question and/or sample and let us try again to help.

I know what I want and I know what I need to do. I simply don't know the excel language. I can describe to you what I need to get done and how I want it done. I just don't know how to do it in excel. I believe my excel attachment covers everything I need.

Ok, so here's what I got so far.

A1

Please Login or Register  to view this content.

B1 & C1: User Input

A2 Is this taking away just from B2? or is the combo of B2+C2?

Please Login or Register  to view this content.

B2

Please Login or Register  to view this content.

C2

Please Login or Register  to view this content.

.
I'm not sure I understand this: "Also, if B1 or C2 for example is actually 2B or 2C the formula will become A1 = 2B1 + C1. Meaning that B2 would be + 2x instead of just +x."

Does that mean 2x instead of 2B or 2C?

A3

Please Login or Register  to view this content.

B3

Please Login or Register  to view this content.

C3

Please Login or Register  to view this content.

As for the quadratic

Please Login or Register  to view this content.

or if you're jsut looking for the positive value

Please Login or Register  to view this content.

This is as much as I understand what you're trying to do. Let me know if it's not. You should have enough information to do most of what you want if you just reverse engineer some of these. Otherwise I'd suggest walking through another example so that I can reverse engineer your table to determine the functions of each cell.

Originally Posted by madadd

This is as much as I understand what you're trying to do. Let me know if it's not. You should have enough information to do most of what you want if you just reverse engineer some of these. Otherwise I'd suggest walking through another example so that I can reverse engineer your table to determine the functions of each cell.

Unfortunately it is not what I'm looking for. You did however give me the quadratic formula which I appreciate.

I need the following things or something along those lines.
1) I need a way to insert text into a cell.
IE: if A1 = 2, B2 = "Hello"
(I don't know how to type if/the clauses in excel so that will have to do.
2) I need a way to use multiple formulas in one cell or have one cell represent the answer of multiple formulas.
IE: A1 = (=1+1), A2 = (=2-1), A3= (Answer of either A1 or A2)
Since it is the same answer just achieved different ways how can I make excel take what ever answer is solved? In my case it will be more of the following...
X = 1, Y = 2
A1 = (=x+x), A2 = (=y-x)
I may know what X is, but not what Y is. So the formula in cell A1 would work and not the formula in A2.
3) To make things easier can someone explain to me how to enter an If/Then clause in excel?
4) Can someone explain to me how to use variables. (I'm pretty sure a guy in a previous post covered this but I'm not sure.) I remember seeing something with $'s representing variables or something. Like $A$ symbolized A as a variable not a letter or something.
5) Any other noteworthy tips.

I will mention that I am not using excel as a spreadsheet. Instead I am using it more as a calculator. So instead of spacing every number into different cells and using tables and charts to perform anything I will be using 1 cell in most cases to perform multiple mathematical operations at once to simply yield an answer.

Originally Posted by MarvinP

Hi Slaya,
Using Excel without separating data into multiple cells is like using a screw driver as a hammer.
Start on these 6 sheets http://spreadsheets.about.com/od/exc...el_formula.htm

You need to understand how to enter a formula IN A CELL.
You need to understand Absolute vs Relative addressing.

You are asking us to teach you stuff but in reality you need to read, do what is suggested and learn by doing.
I think you have an idea of excel that is without any background on what it can do or how it might do it.

On your next post - tell me what you learned from the links I've posted above.

Despite the risk of sounding rude what you posted was of absolutely no help nor was it anything near what I was asking for. Despite your analogy my goal is to use excel to crunch numbers hence what it is meant for. Whether or not I decide to crunch them in a very long, drawn out, and simplistic method, or a more involved and complex method is the choice of the user/myself. If you do not know how to answer my questions or simply don't want to then please don't send me on a wild goose hunt. I reviewed the website you sent me and wasted my time doing so.

In addition, as to asking for you to teach me that is also incorrect. I am asking you to tell me the mechanical syntax for an operation in excel. I am in no way asking you or any other user to teach me anything. I have to interpret my own work into excel language, meaning I am not simply plugging random values into random cells. They need to be ordered meticulously. Not only will the values change each time, but the actions in which the values will have performed on them may change as well. Simple operations such as A1 + B1 are not only a hassle in this case but blatantly inferior as well.

Originally Posted by madadd

Ok,
1) In B2

Please Login or Register  to view this content.

So thats the format you'd use and the second is a nested if for multiple arguments. If you have a whole bunch of different text based on a bunch of different requirements, then you'll probably want to make a helper column.
2)

Please Login or Register  to view this content.

So here A1 has to not be blank AND not 0 for A3 to display its value. Otherwise it'll display A2. Again, you can do a nested if statement if you need the same checks for A2
3) Just use these examples as help. Also, if you need help understanding a function, hit the fx button located at the top, right in front of the input bar.
4)Don't know. $ is usually used to lock a cell. So saying you used A1 in an equation and you dragged that equation out among multiple cells. If you drag it out to the right, that A1 will change to B1, C1, etc however far you drag. If you locked the cell in the equation by writing it as $A$1, then no matter how you drag it out, it won't change.
5) Using that Insert Function Window (by pressing the fx button) should help you a lot when trying to find the functions you want to use and how to use them. Even going into the help menu will often give examples of each.

I assume you were typing the following step...
2)

Please Login or Register  to view this content.

In A3. Now, I don't really understand what that line of text is saying in english in a sense.
If A1 is greater than or less than what ever is in the quotations, then A1 is greater than or less than 0? What does the A1 and A1 apply to at the end then in english?

Also, you said I cannot have a blank or 0 value for any of the things in the if/then statement. If I replaced greater/less than with an greater than or equal to statement could I use zero? In the desired scenario I am using a balancing table in a sense.
So if A = B + C (A, B, and C are variables in this case not cells)
A is like 10, but there are 0 of B and/or C
To balance this we need some C and B. In order to get C and B we must take from A.
That is the entire concept behind the table I used in my spread sheet.
In addition taking from A to get values for B and C is the entire reason I am planning on using if/then statements. This is because if there happens to be values for B and C but 0 A to start then naturally we will not be able to take from A and give to B and C, instead we will need to take from B and C (Because B and C have given values, not zero) and give to A ( because a would not have a given value, or in other words assumed to be zero).

I appreciate your help madadd. I hope I am at least some what interpreting my question to you in an understandable way.

Um, that code for part 2 is to be placed in A3. And in simple terms, "If A1 is NOT blank AND A1 is NOT 0, then display A1 contents, else display A2 contents". I'm not saying this is going to be the right fit for your equation, but it gives you a basis for which to modify. The following does the same thing:

Please Login or Register  to view this content.

So "if A1 is blank OR A1 is 0, then display contents of A2, else display contents of A1".

In A1 i think you want

Please Login or Register  to view this content.

.
And in B2 and C2 put this:

Please Login or Register  to view this content.

Having these all will allow you to overwrite one of them with a user input and the others will react and balance appropriately.

And I think what MarvinP is trying to get at is that a lot of what you seem to want involves the very basics of excel. In all of these equations (except the quadratic and if/then's) It's really just writing out basic math equations. If you're confused on Syntax to do A1-C2 or what those refer to, then yes, 30 min learning the basics from a how-to site will be invaluable. Likewise (maybe not in the EXACT link he specified) you'll learn some other basic logic operators (like "<>" means NOT EQUAL TO, as in opposite of "="). Everyone here is just trying to help, but there are a few things that you'd be better off finding out for yourself so that instead of just receiving an answer, you also understand and can use that answer in the future.

Originally Posted by MarvinP

Hi,

Read this as I have now assessed your level of experience with excel.

http://www.ozgrid.com/Excel/free-tra...n-25-basic.htm

Note the explaination of "<>"

I hope this doesn't waste your time.

Not exactly what I was looking for but it definitely has some material I've been looking for. Thank you very much. As for the colon command I believe that pertains to one of my questions. If anyone could clarify that would be greatly appreciated.

As I said I plan to have multiple equations solve for 1 answer/variable. So for example let's say I have 3 different equations that all solve for X as presented below.
A1 (Where my value for X will be, the answer)
B1 = Eq 1 to find x
C1 = Eq 2 to find x
D1 = Eq 3 to find x
So for example, if I don't have enough information in the given problem to solve Eq 1 or 3 but I have enough to solve Eq 2 then Eq 2 (C1) will yield a numerical value pertaining to X.

If I cannot solve for Eq 1 or 2 but I can solve for Eq 3 then D1 will yield a value pertaining to X while B1 and C1 will not and so on.

Now my question is, since A1 will be displaying the raw answer for X, can I make A1 read the following.

=(B1:C1:D1)

Will that mean what ever answer I get (Since in most cases only one of the 3 cells B1-D1 will display an answer) will be displayed in A1?

If so, what if 2 of the cells display an answer (They will be the same answer), will A1 still display the the correct answer or display error? (IE: B1 will display .01 and C1 will display .01 because they solve for the same variable different ways.)

Originally Posted by MarvinP

Look at the example, numbers 6 and 7 at
http://office.microsoft.com/en-us/ex...010062562.aspx

Originally Posted by MarvinP

See the examples in the attached

Read both of them quickly. I don't get it now but at first glance it seems like what I'm looking for in terms of the Text + cell information.

When I get a chance I'm going to be reading them both over extensively but before I do could you tell me if your link/attachment includes how to have one cell have an answer from multiple other cells such as I mentioned in my prior posts?

Thanks again. I will post back after reading. Please subscribe to the thread until then.