Range

[Legacy 69619]

Hola!!

Is there a simpler way in Excel to find a range within defined dollar amounts.

For example: I have a tier table set-up and depending on your sales you get a percentage.

Sales Percentage
$0 - $5,000 5%
$5,000 - $10,000 10%
$10,001 - $15,000 15%

I am using IF statements, and it works, except isn't there a cooler way to compute??

Thx...thx...cheers!
Miss Exel

 

[indiantrix]

The VLOOKUP function should work, using the TRUE argument as the final portion of the formula. There are many, countless, items in this bulletin board on VLOOKUP. /s/ Larry

 

[Peter_SSs]

Miss Exel

For the method suggested by indiantrix, the formula in B2 (copied down) is:
=VLOOKUP(A2,E:F,2)
Note: Columns B, C and F are formatted as percentage.

However, if your ranges are neat multiples like your sample (Sales in multiples of $5,000 and Percentage in multiples of 5%) then you do not need the lookup table in columns E and F. The formula in C2 (copied down) is:
=CEILING(A2/5000,1)*0.05

Mr Excel.xls
	A	B	C	D	E	F	G
1	Sales	% (VLOOKUP)	% (Direct)		Sales	Percentage
2	5,500	10%	10%		1	5%
3	12,000	15%	15%		5,001	10%
4	4,999	5%	5%		10,001	15%
5	5,000	5%	5%		15,001	20%
6	5,001	10%	10%
7
Sales %

 

[shippey121]

you could also use

=CHOOSE(ROUNDUP(A10/5000,0),A10*1.05,A10*1.1,A10*1.15)

where A10 is the value of sales

=CHOOSE(ROUNDUP(A10/5000,0), this part will get the result needed for the CHOOSE function, 1 2 3 and so on,


A10*1.05 this part takes the sales value and adds 5% to the value of sales, if you are wanting to know the actual percatage value you can use

A10*.05 by removing the 1 or alternatively if you are wanting to show the text percentage value use

=CHOOSE(ROUNDUP(A10/5000,0),"5%","10%","15%")

hth

 

[Legacy 69619]

Perfect! Thx you all and have a fab week...
Miss Exel