Determine whether a value is between a range and return a value

[Assad786]

I'm trying to input a value and have a value returned based on where that input falls between a range. I'd like the formula to round up so in the example below I've inputted 110 which I'd like the formula to round up to 120 and return a value of 20. If that value was 130 it would return a 30 and so on. Can someone please assist?

[pre]

A	 B	 C	 D	  E	  F	 G	 H
1	Input	        110
2	Cost Returned	20
3
4
5
6	Cost
7	$100	$10		$280	$100		$460	$190
8	$120	$20		$300	$110		$480	$200
9	$140	$30		$320	$120		$500	$210
10	$160	$40		$340	$130		$520	$220
11	$180	$50		$360	$140		$540	$230
12	$200	$60		$380	$150		$560	$240
13	$220	$70		$400	$160		$580	$250
14	$240	$80		$420	$170		$600	$260
15	$260	$90		$440	$180		$620	$270

[/pre]

 

[NARAYANK991]

Hi Assad ,


Can you confirm whether the rounding up is always upwards ? For example , suppose a value of 401 is input ; should the cost returned be 160 or 170 ?


Narayan

 

[Assad786]

It should always round up so 170.

 

[Faseeh]

Hi Assad786,


Please see this, it would be simple if you can rearrange your table a little bit:


http://dl.dropbox.com/u/60644346/Vlookup.xlsx


Faseeh

 

[NARAYANK991]

Hi Assad ,


If you cannot rearrange your table , as suggested by Faseeh , then the following formula will do the job ; of course , it does not really make use of the table , except for certain features of the table :


1. The table itself is named Data_Range in the formula , referring to : =Sheet2!$B$8:$I$16


2. The minimum value of the range is taken as the first value , which is 100 ; I have named this Min_Range , referring to : =INDEX(Data_Range,1,1)


3. The maximum value of the range is named Max_Range , referring to : =MAX(Data_Range)


4. The total number of items in the table ( the values from 100 through till 620 ) is named as Number_of_Items , referring to : =(COUNT(Data_Range)/2)


5. The interval is the difference between any two items ; in your case , it is 20. This is named as Separation , and is calculated as : =(Max_Range-Min_Range)/(Number_of_Items-1)


The formula itself is :


=MIN(270,MAX(10,10+10*(INT(($C$2-Min_Range)/Separation)+IF(MOD(($C$2-Min_Range)/Separation,1)<>0,1,0))))


C2 is the cell in which you enter the value , between 100 and 620. Any input less than 100 will generate an output of 10 , while any input greater than 620 will generate an output of 270.


270 and 10 have been hard-coded in the formula ; if these change , you will have to change the formula accordingly.


The formula will work only as long as the interval between any two values is the same through out the table.


Narayan

 

[Assad786]

Thanks worked perfectly!