If cell A1 = then make cell a2 =100 etc

G

gazza uk

New member
Joined
May 29, 2014
Messages
41
Reaction score
0
Points
0
Trying to set up a score sheet based on what info in inputted if a1=1 then return 100 in b2 but if a1=2 then return 90in b2 but also have if a1=6 to 10 return 50 in b2 and so on what formula should I use???
 
Good afternoon,

You can use a nested if. There's probably something better, but it would work:

=IF(A1=1,100,IF(A1=2,90,IF(A1>6,50)))

Best of luck,
 
bgoree09 said:
Good afternoon,

You can use a nested if. There's probably something better, but it would work:

=IF(A1=1,100,IF(A1=2,90,IF(A1>6,50)))

Best of luck,
Click to expand...

That works but if a1 has no number in the cell with the formula says False how do you get that to remain blank until a1 has a number in it
 
Last edited:
Sorry about that

The last bit should read:

IF(A1>6,50,"")))
 
Then instead of:

IF(A1>6,50,"")))

Use:

IF(AND(A1>6,A1<11),40,"")

if 6 and 11 are also valid then you can add "=" after the greater than and less than (to accomplish greater than or equal to, etc.).

 
OK having problems with this is what I am trying to do 1=100 2=90 : 3=80 : 4=70 : 5=60 : 6 to 10=50 : 11 to 20=40 : 21 to 30=30 : 31 to 40=20 : 41 to50=10 but when I get to this point : =IF(C3=1,100,IF(C3=2,90,IF(C3=3,80,IF(C3=4,70,IF(C3=5,60,IF(AND(C3>5,C3<11),50,IF(AND(C3>10,C3<21),40,IF(AND(C3>20,C3<31),30,""))))))) I get a message coming up saying !the specified formula cannot be entered because it uses more levels of masking than are allowed in the current format"
 
You must log in or register to reply here.
Back
Top