Hi, I have a problem with a formula in this file.
I need D2 to be taken automatically as D3 (=SUM(C3*G3)) so it should be =SUM(C2*G2) but it causes me error. I placed in D2 the digit 3 for trying. What is wrong?
Thanks!
Hi, I have a problem with a formula in this file.
I need D2 to be taken automatically as D3 (=SUM(C3*G3)) so it should be =SUM(C2*G2) but it causes me error. I placed in D2 the digit 3 for trying. What is wrong?
Thanks!
Last edited by dandi10; 07-14-2019 at 11:23 AM.
welcome to the forum
For future reference, please take a moment to read the forum rules and use thread titles that are descriptive of your problem - not what you think the answer might be. (think google search terms?).
Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?
Repeating Pepe's post above, I would suggest that you edit both your thread title and your question to something with more meaning.
You have told us what the problem is, but not what is expected. Your formulas are a mess, so give us no indication as to what the correct results should be.
The formulas in D3 and C3 refer to each other creating a circular reference with an impossible result, making your question impossible to answer.
Hi, thank you. I didn't know what to post in the title since it is just an error in formula, will try to think about something better and update.
I know this is a complicated formula (this is why I like it), I added a new sheet3 where you can see it is not impossible. What I need to have
This is the scenario, I type a number in A2 and then G2 takes the value frl K,L columns. Then I insert a value on B2 and just C & D needs to be calculated (as it works in sheet3). Make sense?
Have you tried re-entering the formulas?
I can't test them properly because they refer to external data.
I'm not sure why one would prefer complicated formulas over simple formulas. I know that I, personally, prefer simple formulas because they are easier to build, edit, and (as we are doing here) debug.I know this is a complicated formula (this is why I like it)
As one who commonly uses these iterative algorithms, I see how it can work. However, I also see how these algorithms sometimes don't work. In this case, you have a simple "successive approximations" algorithm. Successive approximations works wells sometimes, and other times it just diverges, which is what appears to be happening in sheet1 row 3. Debugging steps:
1) I need to break the circular reference, so I can see what is happening with each iteration. I enter 75 into M3 as a starting point for the iteration. Then I edit the formulas in C3 and D3 and replace the C3 reference with M3. C3 returns 532 and D3 returns 450. I enter 532 into M3 and C3 goes to 3284. Continue and I see that the algorithm is not converging on a simple result, but is growing without bound. At some point, the algorithm will overflow and an error will be returned.
2) The examples on sheet3 seem to be working, so I repeat the test there. Enter 69 into M3, edit the formulas in C3 and D3 to refer to M3. With 69 in M3, C3 is 85.5. With 85.5 in M3, C3 is 88.0. 88.0 in M3 yields 88.3. Continue and I see the iteration converging on the solution.
Why does the iteration converge on sheet3, but diverge on sheet1? I'm not sure, but I suspect it has to do with the value in G3. On sheet3, the value in G3 is between 0 and 1. On sheet1, G3 is greater than 1. I suspect that this algorithm will only converge when G3 is between 0 and 1.
If I follow the formula correctly, the algebra is not that complicated, and a closed form solution can be found by spending a few minutes with the algebra of the problem. It looks like the formula is basically B3=SUM(constants)+a*B3+b*B3, which can readily be solved for B3. Start by moving all the B3 terms to the left side B3-a*B3-b*B3=SUM(constants), then finish solving for B3. That would be more reliable that a successive approximations iterative algorithm.
Originally Posted by shg
Hi, only C3 had external data, file updated. Thanks.
Hey, thank you. Someone helped my out a few years ago with this complicated formula (sheet3), it worked for few years and now I need something more automatic for the business so I'm just trying to upgrade it as it is, if it can be simpler I'm happy too
Sorry I didn't understand that much your solution, if you can help updating the file will be appreciated!
Hey, thank you. Someone helped my out a few years ago with this complicated formula (sheet3), it worked for few years and now I need something more automatic for the business so I'm just trying to upgrade it as it is, if it can be simpler I'm happy too
Sorry I didn't understand that much your solution, if you can help updating the file will be appreciated!
Last edited by AliGW; 07-15-2019 at 01:22 AM. Reason: Please don't quote unnecessarily!
I won't be able to upload anyting for a while. Until then, I would suggest that you consider the algebraic solution I proposed. Start with the basic formula:
C3=B3+lookup()+7.95+C3*0.023+0.3+C3*G3-F3+E3
move all of the C3's to the same side:
C3-0.023*C3-G3*C3=B3+lookup()+7.95+0.3-F3+E3
and continue solving for C3. you should end up with a straightforward formula that you can put into C3.
If you need a refresher on how to do these kind of algebra problems: https://www.purplemath.com/modules/solvelin3.htm
Thank you MrShorty but still no luck with it
Last edited by AliGW; 07-15-2019 at 01:23 AM. Reason: Please don't quote unnecessarily!
At this point it is all algebra, no Excel programming, yet. What part of solving this kind of algebra problem are you having trouble with.
start: C3=B3+lookup()+7.95+C3*0.023+0.3+C3*G3-F3+E3
move all the C3 containing terms to the left side: C3-0.023*C3-G3*C3=B3+lookup()+7.95+0.3-F3+E3
Combine the C3 terms on the left side: (1.023-G3)*C3=B3+lookup()+7.95+0.3-F3+E3
Divide by (1.023-G3) to leave C3 alone on the left side: C3=(B3+lookup()+7.95+0.3-F3+E3)/(1.023-G3)
Double check my algebra and make sure I did it all correctly. Once you are convinced that the algebra is good, then it should be as simple as entering that formula into C3.
I just don't get what do you mean to move all the C3 containing terms to the left side and why lookup is empty...
Last edited by AliGW; 07-15-2019 at 01:23 AM. Reason: Please don't quote unnecessarily!
Administrative Note:
Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!
For normal conversational replies, try using the QUICK REPLY box below.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
OK I'm new in forums
What exactly is your formula supposed to be doing and in what way is it incorrect? Tell us what results you are expecting and why.
lookup() is empty because I am too lazy to type out all of the references inside each time. Replace the empty lookup() placeholder with the full LOOKUP() function as you have it in your current C3 formula.
Moving all of the unknowns to one side is usually one of the first steps to solving any algebra problem. If I am solving 3x=6+x, then my first step is to move all of the x's to one side 3x-x=6+x-x which makes 2x=6 and x=3. Again, if you need to refresh your algebra skill, I recommend some time with an algebra tutorial: https://www.purplemath.com/modules/solvelin3.htm
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks