Excel Rounding Problems

Originally Posted by shg

=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?

Originally Posted by 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.

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

Originally Posted by shg

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%

Originally Posted by shg

Looks fine to me:

      -A- -B- -----------------------------C------------------------------
  1   78%  0% B1 and down: =IF(A1 < 80%, 0, IF(A1 < 100%, A1 - 80%, 20% ))
  2   80%  0%                                                             
  3   82%  2%                                                             
  4   84%  4%                                                             
  5   86%  6%                                                             
  6   88%  8%                                                             
  7   90% 10%                                                             
  8   92% 12%                                                             
  9   94% 14%

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