+ Reply to Thread
Results 1 to 17 of 17

Error when calculating formula with LOOKUP

  1. #1
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    145

    Error when calculating formula with LOOKUP

    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!
    Attached Files Attached Files
    Last edited by dandi10; 07-14-2019 at 11:23 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Error in formula

    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?

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Error in formula

    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.

  4. #4
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    145

    Re: Error in formula

    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?
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Error when calculating formula with LOOKUP

    Have you tried re-entering the formulas?

    I can't test them properly because they refer to external data.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Error when calculating formula with LOOKUP

    I know this is a complicated formula (this is why I like it)
    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.

    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.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  7. #7
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    145

    Re: Error when calculating formula with LOOKUP

    Hi, only C3 had external data, file updated. Thanks.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    145

    Re: Error when calculating formula with LOOKUP

    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!

  9. #9
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    145

    Re: Error when calculating formula with LOOKUP

    Quote Originally Posted by MrShorty View Post
    I'm not sure why one would prefer complicated formulas over simple formulas. ...
    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!

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Error when calculating formula with LOOKUP

    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

  11. #11
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    145

    Re: Error when calculating formula with LOOKUP

    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!

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Error when calculating formula with LOOKUP

    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.

  13. #13
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    145

    Re: Error when calculating formula with LOOKUP

    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!

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Error when calculating formula with LOOKUP

    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.

  15. #15
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    145

    Re: Error when calculating formula with LOOKUP

    OK I'm new in forums

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Error when calculating formula with LOOKUP

    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.

  17. #17
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Error when calculating formula with LOOKUP

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula not returning correct result. Formatting error or formula error?
    By Yonex1975 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-31-2019, 02:38 PM
  2. [SOLVED] Macro To IGNORE ERROR - Cell With Formula / Green Arrow Error
    By Logit in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-06-2018, 09:16 PM
  3. [SOLVED] FORMULA Remove #NUM error not using IFERROR OR IS ERROR
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-03-2017, 04:22 PM
  4. Formula error or data link error
    By sctr in forum Excel General
    Replies: 4
    Last Post: 09-19-2016, 09:15 AM
  5. [SOLVED] Inputbox error message. Formula you typed contains an error.
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-09-2013, 02:25 PM
  6. Replies: 4
    Last Post: 03-24-2006, 07:20 AM
  7. Error values:DIV/0! error in SumProduct formula with no division
    By Jerry W. Lewis in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 07:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1