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

J

jas10001

New member
Joined
Dec 16, 2013
Messages
4
Reaction score
0
Points
0
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.
 
So each row from B2, will have different conditions? Or do they all have the same conditons and results?
 
NBVC said:
So each row from B2, will have different conditions? Or do they all have the same conditons and results?
Click to expand...
Yes, each row in the B column will have different conditions and different results in the C column.
 
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..
 
Thanks NBVC! Is there a way to combine all of the "IF(AND's into one long string instead of having 30 multiple rows?
 
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....
 
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...
 
I'll check both scenarios out and see which one I actually need. Thanks for your help!
 
You must log in or register to reply here.
Back
Top