Auto Percentage %

Dears,

If I have the below rating,


Outstanding
Excellent
V.Good
Good
Need Improvement
Poor

And I rank the above rating based on the below percentages,

0 -10%
11 - 20%
21- 30%
31 - 50%
51 - 80%
81 - 100%

How can automate excel to show Outstanding in a specific cell when the rating cell is between 0-10% and so on?


Thank you very very much.

=if(A1<=10,6,if(A1<=20,5,if(A1<=30,4,if(A1<=50,3,if(A1<=80,2,1)))))

Try this function it will work but you have to put percentage values in A1 cell

_______________________________________________________________________________________
Please click * to say thank You

Thank you very much, its working

is there a way to modify the formula and automatically insert the word outstanding with bold?

Thank you very much, its working

is there a way to modify the formula and automatically insert the word outstanding with bold?

thnx its outstanding

Use Conditional Formatting, create a 'New Rule', select the last option 'use a formula to determine which cells to format', type in this formula =B1="Outstanding" (or wherever cell the result for the formula is) then click on 'Format' and in the 'Font' tab, select 'Bold' under 'Font Style' - click 'OK'

Thanks,

lets make more complicated.

lets say I need to evaluate performance and I need to say,

If Error % is 0-10% it will = "Outstanding" and if customer files quality = "Outstanding" then the overall ranking ="Outstanding"

and so on for lower grades and percentages. I think it will be too complicated.

Hi Vitorio,

If you click on Conditional Formatting, then select "New Rule" and the 'New Formatting Rule' dialog box opens and here you can see see 'Select a Rule Type' and without doing anything more look at the standard options and what you are wanting to do can be accomplished using these options - have a play!

Thank you for your kind help.

Appreciate it


I have missed something that is quite important. The formula is working great to insert a text like 'outstanding' when the percentage is 0%. however, if there is no accounts opened I need the text to 'N/A'.

I have a column that contains number of accounts opened, Another that contains number of wrong records, third that contains the percentage error rate and finally the column where the text will be inserted automatically. if there is Zero number of account opened, that the final cell should contains N/A


how can i add that please?

Thank you

I tried using conditional formatting but i could not find the option im looking for.

Viitorio may i suggest you upload a smaple of the problem you are having.

I have used this formula to get the auto text

=LOOKUP(A1,{0,0.11,0.21,0.31,0.51,0.81},{"Outstanding","Excellent","V.Good","Good","Need Improvement","Poor"})

it is like this
http://img202.imageshack.us/img202/6400/b1i9.jpg


Thank you for your help

Originally Posted by Ursul

Viitorio may i suggest you upload a smaple of the problem you are having.

Please upload a sample book! that would be handy to work

Originally Posted by Ursul

Viitorio may i suggest you upload a smaple of the problem you are having.

Please upload a sample book! that would be handy to work

please find the attached sample

So you wanted "Outstanding" bold is that right?

See attached: OUTSTANDING - FORMAT.xlsx

and to insert N/A in percentage ranking when there are no accounts opened.

Why would there be no accounts open??

Anyway have a look, I changed the formula so that if there is nothing i.e. blank in col A then the formulae in columns C & D give no results. See A13 is blank hence no results in that row.

OUTSTANDING - FORMAT.xlsx

Thanks for your help, I really appreciate it.

I need to have ZERO accounts opened because I'm rating bank branches toward opening new customer accounts. It happens that some branches does not get a single customer account during the report period which means the value has to be zero in the report (where it says account opened). In such scenario I need the formula to check that if account opened are Zero, then the rating will be N/A. otherwise it is going to be outstanding because there is not errors and that's wrong and not fare to other branches.

Thanks again for your help, you are very kind.

Vitorio you are more than welcome and thanks for the feedback