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

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

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.  Reply With Quote

2. So each row from B2, will have different conditions? Or do they all have the same conditons and results?  Reply With Quote

3. Originally Posted by NBVC 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.  Reply With Quote

4. 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..  Reply With Quote

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?  Reply With Quote

6. 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....  Reply With Quote

7. 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...  Reply With Quote

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

calcultaion, formula, math 