Having difficulty calculating a chemistry problem with quadratic formula uncertain error

**Chemistry in excel** · 11-22-2021, 05:54 PM

Hello everyone, I am new to this page, so I hope I can find some friendly people to help me.

I am not sure if the error I am experiencing is a chemistry, math, formula or process error so if there is anyone good at chemistry and excel I desperately need your help because I have been trying for 4 days to figure this out.

In the attached sheet I am trying to use a quadratic formula to determine the pH of in this example Sodium Hydroxide.
Yes I know that you can do the simplified version however the simplified version does not work very accurately for PolyProtic Acids and bases.

This is just a small section of a much larger project I am working on. and Yes I know that some text books state that the PKA of sodium hydroxide is 15.7 however more recent books have stated a pka value of 13.8 when the water disassociation is factored in so 13.8 is the value I am using in this example.

So I got the formula to work in the top example to find the H+ but I can not for the life of me to get it to work for the OH- in the bottom formula which is an exact duplicate of the top one but instead of using Pka/Ka it uses Pkb/Kb

So here is what I did
The pk value of NaOH is 13.8
NaOH is a base, so the 13.8 SHOULD be the PKB value
so to get the PKA value it would be 14-13.8 which = a PKA of .2 (G3)

Now to get the KA values it is the =10^-(of the PKA) which in this case gives me 0.630957344 (J3)

Then in S5 you take the total moles (-.19g*40 (molar mass)) which equals -0.002997047 (s5)

So now you have A, B, and C to solve for X in the quad formula which in the first example is +x1=0.004714769 (V3) or -x2=0.635672114 (V4)

Now to calculate the pH it is the -log of the H+ value (V3), or (V4), in our case it is (V3) which gives us a pH of 2.32653955 in cell (X3) which is correct.

Now to verify the second formula, if we take 14 - 2.32653955 = a pOH of 11.67346045 now to figure out the OH- Value, we take the
11.67346045 and go 10^-11.67346045 we should get 2.12099E-12 in the S13 cell

Now if we run the same formula, but use the PKB and PKB values, we should see in cell (S7)the 2.12099E-12, but we dont, we get -7.52824E-17 at (cell X11) a pH of 8.06 instead of 11.67346045

Now I am 100% sure that the quad formula is correct and functional, and I am sure that the values in C11, C12 are correct, and I am pretty sure the formulas in the PKA/KA, PKB/KB are correct, but I can not figure out for the life of me WHY this is not returning the correct values in the second formula.

I have spent 4 days trying to trouble shoot this, to no avail, what am I missing???

**curiouscat408** · 11-22-2021, 06:57 PM

This seems to be more of a chemistry question than an Excel question. And IIRC, there is bona fide chemist who participates in this forum. (Or is it "the other" forum?) Hopefully, he will comment on your formula from a chemistry perspective.

I notice that you have an error in the "negative x" quadratic equation. The correct quadratic equation is shown in the attached image.

So the formula in V12 (and similarly in V4) should be:

=(-S12-SQRT(S12^2-4*S11*S13))/(2*S11)

Arguably, that results in a #NUM error when you calculate the LOG in X12 (X4). But that might simply mean that the "negative x" solution is not applicable. It is not uncommon for one quadratic solution to not fit the "real world" requirements.

That said, apparently it is the "positive x" solution that you are concerned about, not the "negative x".

Unfortunately, I do not see any other "obvious" errors in the quadratic formulas themselves.

PS.... I do wonder why the formula in S12 is effectively =SUM(P10:P15) instead of simply =P10, which would be similar to =J3 in P4. But since P11:P15 are empty (ignored or treated like zero), it makes no difference.

PPS.... I have not vetted your Excel formula against your English description to be sure that they agree in all respects. It's a lot of "Greek" to plow through. I would have preferred a link (URL) to a site that provides the chemistry formula(s) in algebraic form.

**mehmetcik** · 11-22-2021, 07:13 PM

The Quadratic equations are correct. If you have errors it in calculating A, B and C.

**Chemistry in excel** · 11-22-2021, 07:37 PM

Duly noted Curiouscat, and thank you for catching that in V12, the formula was also incorrect in v4 as well, and as you stated it creates a num error in those 2 cells which is not of importance as the values in V3, and V11 are the ones we are after anyways.

Per your enquiry S12, it was a remnant of a larger table where all the Pka/PKb, or the Ka/Kb values had to be summed up if using it for a polyprotic acids and bases, ie, Pka1, Pka2, Pka3.... Ka1, Ka2, Ka3..... or PKb1, Pkb2, pkb3..... Kb1, Kb2, Kb3.... those values had to be summed up first to give you the B value, but since we are using a monoprotic, we only needed Pka1/Ka1, Pkb1/Kb1... however thanks for the catch and ive fixed it on the new table attached to this post.

Per your request of a link, since I am new, I apparently cant post links, so if you go to youtube and search this title, Polyprotic Acid Base Equilibria Problems, pH Calculations Given Ka1, Ka2 & Ka3 - Ice Tables
you will find the video that uses the quad in a polyprotic scenario, at time stamp of 9:00 it starts to go into the polyprotic acids and the PKA values using a quadratic formula.
I have been able to follow this videos guidance to make the first formula to work without any problem, especially in the more advanced form of this sheet, I have just simplified the work in the attached sheet to use a mono protic to make it easier for people to follow what the problem is.

**MrShorty** · 11-22-2021, 08:27 PM

I'm struggling to see the errors, because, qualitatively, I see your top "correct" calculation claiming that by adding a very small amount of NaOH to water (0.00475 moles per liter), you will end up with a pH of less than 3. That just seems wrong to me. A weak solution of NaOH should have a pH greater than 7.

I wonder if there is some confusion as to exactly what the pK=13.8 really means. pKa of the conjugate acid for OH- (which is H2O) is about 14. Is it possible that the 13.8 you cite is really the pKa of the conjugate acid of NaOH -- namely NaH2O+?

I may need to pull out my old chemistry text(s) and refresh my memory on how to solve acid-base equilibrium problems, but something seems wrong with this methodology if it is predicting a pH of 2.3 by adding a small amount of NaOH to water.

**Chemistry in excel** · 11-22-2021, 08:57 PM

Mr. Shorty
in the first example, we are solving for the acid value H+ in the NaOH. If there was no H+ (Acid) the pH would be 14.
Since the PK value is 13.8, we have .20 of H+ which when we solve it (being an acid) it gives us the pH of 2.32653955

Now there is also OH- which gives us the accustomed pH of 11.67346045.

Alternatively to demonstrate this if we take a pH of 14 (NO H+) and subtract 2.32653955 (the acid H+) we get the same answer of 11.67346045.

Now when the pH is greater than 7 you are illustrating that the concentration of OH is greater than H+, When it is less than 7 there is more H+ than OH-, at a pH of 7 it is 50/50

So as you can see by measuring the H+ available in the PK 13.8 of NaOH, we can determine without the quad the POH of the NaOH which would be the 11.67...

However in the second formula it SHOULD give you the final pH of 11.67, based on the values, however I can not see where I made the mistake, One person said it was in the molarity in (C14) of the second equation, but I cant see how that is causing the problem.

**MrShorty** · 11-22-2021, 11:13 PM

If there was no H+ (Acid) the pH would be 14.

This makes sense (with the caveat that we never truly have [H+]=0). I think we need to remember that the base (NaOH) must be sufficiently concentrated in order to get to the point where pH is 14. Remember that pH is a function of both pKa AND concentration.

Since the PK value is 13.8, we have .20 of H+ which when we solve it (being an acid) it gives us the pH of 2.32653955

Even if this is what your solution of the math equation tells you, this makes no sense to me. I would not expect to add a small amount of NaOH to water and get a strong acid solution.

If I start with neutral water ([H+]=[OH-]=1E-7 Kw=[H+]*[OH-]=1E-14). If I then add NaOH so that [OH-]=0.0475, Kw cannot change, so I solve for [H+] -- [H+]=1E-14/0.0475~2E-13. It seems unreasonable to me to get [H+]=0.0471 from this. I realize that your interest is extending into polyprotic acids/bases where you will encounter more complex math, but it should still correctly handle the simple case of adding NaOH to water.

**Chemistry in excel** · 11-22-2021, 11:51 PM

Mr.shorty think of it this way, 11.67 add 2.33 = what? 14 right? Which means there is ZERO H+ left.

Another way to look at it is OH- accounts for close to 99.8% of the total value, and the H+ will account for a small amt of about .2%, so since the NaOH i.e. is much much larger, it will negate the small amt of H+.

Alternatively think of it this way, if the pk value was 14, then the ph would be 14, but since the pka is 13.8, and when solved, equates to a pH of 11.67, this means to lower the ph from 14 to 11.67, you would have to add an acid (H+) to the solution to obtain a pH of 11.67. The higher the H+ the lower the pH becomes.

Now i can assure you that the first formula and resulting pH is 100% accurate, as thia has been checked by 3 pH calculators, and 2 chemistry professors when i originally wrote it.

What i didnt have time to ask before the pandemic hit, was how this calculates to finding the OH- values with the pkB/kb. I.e.OH-

This is what i am trying to figure out. And as has been pointed out already, 2 formulas for -X were incorrect and have been fixed, which does not affect the +X that were needed.

It appears to be a molarity issue on the second formula unless i have made a mistake somewhere else in the second formula, hence why im asking here.

**curiouscat408** · 11-23-2021, 05:03 AM

Originally Posted by Chemistry in excel

since I am new, I apparently cant post links

I think MrShorty has you covered, chemistry-wise.

But FYI, for future note, you can always "spell out" URLs. For example, the link to this discussion is:

www dot excelforum dot com /excel-general/1364572-having-difficulty-calculating-a-chemistry-problem-with-quadratic-formula-uncertain-error.html

Also, simply putting [noparse] and [/noparse] around a bona fide URL might work, even for you. For example:

https://www.excelforum.com/excel-general/1364572-having-difficulty-calculating-a-chemistry-problem-with-quadratic-formula-uncertain-error.html

**MrShorty** · 11-23-2021, 01:16 PM

I watched the video you mentioned (assuming I found it, if not, it should have been the same thinking and math). I think I understand what we are misunderstanding, and I, therefore, think that both calculations are correct.

The top calculation represents an "experiment" where we add a known amount of an acid to water. We enter the amounts and the pKa of the conjugate acid, and it calculates the resulting pH. Of course, NaOH is the opposite of an acid, so the top calculation really doesn't apply to the experiment being considered. As near as I can tell, this calculation is correct. I can put 0.1 moles of acetic acid with a pKa of about 4.5 and the top calculates a pH of 2.8 (value according to below link is 2.9).
The bottom calculation represents an "experiment" where we add a known amount of base to water. We enter the amounts for the pKa of the conjugate acid, from which the sheet calculates the corresponding pKb. The sheet then calculates pOH (pOH+pH=14). Again this looks correct. I can put 0.1 moles of sodium acetate (pKa is still about 4.5 for a pKb of 9.5), and the calculator says that pOH is 5.3 (pH of 8.7 linked table says 8.4).

As a further test, put 0.1 M HCN (pKa is about 9.2) into the top calculator and 0.1 M NaCN (pKb=4.8). For the acid case, it calculates a pH of 5.1 (linked table gives 5.1), and, for the conjugate base, it calculates a pOH 2.9 (pH of 11.1 linked table also says 11.1).

Please note from the table of pH values in my link that the pH of 0.1 M conjugate acid and the pH of 0.1 M conjugate base do not add up to 14! It is true that pKa+pKb=14 (and pH+pOH=14), but that does not mean that the pH of the solutions of conjugate acid and base will add up to 14. I think the mistake we are making is to expect that X3+X11 should be 14. It will likely not be.

Of course, the intention is to be able to extend to polyprotic acids/bases where you have multiple equilibria to consider. I haven't looked that far, but the calculator seems to work correctly for the monoprotic cases.

Edit to add link: https://chemed.chem.purdue.edu/gench...1/conjugat.php table of pH at 0.1 M is at the bottom of the page.

**Chemistry in excel** · 11-23-2021, 02:34 PM

Mr. Shorty I found some labels that were mixed up, I have corrected them on the attached sheet, what the 2.32 was not pH, rather the POH value, my labels were mixed up.

Please help me if you will to solve the second equation, it is still causing problems for me. I have played with the molarity last night, and got a molarity in the second formula to work to give the required pH of 11.67, but I have no clue where this molarity is coming from, as it is certainly not from the weight of substance / by the molar mass (.190 / 40)

**Chemistry in excel** · 11-23-2021, 02:43 PM

This pH calculator says .19g of NaOH in 1L of water should be a pH of 11.67.

I have been able to calculate the OH- in formula 1 but not the H+ (pH 11.67) in formula 2.

The CurtiPot calculator with the same values states a ph of 11.67532 with the OH of 2.32468 so I am not sure that both formulas can be correct as they are giving one correct and one incorrect number.

**MrShorty** · 11-23-2021, 10:52 PM

It appears that you are essentially using the same equation/math/logic/formula for both the pOH and pH. I agree that the pOH "top" calculation looks right. The bottom equation, I'm not sure on. How are you deriving the quadratic polynomial?

1) The bottom approach is focused on pKa, so what are you using for your conjugate acid "dissociation" chemical equation? I would expect the standard dissociation of water H2O->H+ + OH-, but you have not specified this explicitly.
2) Then, using the strategy from the video, what are you putting in your ICE table (what are you using for initial concentration, change, and final equilibrium)?
3) Then, what exactly are you using for equation for Ka? Obviously, if you are using the dissociation of water, then Ka=[H+]*[OH-]=14 (or 13.8) where you substitute the appropriate expressions from the ICE table for [H+] and [OH-].

When I worked through the problem using 13.8 for Ka of water, I ended up with a pH of 11.5, which is pretty close to your calculators 11.67. At this point, I wonder if we have understood the lower problem correctly, and if our misunderstanding has caused us to program the lower block incorrectly.

All of which is specific to the NaOH system that we are talking about. I recognize that the intention is to extend to more generic problems (including polyprotic acids/bases)

**Chemistry in excel** · 11-24-2021, 03:11 PM

Hello Mr Shorty

I have attached a revised spread sheet, to better illustrate the problem I am having. The PKA and PKB values are strait off the chembuddy ph calculator for each substance.
(chembuddy.com/?left=BATE&right=example_of_pH_calculation)

I have done this to avoid the confusion with the NaOH and the pka of 13.8 and the pkb of .2 and total 14

I got formula 1 and 3 to work correctly as they should, however formula 2 and 4 will still not lead to the correct answer based on the values of Chem Buddy.

re. the quad, A is 1 and refers to the volume, B refers to the kA or the kB value, and C is (-moles*the KA/KB)

and it seems to work correctly for the top formula on the new sheet, but still not for the bottom even when I use something like HCL, which has a vastly different pKa and pKb values compared to NaOH...

The problem is I need to get this as accurate as a +/- 0.005 pH variation can shift the final amount of say sodium substantially. As this will be used for food, Wine and beer, a +/- 2.5mg per X volume will mean the difference on the label of say 60mg vs 65mg of sodium... Hence I need to get this as close to 0.0001 accuracy as possible.

I love that you understand my desire to extend this to polies! so many dont get that part and ask why even bother with the Quad...

BTW I hold no one accountable here, this calculator is theoretical, and a guide to the expected real world values, which in practice will vary on purity, moisture, and contaminants in the water sources, So, Yes this will eventually be used, but its theoretical values, that will have to be adjusted accordingly in the real world.

**MrShorty** · 11-24-2021, 06:39 PM

re. the quad, A is 1 and refers to the volume, B refers to the kA or the kB value, and C is (-moles*the KA/KB)

I understand that, but I am not sure the derivation is correct -- especially for the 2 and 4 case. How did you derive the quadratic for these cases? As it applies to the 1 and 3 cases, the derivation looks like:

1) HA -> H+ + A- monoprotic acid case NaOH->Na+ + OH- for the NaOH case
2) assume all H+ (for acid case) or OH- (for NaOH case) comes from the acid or base added and ignore the contribution of water (H2O->H+ + OH-)
3) From that, we can derive (see ICE tables in instructional video) the quadratic equation 0=x^2+K*x-K*F, where x is the H+ or OH- concentration, K is Ka or Kb as appropriate, and F is the formal (or molar, if you prefer) concentration of the original acid/base added. Solve the quadratic for x and we are done.

As you note in your latest file, once we have [H+] or [OH-], we then usually use the dissociation of water (H2O->H+ + OH- Kw=[H+]*[OH-]=1E-14) to solve for the other. You state that you are trying to avoid this approach, but it is not clear to me how you intend to calculate the other ion from the first without using Kw. I suspect that the chemistry behind the calculations for 2 and 4 is wrong, so I am trying to understand how you derived the quadratics for approaches 2 and 4. I suspect that, once we get the chemistry right, then the programming will be clearer.

**Chemistry in excel** · 11-24-2021, 09:08 PM

Hey Mr. Shorty

Here ia my logic. It may be in error, but here it is.

In lets say .5 moles of a compound. In that .5 moles we have H+ (represented by pka/ka) and OH- (represented by pka/pkb)

So since the H+ is controlled by thr pka/ka values, and in formula 1 and 3 gives us the proper values.

If we use the same amts and use the pkb/kb values we chould get the correct values for formula 2 and 4.

As the pkb/ kb values control the amt of OH-

All the formulas in 123&4 are all the same with the exception of referencing pka/ka in 1 and 3, and pkb/kb in 2 and 4

The pka valuea came from chem buddy, (esentially i am trying to duplicate that program in excel as it is simple to use for the average jo. And if in excel would fit into a much larger calculator i am working on.

I did not use an ice table as i saw in the video for H2SO4 a way they did it without it by simply taking the grams/ volume,
Then using the pka for the B value, and in C mutiplying the moles by the pka value.

This is shoved through the quad to give us the results.

So if it is (H+) ×(A-)/ HA. We should be able to use the same formula to find either value in my thoughts

**Chemistry in excel** · 11-24-2021, 09:15 PM

According to the henderson hasselbach equation
Pka is a measure of A-/HA

And therefore in my mind Pkb should be H+/HA

**Chemistry in excel** · 11-24-2021, 09:18 PM

As is demonstrated here in this picture

**MrShorty** · 11-25-2021, 01:03 AM

If we use the same amts and use the pkb/kb values we chould get the correct values for formula 2 and 4.

It seems like it should, but are you certain of the derivation of formulas 2 and 4? I'm not sure that the 2 quadratic is correctly expressed as a=1, b=Kb, c=-F*Kb (essentially paralleling formula 1). I would expect a different quadratic, though I haven't gone to the effort to derive it. How are you deriving the quadratic for formula 2 using Kb (which probably starts with exactly what are you using for your conjugate base chemical equation).

Having difficulty calculating a chemistry problem with quadratic formula uncertain error

LinkBack

Thread Tools

Rate This Thread

Display

Having difficulty calculating a chemistry problem with quadratic formula uncertain error

Re: Having difficulty calculating a chemistry problem with quadratic formula uncertain err

Re: Having difficulty calculating a chemistry problem with quadratic formula uncertain err

Re: Having difficulty calculating a chemistry problem with quadratic formula uncertain err

Re: Having difficulty calculating a chemistry problem with quadratic formula uncertain err

Re: Having difficulty calculating a chemistry problem with quadratic formula uncertain err

Re: Having difficulty calculating a chemistry problem with quadratic formula uncertain err

Re: Having difficulty calculating a chemistry problem with quadratic formula uncertain err

Re: Having difficulty calculating a chemistry problem with quadratic formula uncertain err

Re: Having difficulty calculating a chemistry problem with quadratic formula uncertain err

Re: Having difficulty calculating a chemistry problem with quadratic formula uncertain err

Re: Having difficulty calculating a chemistry problem with quadratic formula uncertain err

Re: Having difficulty calculating a chemistry problem with quadratic formula uncertain err

Re: Having difficulty calculating a chemistry problem with quadratic formula uncertain err

Re: Having difficulty calculating a chemistry problem with quadratic formula uncertain err

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] I am having difficulty in calculating the Weighted Average Price

Quadratic Formula help in vba

Scatter graph for chemistry

Formula Error/Problem In Calculating Monthly Payments

Difficulty calculating age in the format YYYY/MM/DD

chemistry lab help graph

Uncertain what function to use

Bookmarks

Bookmarks

Posting Permissions