If statements (of course) {running excel 2003}
 

There has to be an easier way to do this.

I have a commission worksheet. In column A I have the revenue. In
column B I'd like to display the payout.

For instance, if cell A1 is 0-14999, B1 should display A1*0%
if cell A1 is 15-19999, B1 should display A1*10%

and so on, including a total of 7 tiers or variables.

Right now I have a terribly long 'If' statement and it is cumbersome to
make any changes.

I am -okay- at VB, but I know nothing about pivot tables, FYI.

Thank you so much!

Jacob

Create a 2xn table with the values 0,0;15000,10%;etc in say M1:N10

and then use

=VLOOKUP(F1,M1:N10,2,FALSE)

to get the percentage which you multiply by the amount.

--
HTH

Bob Phillips

"jacob" wrote in message
oups.com...
There has to be an easier way to do this.

I have a commission worksheet. In column A I have the revenue. In
column B I'd like to display the payout.

For instance, if cell A1 is 0-14999, B1 should display A1*0%
if cell A1 is 15-19999, B1 should display A1*10%

and so on, including a total of 7 tiers or variables.

Right now I have a terribly long 'If' statement and it is cumbersome to
make any changes.

I am -okay- at VB, but I know nothing about pivot tables, FYI.

Thank you so much!

Jacob

You can use the VLOOKUP function to return the appropriate
percentage.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"jacob" wrote in message
oups.com...
There has to be an easier way to do this.

I have a commission worksheet. In column A I have the revenue.
In
column B I'd like to display the payout.

For instance, if cell A1 is 0-14999, B1 should display A1*0%
if cell A1 is 15-19999, B1 should display A1*10%

and so on, including a total of 7 tiers or variables.

Right now I have a terribly long 'If' statement and it is
cumbersome to
make any changes.

I am -okay- at VB, but I know nothing about pivot tables, FYI.

Thank you so much!

Jacob

Bob,

thank you for the reply. i can't get it to work however.
In the M column, how do I enter the dollar range? 0-15000
or
0;15000
or exactly as you have it below:
0,0;15000?
I've tried it all sorts of ways and all i get is "n/a"

You're saying I need a two column table, first colum with the ranges,
such as
0-15000
15001-20000
20001-30000, etc...

and in the second column (n) i put the corresponding percent:
0%
10%
15%, etc...

correct?

If so, i must be physically entering something in the wrong format.


Bob Phillips wrote:
Create a 2xn table with the values 0,0;15000,10%;etc in say M1:N10

and then use

=VLOOKUP(F1,M1:N10,2,FALSE)

to get the percentage which you multiply by the amount.

--
HTH

Bob Phillips

"jacob" wrote in message
oups.com...
There has to be an easier way to do this.

I have a commission worksheet. In column A I have the revenue. In
column B I'd like to display the payout.

For instance, if cell A1 is 0-14999, B1 should display A1*0%
if cell A1 is 15-19999, B1 should display A1*10%

and so on, including a total of 7 tiers or variables.

Right now I have a terribly long 'If' statement and it is cumbersome to
make any changes.

I am -okay- at VB, but I know nothing about pivot tables, FYI.

Thank you so much!

Jacob

Not quite, I am saying you need a two column table, first column

0
15000
20000
etc,

second column

0%
10%
15%
etc

the first column values are the start point, the second is the percentage to
apply from that start point.

--
HTH

Bob Phillips

"jacob" wrote in message
oups.com...
Bob,

thank you for the reply. i can't get it to work however.
In the M column, how do I enter the dollar range? 0-15000
or
0;15000
or exactly as you have it below:
0,0;15000?
I've tried it all sorts of ways and all i get is "n/a"

You're saying I need a two column table, first colum with the ranges,
such as
0-15000
15001-20000
20001-30000, etc...

and in the second column (n) i put the corresponding percent:
0%
10%
15%, etc...

correct?

If so, i must be physically entering something in the wrong format.


Bob Phillips wrote:
Create a 2xn table with the values 0,0;15000,10%;etc in say M1:N10

and then use

=VLOOKUP(F1,M1:N10,2,FALSE)

to get the percentage which you multiply by the amount.

--
HTH

Bob Phillips

"jacob" wrote in message
oups.com...
There has to be an easier way to do this.

I have a commission worksheet. In column A I have the revenue. In
column B I'd like to display the payout.

For instance, if cell A1 is 0-14999, B1 should display A1*0%
if cell A1 is 15-19999, B1 should display A1*10%

and so on, including a total of 7 tiers or variables.

Right now I have a terribly long 'If' statement and it is cumbersome
to
make any changes.

I am -okay- at VB, but I know nothing about pivot tables, FYI.

Thank you so much!

Jacob