Assign a dollar amount to a corresponding number in a range of num

[Guest]

I have a column listing a number in a range of 1 through 70. I need to
create a new column that assigns a dollar value to the row based on the
number in the first column. The bands I have created are as follows:
Numbers 30 and greater= $42,000
Numbers between 21 and 30 = $26,000
Numbers 20 and less = $10,000
I have tried IF statements without luck, any help would be appreciated.

 

[Jay]

I have a column listing a number in a range of 1 through 70. I need to
create a new column that assigns a dollar value to the row based on the
number in the first column. The bands I have created are as follows:
Numbers 30 and greater= $42,000
Numbers between 21 and 30 = $26,000
Numbers 20 and less = $10,000
I have tried IF statements without luck, any help would be appreciated.

Supposing the "column listing a number" is column A, one way is to put:
=IF(OR(A1>70,A1<1),"Out of range",IF(A1>=30,42000,IF(A1>20,26000,10000)))
in B1 and copy down for as many rows as you need.

 

[Guest]

thanks Jay, it worked perfectly, makes sense to me now. I appreciate the help
Sandi

 

[Gord Dibben]

=LOOKUP(A1,{0,21,30},{10000,26000,42000})

Enter in B1 and double-click on the B1 fill handle to autofill.


Gord Dibben Excel MVP