+ Reply to Thread
Results 1 to 11 of 11

Formula not calculating correctly

  1. #1
    Registered User
    Join Date
    11-02-2012
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    4

    Formula not calculating correctly

    I have a formula =B37*12*B28*5% where B37=$18,000 and B28 = 0.233, which is derived from two other cells B27+B23 (.13+.10). The answer I am getting is 25.20 but when I calculate manually I get 25.16. Also, is I replace B28 with 0.233 rather than cell reference, I get 25.16.

  2. #2
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Maryland, USA
    MS-Off Ver
    Excel 2003/2007
    Posts
    145

    Re: Formula not calculating correctly

    Try this.

    =B37*12*B28*.05

    Use the decimal number intead of the % for the multiplication.


    Simeon

  3. #3
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,799

    Re: Formula not calculating correctly

    I'm guessing your values in B27 and B23 have more decimal precision than you're showing.

    First, 18000*12*0.233*5% = 2516.4 (not 25.16)

    Second, expand your cells to show all decimal places. If I adjust B23 to 0.1003 and B27 to 0.133 they appear as 0.10 and 0.13, and the sum in B28 appears as 0.23 or 0.233 if I only go to 3 decimal places, but it's actually 0.2333, which will throw off your calculations a bit.

    Excel does math on the actual value in the cell, not how it is displayed. There is a setting to calculate as displayed, but I'd recommend against ever using it.

  4. #4
    Registered User
    Join Date
    11-02-2012
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    4

    Wink Re: Formula not calculating correctly

    My apologies. Formula is formula =B37*12*B28%*5%. I tried changint 5% to .5 and still get the same answer. If I expand, I get 35.20000000. I did try the option to calculate as displayed but that did not work. I have also tried increasing the iterative calculation and the multi-threaded calculation. Still, the same...

    Thank you all for your responses!

  5. #5
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,799

    Re: Formula not calculating correctly

    Try posting a copy of your workbook. You can remove any sensitive data (as long as it won't impact the formulas in the cells mentioned). You can always use fake data, but remember to keep it of the same type (numbers where numbers should be, text for text, etc).

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,095

    Re: Formula not calculating correctly

    @ simeon, it makes no difference if you use 5% or 0.05 inteh calc, excel treatrs them as the same
    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

  7. #7
    Forum Contributor
    Join Date
    10-31-2012
    Location
    Maryland, USA
    MS-Off Ver
    Excel 2003/2007
    Posts
    145

    Re: Formula not calculating correctly

    Oh, I did not know that. I guess I could have saved myself some decimal places in past workbooks.
    Thanks for the info!

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,095

    Re: Formula not calculating correctly

    lol now if i could only get it to do a spell check for me in here, i would be all set

  9. #9
    Registered User
    Join Date
    11-02-2012
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Formula not calculating correctly

    Here is the spreadsheet.Commission template.xls

  10. #10
    Registered User
    Join Date
    11-02-2012
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Formula not calculating correctly

    I did an evaluation of the formula and it came up with "The cell currently being evaluated contains a constant". How can I fix that? Thank you all for your help!

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

    Re: Formula not calculating correctly

    That message is for evaluating a cell that contains a constant rather than a formula. You fix it by either selecting a cell that contains a formula before calling the evaluate tool, or putting a formula in the cell that is selected. Double check which cell you have selected before calling the evaluate formula tool.

    And it looks to me like Paul explained the error -- your hand calculation is using rounded values, the Excel calculation is using the values to their full 15 digit precision. Ultimately this traces back to the B7 calculation that results in 4/30 which is a repeating decimal. Whether the "rounded" calculation like you did by hand or the full precision calculation as Excel did it is more correct, I will leave to you to decide. When you want Excel to use rounded numbers in calculations, be sure to include the ROUND() or similar function to force Excel to round to the correct number of decimal places.
    Last edited by MrShorty; 11-09-2012 at 01:28 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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