Use rounded value (to tenths place) in a single cell's calculation

**tinytutu** · 11-18-2013, 03:08 AM

In A2, I have a formula that yields a percentage.

In A3, I have a formula that will yield a phrase that indicates what range the percentage falls into. It works fine, except when the percentage in A2 is at the min or max of the range.

Here's the formula I made:

=IF(AND(ROUND(A1,2)>=0%,ROUND(A1,2)<=5%),"Tier 1",IF(AND(ROUND(A1,2)>=5.1%,ROUND(A1,2)<=9.9%),"Tier 2",IF(AND(ROUND(A1,2)>=-9.9%,ROUNDUP(A1,2)<=-0.1%),"Tier 3",IF(ROUND(A1,2)<=-10%,"Tier 4",IF(ROUND(A1,2)>=10%,"Tier 5")))))

For example, if you put 9.9% in A2, it gives you Tier 5, not Tier 2. If I don't use the "Round" function, it gives the right answer, but not all the time. For example, if A2 were 9.91%, it returns Tier 5, not Tier 2. I need the percentage rounded to the tenths place (rounded up if the second decimal is a 5), and for that rounded value to be considered in the formula in A3.

Any advice? Thank you!

**martindwilson** · 11-18-2013, 03:25 AM

round to 3 not 2 9.9% rounded to 2 is 10% because its actually rounding 0.099 to 0.1 but round to 3 leaves it as 0.099
or 9.9%

**wenqq3** · 11-18-2013, 03:40 AM

I roundup(9.91%,2) = 10.00%, but if i roundup(9.91%,4) = 9.91%

That why "if you put 9.9% in A2, it gives you Tier 5, not Tier 2."

to tenths place roundup(A1,12)
Is this answer your question.