Results 1 to 8 of 8

Thread: This should be simple for someone with a little more than no experience in Excel

  1. #1

    This should be simple for someone with a little more than no experience in Excel



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

    I am trying to write a formula to report a specific value in one cell if the value of another cell fits a range of value. If the value of B2 is between 10 and 19 I want the value of C2 to be 31; if not I don't want it to have a value. Furthermore if B3 is between 20 and 29 I want the value of C3 to be 47; if not I don't want it to have a value. I will have about 30 "if this then that" rows in my spreadsheet. Any help is much appreciated.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,462
    Articles
    0
    Excel Version
    Excel 2016
    So each row from B2, will have different conditions? Or do they all have the same conditons and results?


  3. #3
    Quote Originally Posted by NBVC View Post
    So each row from B2, will have different conditions? Or do they all have the same conditons and results?
    Yes, each row in the B column will have different conditions and different results in the C column.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,462
    Articles
    0
    Excel Version
    Excel 2016
    Well then you will need a separate formula for each.

    e.g.

    in C2:

    =IF(AND(B2>=10,B2<20),31,"")

    in C3:

    =IF(AND(B3>=20,B3<30),47,"")

    etc..


  5. #5
    Thanks NBVC! Is there a way to combine all of the "IF(AND's into one long string instead of having 30 multiple rows?

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,462
    Articles
    0
    Excel Version
    Excel 2016
    But you said that each row would have it's one conditions and results.... that would mean that the formula would have to check which row it's in, before making other checks....


  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,462
    Articles
    0
    Excel Version
    Excel 2016
    If you didn't mean that.. then maybe you need just a simple LOOKUP function

    e.g. =LOOKUP(B2,{10,20,30,40,50,60,...etc},{31,47,58,69,77,89,...etc})

    copied down... in the first array are listed the lower bounds of each range. The second array lists the respective results you want if within the ranges...


  8. #8
    I'll check both scenarios out and see which one I actually need. Thanks for your help!

Tags for this Thread

Posting Permissions

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