Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculation Funnies? (MS Excel/97)

    The attached file has a problem that I seek help with.

    The calculations are not correct and I am unable to work out why.

    Can anyone offer advice please?

    TIA, Leigh
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation Funnies? (MS Excel/97)

    I am not sure in what way the calculation is incorrect.
    It seem to be doing what it should be. ie
    with your figures in B15:C15 the max is greater than 0.46 and your formula gives 0.25
    delete them and the max is not greater than 0.46 and the formula gives 0.5

    What were you expecting to get?

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation Funnies? (MS Excel/97)

    The formula in F7 should be

    =IF(MAX(C7:C10>=0.46),"0.25","0.5")

    because the value in C8 is PRECISELY 0.46. HTH
    Gre

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation Funnies? (MS Excel/97)

    I see at least three things wrong with your formula:

    1- The first right parenthesis is in the wrong place. The correct place is:

    <pre>=IF(MAX(C7:C10)>0.46,"0.25","0.5")
    </pre>


    2- Your formula does not take binary conversion rounding errors into account. So, if you format cell C8 to a number and increase the number of decimal places displayed to 15, you will see that the actual value in that cell si 0.460000000000008. That is greater than 0.46 and therefore will cause the formula in F7 to return ".25" even though it looks like it should return ".5". You could correct this like this:

    <pre>=IF(ROUND(MAX(C7:C10),2)>0.46,"0.25","0.5")
    </pre>


    3- Your IF statement is returning a string value, not a number value. That is fine if that is what you want, but if you want to use this cell in other calculations, the formula should read:

    <pre>=IF(ROUND(MAX(C7:C10),2)>0.46,0.25,0.5)
    </pre>

    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation Funnies? (MS Excel/97)

    Thanks for everyones' replies.

    I rushed to create the workbook and did put the closing bracket for the MAX function in the wrong place. (The PC I sent from did not have MS Excel to check before sending. What about Internet Explorer for reading Office files, I hear you ask... I forgot!)

    I had heard about binary rounding but had not investigated it, but Legare's response hit the nail right on the head, again.

    I am grateful, Leigh

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation Funnies? (MS Excel/97)

    Curiouser and Curiouser!!

    The formula worked just fine as it was for me! Until I re-wrote it elsewhere!! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    If you look at the attached picture you will see that after deleting B15:C15, F7 gave the result 0.5 (as a number) but when I retyped the formula in F8 I got 0.25 as text!
    Copying or dragging the formula still gives me the correct (but wrong?? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> ) result <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>

    <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> Think that I will need stronger tablets now LOL <img src=/S/flee.gif border=0 alt=flee width=25 height=25>
    Attached Images Attached Images

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation Funnies? (MS Excel/97)

    Are you sure that result is in F7 is a number and not text? On the original sheet, that cell is formatted to align right so text would look like a number. However, when I enter this formula in another cell:

    <pre>=ISTEXT(F7)
    </pre>


    The result is True.

    Can't tell why you got the different results without having the workbook to look at.
    Legare Coleman

  8. #8
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation Funnies? (MS Excel/97)

    I did not think to look a cell alingment <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    The workbook was just as I opend it with Exporer, but I tried copying the cells over to excel proper and got the same effect.

    copying the cell left it the same, but copying the formula from the edit bar and pasting it into another cell broke the thing!


    Peter

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation Funnies? (MS Excel/97)

    Copying and Pasting takes the format along with the value unless you do a Paste Special/Value.
    Legare Coleman

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation Funnies? (MS Excel/97)

    The original MS Excel workbook (not the sample file posted by me) was actually intended to return text as both the True and False arguments. It relies on values being met to return the 'text' .

    The solution came when Legare advised that the binary conversion could be allowed for by using the ROUND function. This did solve my particular case, mainly because the results in C7:C10 were not simple values, but the calculations returned from the values in B16:C18.

    Many thanks for your input, Leigh

  11. #11
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Calculation Funnies? (MS Excel/97)

    I know it 's a bit late in the day, but did you also check you had 'Precision as displayed' checked on the toolsoptionsCalculations tab.
    I can feel the flames coming already, but it does exactly as it says and gives 'true to view' answers.
    Alan

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation Funnies? (MS Excel/97)

    Thanks Alan

    I did not have that option enabled as there is a need to use the figures as precisely as possible. In this case, that meant that a value of 0.46 had to be considered other than the MS Excel calculated value of 0.460000000...08

    My problem was answered by Legare (and Bat17 raised an issue with the same formula) although you make a good point of which others need to be aware.

    Leigh

Posting Permissions

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