If a number gives a percentage weighting!

28creation

Board Regular
Joined
Oct 13, 2014
Messages
124
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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
"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"))))))))))
 
Upvote 0
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.
 
Upvote 0
"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.
 
Upvote 0
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))))))))))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top