+ Reply to Thread
Results 1 to 26 of 26

[SOLVED]Formula producing wrong result

  1. #1
    Registered User
    Join Date
    10-26-2013
    Location
    Mexico City
    MS-Off Ver
    Excel 2013
    Posts
    9

    [SOLVED]Formula producing wrong result

    Hi all! I could really use your help with this.

    So, here are columns A, B, and C. I was supposed to copy the data from column B to column C and change the labels to "Optimal Price".

    Next, formula =B7*(1+B4*(1-C8/B8)) was entered to cell C7. I didn't come up with this formula myself, but got it from the textbook instructions for this assignment.

    The value in cell C7 is definitely supposed to be 10,486 when $12.97 is entered into C8. Again, got both numbers from the instructions.

    But it is not. What is wrong? All the other formulas copied from column B are identical in column C. I haven't changed anything in column B or C, except for entering the formula in C7 and the new unit price in C8.

    How would it even be possible to get that value (10,486)? I calculated the answer manually, and the answer is 10,490 (rounded).

    What have I done wrong?

    Edit: Added the workbook
    Attached Files Attached Files
    Last edited by tobu56; 10-26-2013 at 02:08 AM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Formula producing wrong result

    To attach a Workbook
    (please do not post pictures of worksheets)
    • Click Advanced (next to quick post),
    • Scroll down until you see "Manage Attachments",
    • Click that then select "add files" (top right corner).
    • Click "Select Files" find your file, click "open" click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Click "Done" at bottom right to close the Attachment Manager.
    • Click "Submit Reply"
    Ben Van Johnson

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula producing wrong result

    Hi and welcome to the forum

    Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula producing wrong result

    Well, your picture really does not tell us anything, except that the values you expect are not where you expect them,other than that, it is not very useful, a sample workbook would be far better...
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  5. #5
    Registered User
    Join Date
    10-06-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    87

    Re: Formula producing wrong result

    Please upload a workbook with a clear explanation in the sheets itself by highlighting the cells, rows or columns where you want to get the help for with a remark if needed.


    regards

  6. #6
    Registered User
    Join Date
    10-26-2013
    Location
    Mexico City
    MS-Off Ver
    Excel 2013
    Posts
    9

    Re: Formula producing wrong result

    Ok, sorry. Here's the workbook.

    I got the formula from the textbook, so that's what has to be used in C7. =B7*(1+B4*(1-C8/B8))

    With the price of $12.97 in cell C8, the units sold value in C7 has to be 10,486.
    Attached Files Attached Files

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula producing wrong result

    LOL sorry, looks like you got pounded on, that was unintentional

    Well if "that's what has to be used" (sounds like a homework assignment???), then based on the values you supplied, the answer is 10 490, not 10 486. The only way I can see to get to 10 486 is if those numbers were not exactly as you showed?

    For instance, if you use 12.972 instead of 12.97, then you come back to 10 486.2545454545 - pretty close to 10 486

  8. #8
    Registered User
    Join Date
    10-26-2013
    Location
    Mexico City
    MS-Off Ver
    Excel 2013
    Posts
    9

    Re: Formula producing wrong result

    Quote Originally Posted by FDibbins View Post
    LOL sorry, looks like you got pounded on, that was unintentional

    Well if "that's what has to be used" (sounds like a homework assignment???), then based on the values you supplied, the answer is 10 490, not 10 486. The only way I can see to get to 10 486 is if those numbers were not exactly as you showed?

    For instance, if you use 12.972 instead of 12.97, then you come back to 10 486.2545454545 - pretty close to 10 486
    :D No worries. That's what n00bs get.

    Yes, it is an assignment. The thing is, the $12.97 value is also given. So that's why I'm wondering, is there something else I'm supposed to be doing.

    Thanks anyway.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula producing wrong result

    Well you have the formula and you have the values - calc it by hand and see what you come up with?

  10. #10
    Registered User
    Join Date
    10-26-2013
    Location
    Mexico City
    MS-Off Ver
    Excel 2013
    Posts
    9

    Re: Formula producing wrong result

    Quote Originally Posted by FDibbins View Post
    Well you have the formula and you have the values - calc it by hand and see what you come up with?
    Yep, that's what I did. And got 10,490.

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula producing wrong result

    thats what I got too. Who/what is telling you the answer needs to be 10 486?

  12. #12
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula producing wrong result

    Well, are the values in column B right?
    the closest I can is changing B7 to 13995 with B8 @ 11 (10486.07)
    or B8 to 10.99 with B7 @14000 (10468.79)
    so, if all the values are right, and the formula HAS to be right (?) then it must have to be a formatting thing (not really sure how...but only thing I can think of...)

  13. #13
    Registered User
    Join Date
    10-26-2013
    Location
    Mexico City
    MS-Off Ver
    Excel 2013
    Posts
    9

    Re: Formula producing wrong result

    Quote Originally Posted by FDibbins View Post
    thats what I got too. Who/what is telling you the answer needs to be 10 486?
    It's in the textbook. The example screenshots I got from my teacher have it correct as well. It's just not correct in my workbook.

    Hm, could it be because I'm using Excel 2013 to do an assignment that's meant for Excel 2010? Haven't had any problems yet with any other assignments, but maybe I do now.

    Quote Originally Posted by dredwolf View Post
    so, if all the values are right, and the formula HAS to be right (?) then it must have to be a formatting thing (not really sure how...but only thing I can think of...)
    Hm, yeah. I thought that something might have gone wrong when copying column B, but can't really think what I could change.

    And yes, the values in column B are correct.
    Last edited by tobu56; 10-26-2013 at 02:17 AM.

  14. #14
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula producing wrong result

    Actually, I finally got too 10 480.01 with B7=14015 and B8 = 11.99...with proper formatting THAT could be turned into a DISPLAYED answer of what is expected...just not sure what you are looking for here...

  15. #15
    Registered User
    Join Date
    10-26-2013
    Location
    Mexico City
    MS-Off Ver
    Excel 2013
    Posts
    9

    Re: Formula producing wrong result

    Quote Originally Posted by dredwolf View Post
    Actually, I finally got too 10 480.01 with B7=14015 and B8 = 11.99...with proper formatting THAT could be turned into a DISPLAYED answer of what is expected...just not sure what you are looking for here...
    Thanks, thought about tweaking the values and formatting too. But that would start changing the values in other cells in other sheets, so can't be an option.

    Thanks anyway. It's just really weird to follow all the instructions to the point and still get things wrong. Oh well, I'll just contact my teacher and grovel a bit.

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula producing wrong result

    yes with formatting to 2 dec, 12.972 instead of 12.97 would give the correct answer, and still only show 12.97...so it appears that the mystery is as a result of formatting?

  17. #17
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula producing wrong result

    Just wondering, this is a textbook, so.. what is the textbook Name, and what is the chapter Title?, this may give some clues as to what we are actually trying to figure out here (ie- formula modification, formatting, etc,,)

  18. #18
    Registered User
    Join Date
    10-26-2013
    Location
    Mexico City
    MS-Off Ver
    Excel 2013
    Posts
    9

    Re: Formula producing wrong result

    Quote Originally Posted by FDibbins View Post
    yes with formatting to 2 dec, 12.972 instead of 12.97 would give the correct answer, and still only show 12.97...so it appears that the mystery is as a result of formatting?
    I wish it were that simple. The value in C9 is supposed to be $136,023.64

    Quote Originally Posted by dredwolf View Post
    Just wondering, this is a textbook, so.. what is the textbook Name, and what is the chapter Title?, this may give some clues as to what we are actually trying to figure out here (ie- formula modification, formatting, etc,,)
    New Perspectives on Microsoft Excel 2010: Comprehensive
    Authors: Parsons, Oja, Ageloff, Carey
    ISBN-10: 0538742917
    Tutorial 10. Performing What-If-Analyses. Analyzing the Cost-Volume-Profit Relationship

    The objective with this part of the assignment is to project the units sold based on the sales price by entering the aforementioned formula.
    Last edited by tobu56; 10-26-2013 at 02:46 AM.

  19. #19
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula producing wrong result

    the only other thing I can suggest is to work the formula back, as you know the expected end result, use that to calculate backwards to the right values (yes, I know, lots of guesswork and surmising, but is the only other thing I can think of atm )

  20. #20
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula producing wrong result

    Just getting the book, give me a few hours to download,read and understand what we are supposed to be accomplishing here (although, by the chapter title, it sounds like it may be using the built in 'what if' tool..just not sure yet..)

  21. #21
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula producing wrong result

    Noticed changing the price elasticity can get me the value of 10,486 in c7, but will not get the value of C9 to equal 136,023.64, but seems to be getting closer... maybe we are missing something there?

  22. #22
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula producing wrong result

    self deleted,duplicate post
    Last edited by dredwolf; 10-26-2013 at 03:43 AM.

  23. #23
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula producing wrong result

    Well, you've got me hooked for sure now....I can get close by adding minute fractions to each of B4 and B8, but still can't get it all to line up...I NEED to see the answer tothis now...LOL
    gonna be awhile before the book downloads, (I have high speed, apparently the bookseller does NOT (must be so I watch the ads.))

  24. #24
    Registered User
    Join Date
    10-26-2013
    Location
    Mexico City
    MS-Off Ver
    Excel 2013
    Posts
    9

    Re: Formula producing wrong result

    Thanks, @dredwolf

    It's the strangest thing. There must be come catch here.

    I tried using the same values as in the tutorial assignment, and everything worked out. So, it can't be some formatting issue.

    But when I use the values I'm supposed to have with the review assignment, it does not work.

  25. #25
    Registered User
    Join Date
    10-26-2013
    Location
    Mexico City
    MS-Off Ver
    Excel 2013
    Posts
    9

    Re: Formula producing wrong result

    OMG!!!!!!!!! GOT IT!!

    I used Goal Seek. Set cell C9 to value 136023.64 by changing cell C8. And there it was, everything worked out. So weird.

    I don't know if I was supposed to do that because the instructions never mentioned that. But I guess I can call this problem solved.

  26. #26
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: [SOLVED]Formula producing wrong result

    Glad you found the Solution...I was going a little nuts here (well, a little more nuts )
    Congrats !

+ 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. vlookup producing wrong results
    By moley165 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-05-2013, 08:21 AM
  2. Wrong result with IF formula
    By rizmomin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-25-2012, 03:57 PM
  3. [SOLVED] Index and Match combination producing the wrong result
    By Authentik8 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-10-2012, 11:01 AM
  4. formula giving wrong result sometimes?
    By lnjr in forum Excel General
    Replies: 2
    Last Post: 08-13-2010, 09:20 AM
  5. FORMULA PRODUCES WRONG RESULT
    By Wildebeest222 in forum Excel General
    Replies: 2
    Last Post: 10-11-2005, 05:05 AM

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