Results 1 to 7 of 7

Thread: If cell A1 = then make cell a2 =100 etc

  1. #1

    If cell A1 = then make cell a2 =100 etc



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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???

  2. #2
    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,

  3. #3
    Quote Originally Posted by bgoree09 View Post
    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,
    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 by gazza uk; 2014-10-29 at 10:45 PM.

  4. #4
    Sorry about that

    The last bit should read:

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

  5. #5
    Quote Originally Posted by bgoree09 View Post
    Sorry about that

    The last bit should read:

    IF(A1>6,50,"")))
    What if I want to say if its between 6 and 11 return 40

  6. #6
    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.).


  7. #7
    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"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •