UPDATE 2 corrected, 2 very critical function formulas that I need to reverse PLEASE HELP

[born2dive00]

Hello everyone and happy Thanksgiving

I have 2 very critical function formulas that I need to work in reverse.

Currently the manually entered values in B4, B8, and the Automatically generated value in B6, will give me the missing result in S4 This way works fine moving forward.

But What I need this to do is have the value in S4 go through the formula to = the auto generated result value missing in B6

I have UPDATED and attached the excel sheet with the current formulas The given/ known values are in Sky blue (B4,B8, S4)
The dark green value B8 is what I need to solve for with a given value in S4 is entered.

Please Help and Thank you for all your help.

Brad

[MrShorty]

How much of this is math/trig and how much is really Excel? Assuming I understand (I was confused by some of it), In all 4 cases, you have one equation and 3 unkowns. Such problems do not yield unique solutions. Given any 3 of the values (Fn, Gn, Hn, Jn) it sould be relatively easy to solve for the 4th value (inverse cosine can sometimes get tricky -- especially knowing which quadrant the solution should be in).

Formula 1: I note that H3 is not even used in this formula, so I don't know how to find H3 by any method. The inverse of the arccosine (ACOS()) function is the cosine (COS()) function, so F3/SQRT(G3^3)=COS(J3). From there it should be simply algebra to solve for F3 or G3 (as long as Excel doesn't have trouble with the cubed root).

Formula 2: Given any 3 of the values, this should be straightforward. Again, when solving for G6, you will need to know enough about the problem to make sure the angle returned by the ACOS() function is in the right quadrant. ACOS(), by convention, returns an angle in the first quadrant, so you need to know when it should return an angle in the 4th quadrant. I note that, in your sample file, you claim that you need to find F3:H3 using J6, but J6 does not even refer to F3:H3. I wonder if this was a typo, and you intended this to say F6:H6 (same for formulas 3 and 4).

Formulas 3 and 4: I don't know that they are always equivalent, but inverting those two functions should be basically the same. As with formula 2, it should be straightforward, except for knowing which quadrant the G angle should be, when you need to solve for G.

As noted, the inverse functions seem fairly straightforward. The problems also seem underspecified, because you cannot solve for Fn:Hn given only Jn. You can solve for any 1 of the value given the other 3. The exact inverse function will depend on which variable is the unkown. Perhaps part of your programming challenge here is to have Excel identify which value is the unkown (which usually ends up being a nested IF() testing whatever toggle you decide to use to tell the spreadsheet which value it should solve for)?

With those questions and concerns, I don't yet have a specific suggestion. Can you elaborate a little on your exact requirements? What specific part of programming this into Excel is giving you trouble?

[born2dive00]

Hello Mr Shorty
I have updated the Excel sheet, and the original post, Thank you for your help. I also have given the complete formula and results going the right normal way. The updated Spread sheet also shows the cos information you asked about.

Any help you can give me would be great. I have been working on this for at least 3 days now and am nearly bald. Thanks again.

Anyone wishing to have a very accurate concentration to PH converter, and eventually a PH converter to a concentration please contact me when this is done.

Thanks again for all who are willing to help


[QUOTE=MrShorty;5014827]How much of this is math/trig and how much is really Excel? Assuming I understand (I was confused by some of it), In all 4 cases, you have one equation and 3 unkowns. Such problems do not yield unique solutions. Given any 3 of the values (Fn, Gn, Hn, Jn) it sould be relatively easy to solve for the 4th value (inverse cosine can sometimes get tricky -- especially knowing which quadrant the solution should be in).

[born2dive00]

Hello Mr Shorty

Specifically how do I reverse the action of the 2 formulas in red

Specifically how to enter the reverse of the SQRT in excel,
Specifically how to enter the reverse of the COS
and last specifically how to reverse ACOS

there is additional data in the new spread sheet that are the forward answers.

Thanks for your help

[MrShorty]

Specifically how to enter the reverse of the SQRT in excel,

From your basic algebra class (https://www.purplemath.com/modules/radicals.htm ) If y=SQRT(x) then x=y^2. The inverse of the SQRT() function is the "square" function (raise to the 2nd power).

Specifically how to enter the reverse of the COS and last specifically how to reverse ACOS

Basic trig ratio definitions say that cosine and arccosine are inverse functions (kind of).
https://www.purplemath.com/modules/invratio.htm
http://www.mathwords.com/c/cosine_inverse.htm
So if y=COS(x) the x=ACOS(y)

Those are the specific answer to those specific questions. I haven't had time to look at the full calculation, so I'm not sure that you can derive 3 values from the single value in O4 or not. I don't see a simple inverse for O4. O4 is a single function with three input variables. I don't see a ready way to get J4, J8, AND F4 from a single value in O4. Perhaps after I look at it more, I will see something, but one usually cannot derive 3 unkowns from a single equation.

[born2dive00]

Hello Mr Shorty

I understand your point about 1 to 3. BUT I found some good news. I did not realize that The values in B4, B8, as well as the value of S4 are given in the larger spread sheet so we dont have to find them. (they are constants or manually entered in

The ONLY thing I need to find is the Missing value in B6.

So knowing the given values in B4, B8, and S4 is it now possible to solve for the missing value in B6?

I have updated the given Values in sky Blue and attached the new spreadsheet.

Thank you so much for your help with this, as I said I am no math math expert.

Sincerely Brad

Those are the specific answer to those specific questions. I haven't had time to look at the full calculation, so I'm not sure that you can derive 3 values from the single value in O4 or not. I don't see a simple inverse for O4. O4 is a single function with three input variables. I don't see a ready way to get J4, J8, AND F4 from a single value in O4. Perhaps after I look at it more, I will see something, but one usually cannot derive 3 unkowns from a single equation.

[Bo_Ry]

This is more like math question.
B4 =F4

S4 =(-2*SQRT($J$4)*COS(($J$8+2*PI())/3)-$F$4/3)

$F$4/3 =-2*SQRT($J$4)*COS(($J$8+2*PI())/3)-S4

$F$4 =(-2*SQRT($J$4)*COS(($J$8+2*PI())/3)-S4)*3

B4 =(-2*SQRT($J$4)*COS(($J$8+2*PI())/3)-S4)*3

[born2dive00]

Here is the entire spreadsheet the whole thing that I am trying to get to work. The parts in red are the non functioning parts that are in the revised complete formula.

The value in Pka1 will tell me the concentration after it goes thru the formula that we were discussing. I have checked the tables, and the only thing I need is for S4 (pka1) to = B6 (concentration)

Your help is greatly appreciated with this. if no one can Help me is there a paid service that you recommend that could reverse this function at a reasonable price?

I haven't had time to look at the full calculation, so I'm not sure that you can derive 3 values from the single value in O4 or not. I don't see a simple inverse for O4. O4 is a single function with three input variables. I don't see a ready way to get J4, J8, AND F4 from a single value in O4. Perhaps after I look at it more, I will see something, but one usually cannot derive 3 unkowns from a single equation.

[MrShorty]

I'm a little confused. You say that B4 will be given as a constant or manually entered value, then you say that you need to find B4. It probably doesn't matter that I am confused, because I expect that you know what you meant.

There will be exceptions*, but the problem should be solvable as long as you have only one unknown (whichever is the unknown). As I indicated, I have not gone through the formulas in great detail to attempt to derive the inverse function.

How important is it to find that actual inverse function? In a case like this (where the algebra can be a bit challenging), the easiest way to get the inverse is to use a numeric algorithm (Newton Raphson or other algorithm like those programmed into Goal Seek and Solver). Where you have the forward calculation programmed into the spreadsheet, the easiest way to get the inverse might be to:
1) Enter the target value in S4 (rather than the formula =O4 that you currently have).
2) (Optional) I like to have my objective function target 0, so I would add a cell (T4) =S4-O4.
3) Solver is not going to like a merged cell as the decision variable, so unmerge B4 (or whichever cell is the actual unkown) (Most pwer users around forums like this say to never use merged cells anyway, so maybe now is a good time to break any habits of using merged cells).
4) Call Solver and tell it to:
4a) Set Target Cell T4 (or O4, if you opted not to use step 2).
4b) to a value of 0 (or the desired target value if you opted not to use step 2).
4c) By changing B4 (or whatever the unknown cell is).

If the initial value in B4 is close to the correct solution, Solver should converge on the correct solution. Unless you actually need the inverse function (because you need this to be perfectly automatic without the possible instabilities in a numeric solution), this might be the easiest way to find x at a given y value.

*trig functions can be tricky when finding the inverse. If nothing else, often the inverse "function" has multiple solutions (due to the periodic nature of trig functions). 1/2=cos(x) and solve for x (for example). x can be pi()/3 or -pi()/3 or 5*pi()/3 (and so on). In the forward function, there is only one possible value of y for any given value of x. IN the inverse, however, there could be many possible values of x for any given value of y. However you choose to approach this problem, you may need to account for this kind of possibility.

[born2dive00]

Sorry Mr Shorty

B6
is what I am looking for from S4 value

The value returned in B6 should have a resolution of +/- 0.0000005392 this will give me a +/- 1mg value in the final calculation. Ideally it would be the exact same as going forward.

I'm a little confused. You say that B4 will be given as a constant or manually entered value, then you say that you need to find B4. It probably doesn't matter that I am confused, because I expect that you know what you meant..

[born2dive00]

Hello Bo Ry Sorry I need the value of B6 derived from S4
could you please tell me which/ where to put these formulas, which cells do they replace?

This is more like math question.
B4 =F4

S4 =(-2*SQRT($J$4)*COS(($J$8+2*PI())/3)-$F$4/3)

$F$4/3 =-2*SQRT($J$4)*COS(($J$8+2*PI())/3)-S4

$F$4 =(-2*SQRT($J$4)*COS(($J$8+2*PI())/3)-S4)*3

B4 =(-2*SQRT($J$4)*COS(($J$8+2*PI())/3)-S4)*3

[MrShorty]

You haven't said whether or not you will accept a numeric solution. The same thing I suggested should work, in theory. Simply tell Solver to use B6 as the by changing cell. In practice, my copy of Solver doesn't always like working with small values like are in B6 (it terminates too early). In this case, I might add another cell (maybe C6) where I enter that exponent (currently -14.4665), then B6 becomes =10^C6. Then have my Solver model set target cell T4 to a value of 0 by changing C6.

That works for me, will that work for you?

[born2dive00]

Hello Mr.Shorty
The solution i would imagine needs to be numeric so that the calculation can be finished.

As for the solve program you speak of do you have a link to it? And last I can not have any special add ons or java scripts to this spread sheet. It must be a stand alone.

You haven't said whether or not you will accept a numeric solution. The same thing I suggested should work, in theory. Simply tell Solver to use B6 as the by changing cell. In practice, my copy of Solver doesn't always like working with small values like are in B6 (it terminates too early). In this case, I might add another cell (maybe C6) where I enter that exponent (currently -14.4665), then B6 becomes =10^C6. Then have my Solver model set target cell T4 to a value of 0 by changing C6.

That works for me, will that work for you?

[MrShorty]

Solver is built into Excel (though it is not always included in the installation by default). If Solver does not appear to be installed, follow instructions here to load/install Solver: https://support.office.com/en-us/art...c-e24772f078ca

[born2dive00]

Mr Shorty.

I found the solver, but when I do as you say, it says it wants a formula in B6.

Also I think I would need the true inverse, as this needs to generate an automatic value. I can not use any manual solvers.

Solver is built into Excel (though it is not always included in the installation by default). If Solver does not appear to be installed, follow instructions here to load/install Solver: https://support.office.com/en-us/art...c-e24772f078ca

[MrShorty]

How much of the algebra have you done on this? After quickly trying to piece the complete function together (so, assuming I got the algebra right), it looks to me like there is something like a y=x*cos(x) term, which cannot be explicitly inverted (unless I am forgetting something from my secondary math/trig days). In other words, I don't think there is any way to enter a function in B6 where B6=g(O4,B4,B8), so you will be required to use some kind of numerical algorithm.

By far the easiest way to program something like this is to use the built in Solver/Goal seek algorithms. They can be automated in VBA using a calculate event procedure (we have several examples around this forum, let us know if you want to use this kind of approach).

Or you can write your own numeric algorithm for solving this kind of problem. I have examples here (spreadsheet and VBA UDF) for writing your own Newton Raphson type algorithms. https://www.excelforum.com/tips-and-...ind-roots.html

Have I done the algebra correclty -- will this require a numerical algorithm to solve?

[born2dive00]

The part that we are speaking of, i did not write, a friend gave me this to use i dont know where he got it from. I am using this in yet a even larger spread sheet part of a much larger calculating program.

I do not know if the formula is algerbra, trig, calc, or superman flying arround. I an a social science teacher and writer by profession, advanced math is way beyond me in this setup.

How much do you think it would cost to get this to work?

How much of the algebra have you done on this? After quickly trying to piece the complete function together (so, assuming I got the algebra right), it looks to me like there is something like a y=x*cos(x) term, which cannot be explicitly inverted (unless I am forgetting something from my secondary math/trig days). In other words, I don't think there is any way to enter a function in B6 where B6=g(O4,B4,B8), so you will be required to use some kind of numerical algorithm.

By far the easiest way to program something like this is to use the built in Solver/Goal seek algorithms. They can be automated in VBA using a calculate event procedure (we have several examples around this forum, let us know if you want to use this kind of approach).

Or you can write your own numeric algorithm for solving this kind of problem. I have examples here (spreadsheet and VBA UDF) for writing your own Newton Raphson type algorithms. https://www.excelforum.com/tips-and-...ind-roots.html

Have I done the algebra correclty -- will this require a numerical algorithm to solve?

[MrShorty]

I don't have a good feel for what it should cost. If I were to continue, I would expect to spend about an hour or two getting a functional UDF. Then however long it takes for you to learn how to use it, which, if the UDF is well written and documented, should be no worse than learning to use one of Excel's built in functions.

Have you got any math or programming experience at all? Did my NR tutorial (especially the VBA UDF portion at the end) make any sense to you? I would expect to write a similar UDF for this (the only real difference would be using the function described in your spreadsheet rather than a polynomial).

[born2dive00]

Hello Mr shorty

As this will be eventually used by students that I teach Geography to, It has to be self standing and automatic, i.e. the student puts in the PH and the amount of fluid and the Kpa Value for the different chemicals. and it spits back the concentration. It can not have anything that I have to train the students how to use.

The only thing I need is for this to work in reverse. I am currently teaching environmental science (geography) overseas and my students need to calculate the concentration from the ph of contaminates in water. I.e. acid rain (sulphuric acid) at X ph = X concentration- mg/ml

This has been a long time problem and it is not really covered how to do this with out speaking to a chem teacher. And the problem is that the chem teachers are often put off by students requests on how to do this after the 50th time.... Then the teachers get stroppy with me for requesting this type of information.

Your help would be greatly appreciated to help create this formula. (the formula that i have been given, is from the chem teacher going from concentration to ph

If you can solve how to do this, I could pay you $50.00 If you can solve this for me. But as I said I need it to go from Ph to concentration.

I also PMed you

Please let me know

Sincerely
Brad

I don't have a good feel for what it should cost. If I were to continue, I would expect to spend about an hour or two getting a functional UDF. Then however long it takes for you to learn how to use it, which, if the UDF is well written and documented, should be no worse than learning to use one of Excel's built in functions.

Have you got any math or programming experience at all? Did my NR tutorial (especially the VBA UDF portion at the end) make any sense to you? I would expect to write a similar UDF for this (the only real difference would be using the function described in your spreadsheet rather than a polynomial).

[born2dive00]

Mr Shorty

Just as a heads up, this formula calculates for a lack of a better term ph curves of an acid or base, not unlike a titration curve.
I tried to make a table to cover all possibilities, but it consisted of over 10,000 X 1000 cells. and took something like 20mb which was way too big.

[born2dive00]

Mr Shorty

I think but i am not sure (as I am not a mathmatician) that this article explains the formula used. https://www.fmf.uni-lj.si/~vitrih/pa...cubic-rev2.pdf

I don't have a good feel for what it should cost. If I were to continue, I would expect to spend about an hour or two getting a functional UDF. Then however long it takes for you to learn how to use it, which, if the UDF is well written and documented, should be no worse than learning to use one of Excel's built in functions.

Have you got any math or programming experience at all? Did my NR tutorial (especially the VBA UDF portion at the end) make any sense to you? I would expect to write a similar UDF for this (the only real difference would be using the function described in your spreadsheet rather than a polynomial).