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

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.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
So each row from B2, will have different conditions? Or do they all have the same conditons and results?
 

jas10001

New member
Joined
Dec 16, 2013
Messages
4
Reaction score
0
Points
0
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.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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..
 

jas10001

New member
Joined
Dec 16, 2013
Messages
4
Reaction score
0
Points
0
Thanks NBVC! Is there a way to combine all of the "IF(AND's into one long string instead of having 30 multiple rows?
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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....
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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...
 

jas10001

New member
Joined
Dec 16, 2013
Messages
4
Reaction score
0
Points
0
I'll check both scenarios out and see which one I actually need. Thanks for your help!
 
Top