solving with a formula continuously without goal seek nor solver

[Hermat]

Hello,
I am desperate for help!
I need to calculate s on these equation:
NT=W(0.88*(1-exp(-0.1B))*(1-exp(-7.5s))-(1/B)-(0.5*s/SQR(B)))
As I need to calculate a huge amount of times going with solver or with goal seek does not seem like an option (even if run by a macro) as multiple calculations and charts will feed on the result.
Can you please help me out?
Regards,
Desperate for ideas

 

[Eric W]

Welcome to the MrExcel forum!

As you're aware, you can't solve for s in that equation and get a closed formula, which is where Solver or Goal Seek usually comes in. It is possible though to approximate Goal Seek within a formula, which might work for you. Consider:

Book2
	A	B
1	NT	8781390.17
2	W	-2
3	B	1
4	s	-2.37
5
6	NTx	8781390.17
7	Wx	-2
8	Bx	1
9	sx	-2.37
10
11	-2
Sheet1

Cell Formulas
Range	Formula
B1,B6	B1	=W*(0.88*(1-EXP(-0.1*B))*(1-EXP(-7.5*s))-1/B-0.5*s/SQRT(B))
B9	B9	=LET(ss,SEQUENCE(2001,,A11-1,0.001),a,ABS(Wx*(0.88*(1-EXP(-0.1*Bx))*(1-EXP(-7.5*ss))-1/Bx-0.5*ss/SQRT(Bx))-NTx),XLOOKUP(MIN(a),a,ss,,0))
A11	A11	=LET(ss,SEQUENCE(101,,-50),a,ABS(Wx*(0.88*(1-EXP(-0.1*Bx))*(1-EXP(-7.5*ss))-1/Bx-0.5*ss/SQRT(Bx))-NTx),XLOOKUP(MIN(a),a,ss,,0))

Named Ranges
Name	Refers To	Cells
B	=Sheet1!$B$3	B6, B1
Bx	=Sheet1!$B$8	B9, A11
NTx	=Sheet1!$B$6	B9, A11
s	=Sheet1!$B$4	B6, B1
W	=Sheet1!$B$2	B6, B1
Wx	=Sheet1!$B$7	B9, A11

The B1 formula is my attempt to replicate your equation. It takes the values from B2:B4 and calculates the result. You should check that to make sure I got it right. Assuming it is, then look at the A11 formula. It sets up an array from -50 to 50, and checks each result of the equation, using the B7:B8 values for W and B, and the array value for S. (Much more than +/-50 leads to size errors.) It then finds the closest match to the NT value in B6. Then in the B9 formula, it takes that value of s, and starting from s-1 goes in steps of .001 until s+1. Again, it looks for the closest match to B6 and returns the closest match.

Bottom line, if s is between +/-50 and you only want 3 decimals of precision, this should work for you. Just put in the values you want for B6:B8 and B9 will return s. Hope this helps!

 

[Hermat]

Welcome to the MrExcel forum!

As you're aware, you can't solve for s in that equation and get a closed formula, which is where Solver or Goal Seek usually comes in. It is possible though to approximate Goal Seek within a formula, which might work for you. Consider:

Book2
	A	B
1	NT	8781390.17
2	W	-2
3	B	1
4	s	-2.37
5
6	NTx	8781390.17
7	Wx	-2
8	Bx	1
9	sx	-2.37
10
11	-2
Sheet1

Cell Formulas
Range	Formula
B1,B6	B1	=W*(0.88*(1-EXP(-0.1*B))*(1-EXP(-7.5*s))-1/B-0.5*s/SQRT(B))
B9	B9	=LET(ss,SEQUENCE(2001,,A11-1,0.001),a,ABS(Wx*(0.88*(1-EXP(-0.1*Bx))*(1-EXP(-7.5*ss))-1/Bx-0.5*ss/SQRT(Bx))-NTx),XLOOKUP(MIN(a),a,ss,,0))
A11	A11	=LET(ss,SEQUENCE(101,,-50),a,ABS(Wx*(0.88*(1-EXP(-0.1*Bx))*(1-EXP(-7.5*ss))-1/Bx-0.5*ss/SQRT(Bx))-NTx),XLOOKUP(MIN(a),a,ss,,0))

Named Ranges
Name	Refers To	Cells
B	=Sheet1!$B$3	B6, B1
Bx	=Sheet1!$B$8	B9, A11
NTx	=Sheet1!$B$6	B9, A11
s	=Sheet1!$B$4	B6, B1
W	=Sheet1!$B$2	B6, B1
Wx	=Sheet1!$B$7	B9, A11

The B1 formula is my attempt to replicate your equation. It takes the values from B2:B4 and calculates the result. You should check that to make sure I got it right. Assuming it is, then look at the A11 formula. It sets up an array from -50 to 50, and checks each result of the equation, using the B7:B8 values for W and B, and the array value for S. (Much more than +/-50 leads to size errors.) It then finds the closest match to the NT value in B6. Then in the B9 formula, it takes that value of s, and starting from s-1 goes in steps of .001 until s+1. Again, it looks for the closest match to B6 and returns the closest match.

Bottom line, if s is between +/-50 and you only want 3 decimals of precision, this should work for you. Just put in the values you want for B6:B8 and B9 will return s. Hope this helps!

Thanks a million Eric!
What a genious idea! It will take me a bit to digest all your information.
I visit the forum often but as I always found answers. This time I didn't came across with an answer.