Previous Topic Next Topic

Changing values in one cell depending on drop down

Solved/Closed
NOVA007 Posts 2 Registration date Wednesday January 19, 2022 Status Member Last seen January 21, 2022 - Updated on Jan 19, 2022 at 10:47 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Jan 21, 2022 at 05:20 AM
Hello,
I created a drop-down list of scores (ie. 10, 20, 30 to 100) in cell A1. In cell B1 I want it to show a specific number value based on which selection has been chosen in the drop-down. So if I select 10 or 20 in Cell A1, it shows 1 in Cell B1 and similarly, if I select 90 or 100 in Cell A1 it shows 5 in Cell B1.

I have tried using the IF function =IF(A1="10",1,IF(A1="20",1,IF(A1="30",2,"")))

But it does not seem to work.

Does anyone know how to fix this formula to achieve this?

2 responses

Answer 1 / 2
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Updated on Jan 21, 2022 at 01:25 AM
Hello Nova,

It's a bit long winded but try this IF/OR formula in B1:- 
=IF(A1="","",IF(OR(A1=10,A1=20),1,IF(OR(A1=30,A1=40),2,IF(OR(A1=50,A1=60),3,IF(OR(A1=70,A1=80),4,IF(OR(A1=90,A1=100),5))))))


or this option:- 

=IF(OR(A1=10,A1=20),1,IF(OR(A1=30,A1=40),2,IF(OR(A1=50,A1=60),3,IF(OR(A1=70,A1=80),4,IF(OR(A1=90,A1=100),5,"")))))


I hope that this helps.

Cheerio,
vcoolio.
2
NOVA007 Posts 2 Registration date Wednesday January 19, 2022 Status Member Last seen January 21, 2022
Jan 21, 2022 at 03:48 AM
Wow, Thanks a Billion

It worked perfectly.
0
Answer 2 / 2
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Jan 21, 2022 at 05:20 AM
You're welcome Nova.
I'm glad to have been able to help.

Cheerio,
vcoolio.
0

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses
Counting cells containing only numbers
jutulu -
jutulu -

4 responses
If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses
How to use conditional format based on text?
Leanne99 -
Mooseman -

26 responses
Excel - IF function with multiple words
Nick -
user -

14 responses