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
Joined
Oct 17, 2002
Messages
943
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
Joined
May 28, 2005
Messages
49,407
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 21, 2006
Messages
1,001
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 ;)
 

Forum statistics

Threads
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?

Disable AdBlock

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

Disable AdBlock Plus

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
Top