Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculations and decimal points (Excel 2000)

    Hi Everyone,

    Currently I have an IF statement that looks similar to

    =IF( AND (B1 >= 5%, B1 < 7.5%), C1*0.02, C1*0.05)

    I have set column B to percentage with no decimal places. So when a cell in column B (which is based on another formula) equals 4.9 is display 5%.
    My problem is that the IF statement still recognises the cell to be 4.9 therefore if doesn't fit my IF condition
    properly. If I manually type 5% it works OK.

    Is there anyway, that it can be fixed. That whatever is displayed is the reference used in the IF statement.

    Thanks Kindly,

    Kerrie [img]/forums/images/smilies/smile.gif[/img]

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

    Re: Calculations and decimal points (Excel 2000)

    There are a number of possibilities, depending on what you want in the cell B1.

    1- If you want B1 to be rounded to the nearest integer, then you can use the ROUND, ROUNDUP or ROUNDDOWN function like this:

    <pre>=ROUND(currentformulainB1,0)
    </pre>


    2- If you want cell B1 to contain the current calculated value, then you can use the one of the ROUND functions in the IF formula, like this:

    <pre>=IF(AND(ROUND(B1,0)>=5,ROUND(B1,0)<8),C1*0.02 ,C1*0.05)
    </pre>


    3- If you want the percision of all of the values in the sheet to be exactly as displayed, you can go to the Calculation tab in the Tools/Options dialog box and select "Precision as displayed."
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculations and decimal points (Excel 2000)

    Hi Legare Coleman,

    Thank you very much, the Tools, Precision bit worked perfectly.

    Kerrie [img]/forums/images/smilies/smile.gif[/img]

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculations and decimal points (Excel 2000)

    Just to warn you,

    Tools, precision as displayed forces Excel to do all calculations with the numbers as they are displayed. You can loose accuracy by doing so!
    It also means if you change a number format, your calculations will be affected. If that is desired, no problem. If it isn't, I would go for one of the other options that were suggested.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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