solving with a formula continuously without goal seek nor solver

Hermat

New Member
Joined
Nov 14, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
AB
1NT8781390.17
2W-2
3B1
4s-2.37
5
6NTx8781390.17
7Wx-2
8Bx1
9sx-2.37
10
11-2
Sheet1
Cell Formulas
RangeFormula
B1,B6B1=W*(0.88*(1-EXP(-0.1*B))*(1-EXP(-7.5*s))-1/B-0.5*s/SQRT(B))
B9B9=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))
A11A11=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
NameRefers ToCells
B=Sheet1!$B$3B6, B1
Bx=Sheet1!$B$8B9, A11
NTx=Sheet1!$B$6B9, A11
s=Sheet1!$B$4B6, B1
W=Sheet1!$B$2B6, B1
Wx=Sheet1!$B$7B9, 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!
 
Upvote 0
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
AB
1NT8781390.17
2W-2
3B1
4s-2.37
5
6NTx8781390.17
7Wx-2
8Bx1
9sx-2.37
10
11-2
Sheet1
Cell Formulas
RangeFormula
B1,B6B1=W*(0.88*(1-EXP(-0.1*B))*(1-EXP(-7.5*s))-1/B-0.5*s/SQRT(B))
B9B9=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))
A11A11=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
NameRefers ToCells
B=Sheet1!$B$3B6, B1
Bx=Sheet1!$B$8B9, A11
NTx=Sheet1!$B$6B9, A11
s=Sheet1!$B$4B6, B1
W=Sheet1!$B$2B6, B1
Wx=Sheet1!$B$7B9, 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.
 
Upvote 0

Forum statistics

Threads
1,224,965
Messages
6,182,041
Members
453,083
Latest member
Sayed Kassem

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top