Return range of values

[wayne0881]

Hi All,

I have a value in cell D5 the lowest it will be is 3 and the highest is 20.

In cell D10 I would like to return a value based on the value of cell D5

In D10:

If D5 is between 3-4 I would like D10 to return D5 + 0.05
If D5 is between 4-5 I would like D10 to return D5 + 0.10
If D5 is between 6-10 I would like D10 to return D5 + 0.20
If D5 is above 10 I would like D10 to return D5 + 0.5

Thanks in advance

 

[Aladin Akyurek]

In D10 enter:

=LOOKUP(D5,{0,3,4,6,10},{0,0.05,0.1,0.2,0.5})+D5

 

[cooper645]

I made assumptions when it came to whether the cell contained a 4 or 10, with regard to how you wanted the answer as the info you provided was loose.

my answer assumes greater than or equal to 3 and less than 4, greater than or equal to 4 and less than 5, greater than or equal to 5 and less than 10, greater than or equal to 10 and less than or equal to 20, with an error if number does not fall between 3 and 20 inclusive.

=IF(AND(D5>=3,D5<4),D5+0.05,IF(AND(D5>=4,D5<5),D5+0.1,IF(AND(D5>=5,D5<10),D5+0.2,IF(AND(D5>=10,D5<=20),D5+0.5,"Number not between 3 and 20"))))

as a nested formula

 

[wayne0881]

Thanks for suggestions really appreciate it. Saved me a headache