Excel Rounding Problems

[joejensen]

Hi

I am trying to produce a set of figures in column B for a a set in column A.
The figure produce in Column B should be determine on preset conditons
If A<80%, B=0
If A>100%, B=20
If 80<=A<=100%, B= A-80%

All of set in A are positive percentages
The figure should then be *100 and then rounded down to the closest integer.

I have been using
"=INT((IF(A1>1, 0.2, (IF(80%<=A1, A1-0.8, 0)))*100))"

A B
0.75 0
0.76 0
0.77 0
0.78 0
0.79 0
0.8 0
0.81 1
0.82 1
0.83 2
0.84 3
0.85 4
0.86 5
0.87 6
0.88 8
0.89 9
0.9 10

As you can see there appears to be rounding issues possibly made during the calculations that is affection 0.82-0.87.
Would anybody be able to advise on this or produce an alternative formula for the same thing?

Thanks

[shg]

=if(a1 < 80%, 0, if(a1 < 100%, a1 - 80%, 20% ))

[joejensen]

=if(a1 < 80%, 0, if(a1 < 100%, a1 - 80%, 20% ))

Thanks for the help,

However, the central formula within the original works as does yours.
The issues i'm having is when the formula rounds it to down to the lowest integer. This has to be done as in practice cell A will contain percentages to 2 decimal places. i.e 93.45% or 0.9345.
At a later date I am gong to need to assign a value to each full percentage point which is reasoning behind rounding.

Can you offer assistance with this?

[shg]

It seems to me that your formula already does what you describe.

Provide some examples of where it doesn't, and what you want instead.

[joejensen]

It seems to me that your formula already does what you describe.

Provide some examples of where it doesn't, and what you want instead.

The formula doesn't appear to work when the section A =82.00%, 83.00%, 84.00%, 85.00%, 86.00% and 87.00%.

[shg]

What should it return in those cases?

[joejensen]

What should it return in those cases?

A=82.00% B should = 2
A=83.00% B should = 3
A=84.00% B should = 4
A=85.00% B should = 5
A=86.00% B should = 6
and A=87.00% B should = 7

The original formula should return A-80%*100 ( minus the prior conditions, a<80% and a>100%

[shg]

Looks fine to me:

Please Login or Register  to view this content.

You could also use =MIN(20%, (A1>80%)*(A1-80%))

[joejensen]

Looks fine to me:

Please Login or Register  to view this content.

You could also use =MIN(20%, (A1>80%)*(A1-80%))

Thanks for your help,
I have no idea why the original formula was returning incorrect figures in my spreadsheet. I have amended it slightly and all seems to be well now

=INT((IF((A1*100)>100, 20, (IF(80<=(A1*100), (A1*100)-80, 0)))))

thanks for you help