# Thread: Calculations and decimal points (Excel 2000)

1. ## 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. ## 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."

3. ## 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. ## 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.

#### Posting Permissions

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