Cell Value Updated based cell input

D

DC

I'm trying to update a cell "A1" based on a cell value in "B1":

If cell value in B1 < 5, A1 should be 100
if cell value in B1 is between 5-10, A1 should be 200
if cell value in B1 is between 10-20, A1 should be 300
and so on.

How do I write a formula in A1 to make this calculation?
 
P

Paul

If this is just a three condition item, I would use an IF statment
=IF(condition,true,false) You can nest them up to 7 deep I think.

=IF(b1 < 5,100,if(and(b1>=5,b1<=10),200,if(and(b1>10,b1<20),300,"Not In
Range"))

If you want a matrix, then I think a match or even a vlookup would work
better.
 
B

Bill Kuunders

set up a table starting with 0,5,10,20,30 etc in the first column and
100,200,300,400,500 etc in the second column
then use a vlookup function in A1 such as

=VLOOKUP(B1,D1:E15,2)
D1:E15 is the area for the table
2 indicates to look in the second column of that table.
 
Top