If a number gives a percentage weighting!

[28creation]

Hi all,

I'm currently designing a scorecard in work which gives a certain weighting for several objectives...

OBJ 1 - 10%
OBJ 2 - 5%
OBJ 3 - 7.5%
etc, etc

Some cells contain words, such as outstanding, achieving, developing, etc & I've been able to give a % weight to each of these with this formula...

=IF(O8="OUTSTANDING","25%",(IF(O8="EXCEEDING","20%",(IF(O8="ACHIEVING","15%",(IF(O8="DEVELOPING","10%",(IF(O8="UNACCEPTABLE","5%",(IF(O8="","")))))))))))

Other objectives have numbers or percentages instead of words but using the same formula doesn't work. Can anyone help edit the formula or give me an alternative please?

Thanks in advance.

 

[etaf]

"25%" is a word
0.25 would be a number

so
=IF(O8="OUTSTANDING",0.25,(IF(O8="EXCEEDING",0.20,(IF(O8="ACHIEVING",0.15,(IF(O8="DEVELOPING",0.10,(IF(O8="UNACCEPTABLE",0.05,(IF(O8="","")))))))))))
but you are missing a false
=IF(O8="","", IF(O8="OUTSTANDING",0.25,(IF(O8="EXCEEDING",0.20,(IF(O8="ACHIEVING",0.15,(IF(O8="DEVELOPING",0.10,(IF(O8="UNACCEPTABLE",0.05,"other"))))))))))

 

[jproffer]

VLookup would work.

Also, just so you're aware of it, you're entering the percentages as text. That's fine as long as you don't want to do any calculations with them, but if you do, they'll need to (at some point) be converted to numbers or just entered as numbers now.

 

[28creation]

"25%" is a word
0.25 would be a number

so
=IF(O8="OUTSTANDING",0.25,(IF(O8="EXCEEDING",0.20,(IF(O8="ACHIEVING",0.15,(IF(O8="DEVELOPING",0.10,(IF(O8="UNACCEPTABLE",0.05,(IF(O8="","")))))))))))
but you are missing a false
=IF(O8="","", IF(O8="OUTSTANDING",0.25,(IF(O8="EXCEEDING",0.20,(IF(O8="ACHIEVING",0.15,(IF(O8="DEVELOPING",0.10,(IF(O8="UNACCEPTABLE",0.05,"other"))))))))))

Thanks for the reply etaf, but putting the percentage in is fine, they appear no problem.

What I need is to replace the words Outstanding, Exceeding, Achieving, etc in the formula with numbers.

The numbers are in other cells & the percentage weighting is attached to whatever number is in that cell.

Hope you can help.

 

[shg]

See https://www.mrexcel.com/forum/excel-questions/1042249-weighting-score.html

 

[etaf]

can you give examples

if the numbers for example
1 = 25%
2 = 20%
3 = 15%
4 = 10%
5 = 5%
etc
then

=IF(O8="","", IF(O8=1,0.25,(IF(O8=2,0.20,(IF(O8=3,0.15,(IF(O8=4,0.10,(IF(O8=5,0.05,0))))))))))