A number between the range

[Chintan Trivedi]

Hi,

I have a number like 50 and there are certain range like...
0-50 : 10%
50-100 : 20%
100-150 : 30%

How could I formulate to define under which range the said number is fall? Post confirming the range, I require to multiply the percentage with the said number?

Kindly suggest formula....

Thanks & Regards,
Chintan Trivedi

 

[Faseeh]

Hi Chintan,

You hath overlapping ranges in your table, i.e. 50 could be found in first tier as well as in second one, if your data is like 0-50, 51-99,100-150 then following will work:

=LOOKUP(F15,{0,50,100},{0.1,0.2,0.3})

 

[Ravi Verma]

Chintan,
Please upload a sample file with desired output
Regards

 

[SirJB7]

Hi, Chintan Trivedi!
Adding my two cents to Faseeh's solution, if either your ranges/percentages change frequently or their number is much greater then you may want to build a 2 column table (Range up to, Percentage) and then use a formula like this:
=VLOOKUP(A1,Sheet2!A:B,2,TRUE)
assuming that you place the related table in columns A:B of worksheet Sheet2.
Regards!

 

[Chintan Trivedi]

Please find attached the file for your reference. Kindly suggest the formula (if possible with logic so that I can understand and apply to other area as well)

 

[kchiba]

Hi Chintan,

please check attached file,

regards

kanti

 

[Chintan Trivedi]

Hi Kchiba,

Could you suggest any other formula with the logic Faseeh and sirJB7.

I would request Faseeh and SirJB7 to explain the logic of the formula.

 

[SirJB7]

Hi, Chintan Trivedi!
Try this in E10:
=BUSCARV(C10;B5: D8;3;VERDADERO) -----> in english: =VLOOKUP(C10,B5: D8,3,TRUE)
Regards!
PS: Please adjust ranges inner spaces thanks to : D :D