Range

L

Legacy 69619

Guest
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

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

indiantrix

Well-known Member
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

MrExcel MVP, Moderator
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
ABCDEFG
1Sales% (VLOOKUP)% (Direct)SalesPercentage
25,50010%10%15%
312,00015%15%5,00110%
44,9995%5%10,00115%
55,0005%5%15,00120%
65,00110%10%
7
Sales %

shippey121

Well-known Member
you could also use

Code:
``=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

L

Legacy 69619

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

Replies
13
Views
722
Replies
9
Views
243
Replies
12
Views
2K
Replies
12
Views
340
Replies
1
Views
196

1,136,268
Messages
5,674,734
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back