Spreadsheet: 103961d1301594180-creating-a-self-solving-table-chem-acid-base-solver
Sheet: Sheet1

 |  | HA | <--> | H+ | A- |  |  |  |  | Entry |  | Answers
I | Initial | 0.05 |  | 0 | 0 |  |  |  | Ka =
C | Change | .05 - x |  | 0 + x | 0 + x |  |  |  | kb =
E | End | .05 - 9.5e-4 |  | 0.00095 | 0.00095
 | Ka = 1.8e-5
 | Final |  |  | 0.00095 | 0.00095 |  |  |  | PH =
 |  |  |  |  |  |  |  |  | POH =
 | [NAME] (H+x)( Ac+x)/(HA-x) |  |  |  |  |  |  |  | H+ =
 |  |  |  |  |  |  |  |  | OH- =
 |  |  |  |  |  |  |  |  | Pka =
The problems start by offering a molarity, IE: .05.
That would be the HB or HA value in this case (It doesn't matter the notation)
Since there is no H+ or A- the equalibirum has to go to the right. So the change is
Taken from the HA (-x) and added to the other side.
Now I have to solve for X.
In order to do so I use the given Kb or Ka value (they can be converted back and forth)
The equation becomes the following in steps…
 | 1)  Ka = (H+x)( Ac+x)/(HA-x)
 | 2) 1.8e-5 = ((x)(x))/(.05-x)
 | Note: Technically I should do a quadratic right now but I don't know how to write the quadratic formula in excel.
 | Note 2: Due to some rules I can assume .05-x is equal to .05 so I can drop the -x and the problem becomes the following…
 | 2) 1.8e-5 = ((x)(x))/(.05)
 | 3) 1.8e-5 * .05 = x^2
 | 4) sqrt (1.8e-5 * .05 )= x
 | 5) x =
 | Note: If you click cell C25 you can see how I write my formulas. Tips would be appreciated.
Now that I know what X is I need to fill in the last row on the table labeled 'Final.'
Repost of what I have
 | HA | <--> | H+ | A-
Initial | 0.05 |  | 0 | 0
Change | .05 - x |  | 0 + x | 0 + x
End | .05 - 9.5e-4 |  | 0.00095 | 0.00095
Ka = 1.8e-5
Final |  |  | 0.00095 | 0.00095
Now that I have H+ I need to solve for pH using the H+ which is the hydrogen Ion concentration.
To the right are the formulas I have created in an attempt to create what I want but they are rather confusing.
 |  |  |  |  |  |  |  |  |  |  | Entry |  | Answers
The goal is to enter a given value somewhere and then have the little table to the right |  |  |  |  |  |  |  |  |  | Ka =
automatically fill in the answers that I want. However, the way I currently have it set up |  |  |  |  |  |  |  |  |  | kb =
the answers appear to the right under what ever equation I have written.
My question is can I have one cell run multiple equations and pick the one that actually works?
If you look to the right of the collum named 'Answers' (N40) you can see the equations I've entered. |  |  |  |  |  |  |  |  |  | PH =
Essentially there are multiple ways to solve for items such as pH depending upon what is given. |  |  |  |  |  |  |  |  |  | POH =
At times there can be 2-3 different ways that will all work, it is only a matter of preference. |  |  |  |  |  |  |  |  |  | H+ =
 |  |  |  |  |  |  |  |  |  | OH- =
For example… |  |  |  |  |  |  |  |  |  | Pka =
pH = -log [H+] |  | H+ is what I solved for above, the X value in that problems case.
pH = 14 - pOH
pH = pKa + log[Base/Acid]
Those are 3 ways to solve for pH. What I would like is to have the formulas use the cells from the table that
I solved for X in. I of course would always have to enter the data correctly (put the numbers given in the right spots initially in the table).
This way for instance if I had the pOH value and the H+ value. Excel could do both calculations and they should equal the same number.
If it works that way I shouldn't have 3 different collums displaying answers (I don't want to have a different cell for every equations if possible)
(They should all display the same answer)
Example: |  | Entry |  | Answers
 | Ka =
 | kb =
 | PH = | 2.69
 | POH = | 11.31
 | H+ = | 0.002
 | OH- =
 | Pka =
Under entry I entered H+ = .002 first. That gave me a ph value in E64.
I then entered that value in [NAME] (pH = 2.69) and in G65 it displayed my pOH value 11.31
I enter 11.31 in C65 (pOH = 11.31) and in F64 it gives me 2.69 for my pH.
All the same numbers as you can see. I would rather just not have to look through different cells.
Also, these  numbers in the Entry collum I will be getting from the Table from earlier where I solved for X.
Additionally, I have a few more questions.
Q1) In the table below
 | HA | <--> | H+ | A-
Initial | 0.05 |  | 0 | 0
Change | .05 - x |  | 0 + x | 0 + x
End | .05 - 9.5e-4 |  | 0.00095 | 0.00095
The reality of the situation is it may be something like the following.
 | HA | <--> | H+ | A-
Initial | 0 |  | 0.07 | 0.02
Change
End
I still want the table to auto solve and fill out the " # -/+ x" in the change row and the numbers in the End.
For example I was trying to write the following sentence as a if/then clause but have been failing…
If HA is greather than H+ and A- then the equalibrium goes to the right. The original table is an example of it.
 | HA | <--> | H+ | A-
Initial | 0.05 |  | 0 | 0
Change | .05 - x |  | 0 + x | 0 + x
End | .05 - 9.5e-4 |  | 0.00095 | 0.00095
If HA is smaller than H+ and A- then the equalibrium is reversed and goes to the left.
 | HA | <--> | H+ | A-
Initial | 0 |  | 0.07 | 0.02
Change | 0 + x |  | .07 - x | .02 - x
End
This causes the equation [NAME] (H+x)( Ac+x)/(HA-x) to be incorrect.
The real equation is this.
Ka = [Products]/[Reactants]
Right side = products, left = reactants.
The new eqeuation with the numbers above would be Ka = ((.07-x)(.02-x))/x
So I need a way to make my table recognize that and do these actions automatically.