Beginner needs help w/first work project

I have created a spreadsheet that looks like below:

Prod # Sold Cost Profit Profit % Sales Pay Rate Sales Pay Amt


I type in the prod#,sold, and cost fields and I've gotten Excel to figure
out and print the correct answer for profit and the profit percent cell. Now
where I have sales pay rate and sales pay amt, I can't remember how to get it
to look at a table and based off of the profit percent, I need it to tell me
how much the sales persons' commission rate for that sale would be. I need
it to give me the % and also the amount that it equals. If I can get some
help on this, I have a step 2 problem to figure out. Thanks very much in
advance for help with this, this project is due Thursday in order to be
presented "working" to the sales staff on Friday morning so I am pretty
desperate for some help. This is my first excel project ever so...thanks!

Hi!

You want some type of Lookup formula to get the Sales Pay
Rate.

Here's one type:

Create a table somewhere on your sheet that lists the %
Profit scale and the corresponding Sales Pay Rate. The
table should be in ascending order if there are ranges
involved. For example:

Profit % Pay Rate %
0 10
5 15
10 20
15 25
20 30
25 35
30 40

Assume that table is in the range G2:H8, G1 and H1 being
headers, Profit % and Pay Rate %.

Then you could use a formula like this:

A1 = Prod #
B1 = Sold
C1 = Profit
D1 = Profit %
E1 = Sales Pay Rate formula:

=IF(D1="","",VLOOKUP(D1,G2:H8,2,1))

Biff

>-----Original Message-----
>I have created a spreadsheet that looks like below:
>
>Prod # Sold Cost Profit Profit % Sales Pay
Rate Sales Pay Amt
>
>
>I type in the prod#,sold, and cost fields and I've gotten
Excel to figure
>out and print the correct answer for profit and the
profit percent cell. Now
>where I have sales pay rate and sales pay amt, I can't
remember how to get it
>to look at a table and based off of the profit percent, I
need it to tell me
>how much the sales persons' commission rate for that sale
would be. I need
>it to give me the % and also the amount that it equals.
If I can get some
>help on this, I have a step 2 problem to figure out.
Thanks very much in
>advance for help with this, this project is due Thursday
in order to be
>presented "working" to the sales staff on Friday morning
so I am pretty
>desperate for some help. This is my first excel project
ever so...thanks!
>.
>

Biff, thanks you so much for your help. I mentioned there is a step 2 to
this problem. Not only do I need for excel to figure out the sales
commission % and the amount that it equals, I have 4 total tables. One for
sales commission, one for the sales mgr (who gets a cut), one for the product
buyer who gets a cut and one for the senior buyer who may also get a cut. So
Excel needs to figure out everyones' cut in the profit...the percentages for
each and the amount it equals for each....is this possible? Thanks again in
advance.

"Biff" wrote:

> Hi!
>
> You want some type of Lookup formula to get the Sales Pay
> Rate.
>
> Here's one type:
>
> Create a table somewhere on your sheet that lists the %
> Profit scale and the corresponding Sales Pay Rate. The
> table should be in ascending order if there are ranges
> involved. For example:
>
> Profit % Pay Rate %
> 0 10
> 5 15
> 10 20
> 15 25
> 20 30
> 25 35
> 30 40
>
> Assume that table is in the range G2:H8, G1 and H1 being
> headers, Profit % and Pay Rate %.
>
> Then you could use a formula like this:
>
> A1 = Prod #
> B1 = Sold
> C1 = Profit
> D1 = Profit %
> E1 = Sales Pay Rate formula:
>
> =IF(D1="","",VLOOKUP(D1,G2:H8,2,1))
>
> Biff
>
> >-----Original Message-----
> >I have created a spreadsheet that looks like below:
> >
> >Prod # Sold Cost Profit Profit % Sales Pay
> Rate Sales Pay Amt
> >
> >
> >I type in the prod#,sold, and cost fields and I've gotten
> Excel to figure
> >out and print the correct answer for profit and the
> profit percent cell. Now
> >where I have sales pay rate and sales pay amt, I can't
> remember how to get it
> >to look at a table and based off of the profit percent, I
> need it to tell me
> >how much the sales persons' commission rate for that sale
> would be. I need
> >it to give me the % and also the amount that it equals.
> If I can get some
> >help on this, I have a step 2 problem to figure out.
> Thanks very much in
> >advance for help with this, this project is due Thursday
> in order to be
> >presented "working" to the sales staff on Friday morning
> so I am pretty
> >desperate for some help. This is my first excel project
> ever so...thanks!
> >.
> >
>

I'm not sure that I understand the problem, but I think you just need to use
the vlookup function 4 times. If you provided more info i could possibly do
the same...


"Buckwheat" wrote:

> Biff, thanks you so much for your help. I mentioned there is a step 2 to
> this problem. Not only do I need for excel to figure out the sales
> commission % and the amount that it equals, I have 4 total tables. One for
> sales commission, one for the sales mgr (who gets a cut), one for the product
> buyer who gets a cut and one for the senior buyer who may also get a cut. So
> Excel needs to figure out everyones' cut in the profit...the percentages for
> each and the amount it equals for each....is this possible? Thanks again in
> advance.
>
> "Biff" wrote:
>
> > Hi!
> >
> > You want some type of Lookup formula to get the Sales Pay
> > Rate.
> >
> > Here's one type:
> >
> > Create a table somewhere on your sheet that lists the %
> > Profit scale and the corresponding Sales Pay Rate. The
> > table should be in ascending order if there are ranges
> > involved. For example:
> >
> > Profit % Pay Rate %
> > 0 10
> > 5 15
> > 10 20
> > 15 25
> > 20 30
> > 25 35
> > 30 40
> >
> > Assume that table is in the range G2:H8, G1 and H1 being
> > headers, Profit % and Pay Rate %.
> >
> > Then you could use a formula like this:
> >
> > A1 = Prod #
> > B1 = Sold
> > C1 = Profit
> > D1 = Profit %
> > E1 = Sales Pay Rate formula:
> >
> > =IF(D1="","",VLOOKUP(D1,G2:H8,2,1))
> >
> > Biff
> >
> > >-----Original Message-----
> > >I have created a spreadsheet that looks like below:
> > >
> > >Prod # Sold Cost Profit Profit % Sales Pay
> > Rate Sales Pay Amt
> > >
> > >
> > >I type in the prod#,sold, and cost fields and I've gotten
> > Excel to figure
> > >out and print the correct answer for profit and the
> > profit percent cell. Now
> > >where I have sales pay rate and sales pay amt, I can't
> > remember how to get it
> > >to look at a table and based off of the profit percent, I
> > need it to tell me
> > >how much the sales persons' commission rate for that sale
> > would be. I need
> > >it to give me the % and also the amount that it equals.
> > If I can get some
> > >help on this, I have a step 2 problem to figure out.
> > Thanks very much in
> > >advance for help with this, this project is due Thursday
> > in order to be
> > >presented "working" to the sales staff on Friday morning
> > so I am pretty
> > >desperate for some help. This is my first excel project
> > ever so...thanks!
> > >.
> > >
> >

Mike,

do you have an email address I can send the file to you to show you more
specifically what I'm trying to do?

Thanks,
Buckwheat

"Mike" wrote:

> I'm not sure that I understand the problem, but I think you just need to use
> the vlookup function 4 times. If you provided more info i could possibly do
> the same...
>
>
> "Buckwheat" wrote:
>
> > Biff, thanks you so much for your help. I mentioned there is a step 2 to
> > this problem. Not only do I need for excel to figure out the sales
> > commission % and the amount that it equals, I have 4 total tables. One for
> > sales commission, one for the sales mgr (who gets a cut), one for the product
> > buyer who gets a cut and one for the senior buyer who may also get a cut. So
> > Excel needs to figure out everyones' cut in the profit...the percentages for
> > each and the amount it equals for each....is this possible? Thanks again in
> > advance.
> >
> > "Biff" wrote:
> >
> > > Hi!
> > >
> > > You want some type of Lookup formula to get the Sales Pay
> > > Rate.
> > >
> > > Here's one type:
> > >
> > > Create a table somewhere on your sheet that lists the %
> > > Profit scale and the corresponding Sales Pay Rate. The
> > > table should be in ascending order if there are ranges
> > > involved. For example:
> > >
> > > Profit % Pay Rate %
> > > 0 10
> > > 5 15
> > > 10 20
> > > 15 25
> > > 20 30
> > > 25 35
> > > 30 40
> > >
> > > Assume that table is in the range G2:H8, G1 and H1 being
> > > headers, Profit % and Pay Rate %.
> > >
> > > Then you could use a formula like this:
> > >
> > > A1 = Prod #
> > > B1 = Sold
> > > C1 = Profit
> > > D1 = Profit %
> > > E1 = Sales Pay Rate formula:
> > >
> > > =IF(D1="","",VLOOKUP(D1,G2:H8,2,1))
> > >
> > > Biff
> > >
> > > >-----Original Message-----
> > > >I have created a spreadsheet that looks like below:
> > > >
> > > >Prod # Sold Cost Profit Profit % Sales Pay
> > > Rate Sales Pay Amt
> > > >
> > > >
> > > >I type in the prod#,sold, and cost fields and I've gotten
> > > Excel to figure
> > > >out and print the correct answer for profit and the
> > > profit percent cell. Now
> > > >where I have sales pay rate and sales pay amt, I can't
> > > remember how to get it
> > > >to look at a table and based off of the profit percent, I
> > > need it to tell me
> > > >how much the sales persons' commission rate for that sale
> > > would be. I need
> > > >it to give me the % and also the amount that it equals.
> > > If I can get some
> > > >help on this, I have a step 2 problem to figure out.
> > > Thanks very much in
> > > >advance for help with this, this project is due Thursday
> > > in order to be
> > > >presented "working" to the sales staff on Friday morning
> > > so I am pretty
> > > >desperate for some help. This is my first excel project
> > > ever so...thanks!
> > > >.
> > > >
> > >

Hi!

You can send me your file and I'll have a look.


Biff
XLHelp<at>comcast<dot>net

>-----Original Message-----
>Mike,
>
>do you have an email address I can send the file to you
to show you more
>specifically what I'm trying to do?
>
>Thanks,
>Buckwheat
>
>"Mike" wrote:
>
>> I'm not sure that I understand the problem, but I think
you just need to use
>> the vlookup function 4 times. If you provided more
info i could possibly do
>> the same...
>>
>>
>> "Buckwheat" wrote:
>>
>> > Biff, thanks you so much for your help. I mentioned
there is a step 2 to
>> > this problem. Not only do I need for excel to figure
out the sales
>> > commission % and the amount that it equals, I have 4
total tables. One for
>> > sales commission, one for the sales mgr (who gets a
cut), one for the product
>> > buyer who gets a cut and one for the senior buyer who
may also get a cut. So
>> > Excel needs to figure out everyones' cut in the
profit...the percentages for
>> > each and the amount it equals for each....is this
possible? Thanks again in
>> > advance.
>> >
>> > "Biff" wrote:
>> >
>> > > Hi!
>> > >
>> > > You want some type of Lookup formula to get the
Sales Pay
>> > > Rate.
>> > >
>> > > Here's one type:
>> > >
>> > > Create a table somewhere on your sheet that lists
the %
>> > > Profit scale and the corresponding Sales Pay Rate.
The
>> > > table should be in ascending order if there are
ranges
>> > > involved. For example:
>> > >
>> > > Profit % Pay Rate %
>> > > 0 10
>> > > 5 15
>> > > 10 20
>> > > 15 25
>> > > 20 30
>> > > 25 35
>> > > 30 40
>> > >
>> > > Assume that table is in the range G2:H8, G1 and H1
being
>> > > headers, Profit % and Pay Rate %.
>> > >
>> > > Then you could use a formula like this:
>> > >
>> > > A1 = Prod #
>> > > B1 = Sold
>> > > C1 = Profit
>> > > D1 = Profit %
>> > > E1 = Sales Pay Rate formula:
>> > >
>> > > =IF(D1="","",VLOOKUP(D1,G2:H8,2,1))
>> > >
>> > > Biff
>> > >
>> > > >-----Original Message-----
>> > > >I have created a spreadsheet that looks like below:
>> > > >
>> > > >Prod # Sold Cost Profit Profit % Sales
Pay
>> > > Rate Sales Pay Amt
>> > > >
>> > > >
>> > > >I type in the prod#,sold, and cost fields and I've
gotten
>> > > Excel to figure
>> > > >out and print the correct answer for profit and
the
>> > > profit percent cell. Now
>> > > >where I have sales pay rate and sales pay amt, I
can't
>> > > remember how to get it
>> > > >to look at a table and based off of the profit
percent, I
>> > > need it to tell me
>> > > >how much the sales persons' commission rate for
that sale
>> > > would be. I need
>> > > >it to give me the % and also the amount that it
equals.
>> > > If I can get some
>> > > >help on this, I have a step 2 problem to figure
out.
>> > > Thanks very much in
>> > > >advance for help with this, this project is due
Thursday
>> > > in order to be
>> > > >presented "working" to the sales staff on Friday
morning
>> > > so I am pretty
>> > > >desperate for some help. This is my first excel
project
>> > > ever so...thanks!
>> > > >.
>> > > >
>> > >
>.
>

Yeah. Its mjmetzger@minnair.com

"Buckwheat" wrote:

> Mike,
>
> do you have an email address I can send the file to you to show you more
> specifically what I'm trying to do?
>
> Thanks,
> Buckwheat
>
> "Mike" wrote:
>
> > I'm not sure that I understand the problem, but I think you just need to use
> > the vlookup function 4 times. If you provided more info i could possibly do
> > the same...
> >
> >
> > "Buckwheat" wrote:
> >
> > > Biff, thanks you so much for your help. I mentioned there is a step 2 to
> > > this problem. Not only do I need for excel to figure out the sales
> > > commission % and the amount that it equals, I have 4 total tables. One for
> > > sales commission, one for the sales mgr (who gets a cut), one for the product
> > > buyer who gets a cut and one for the senior buyer who may also get a cut. So
> > > Excel needs to figure out everyones' cut in the profit...the percentages for
> > > each and the amount it equals for each....is this possible? Thanks again in
> > > advance.
> > >
> > > "Biff" wrote:
> > >
> > > > Hi!
> > > >
> > > > You want some type of Lookup formula to get the Sales Pay
> > > > Rate.
> > > >
> > > > Here's one type:
> > > >
> > > > Create a table somewhere on your sheet that lists the %
> > > > Profit scale and the corresponding Sales Pay Rate. The
> > > > table should be in ascending order if there are ranges
> > > > involved. For example:
> > > >
> > > > Profit % Pay Rate %
> > > > 0 10
> > > > 5 15
> > > > 10 20
> > > > 15 25
> > > > 20 30
> > > > 25 35
> > > > 30 40
> > > >
> > > > Assume that table is in the range G2:H8, G1 and H1 being
> > > > headers, Profit % and Pay Rate %.
> > > >
> > > > Then you could use a formula like this:
> > > >
> > > > A1 = Prod #
> > > > B1 = Sold
> > > > C1 = Profit
> > > > D1 = Profit %
> > > > E1 = Sales Pay Rate formula:
> > > >
> > > > =IF(D1="","",VLOOKUP(D1,G2:H8,2,1))
> > > >
> > > > Biff
> > > >
> > > > >-----Original Message-----
> > > > >I have created a spreadsheet that looks like below:
> > > > >
> > > > >Prod # Sold Cost Profit Profit % Sales Pay
> > > > Rate Sales Pay Amt
> > > > >
> > > > >
> > > > >I type in the prod#,sold, and cost fields and I've gotten
> > > > Excel to figure
> > > > >out and print the correct answer for profit and the
> > > > profit percent cell. Now
> > > > >where I have sales pay rate and sales pay amt, I can't
> > > > remember how to get it
> > > > >to look at a table and based off of the profit percent, I
> > > > need it to tell me
> > > > >how much the sales persons' commission rate for that sale
> > > > would be. I need
> > > > >it to give me the % and also the amount that it equals.
> > > > If I can get some
> > > > >help on this, I have a step 2 problem to figure out.
> > > > Thanks very much in
> > > > >advance for help with this, this project is due Thursday
> > > > in order to be
> > > > >presented "working" to the sales staff on Friday morning
> > > > so I am pretty
> > > > >desperate for some help. This is my first excel project
> > > > ever so...thanks!
> > > > >.
> > > > >
> > > >

Yeah, its mjmetzger@minnair.com


"Buckwheat" wrote:

> Mike,
>
> do you have an email address I can send the file to you to show you more
> specifically what I'm trying to do?
>
> Thanks,
> Buckwheat
>
> "Mike" wrote:
>
> > I'm not sure that I understand the problem, but I think you just need to use
> > the vlookup function 4 times. If you provided more info i could possibly do
> > the same...
> >
> >
> > "Buckwheat" wrote:
> >
> > > Biff, thanks you so much for your help. I mentioned there is a step 2 to
> > > this problem. Not only do I need for excel to figure out the sales
> > > commission % and the amount that it equals, I have 4 total tables. One for
> > > sales commission, one for the sales mgr (who gets a cut), one for the product
> > > buyer who gets a cut and one for the senior buyer who may also get a cut. So
> > > Excel needs to figure out everyones' cut in the profit...the percentages for
> > > each and the amount it equals for each....is this possible? Thanks again in
> > > advance.
> > >
> > > "Biff" wrote:
> > >
> > > > Hi!
> > > >
> > > > You want some type of Lookup formula to get the Sales Pay
> > > > Rate.
> > > >
> > > > Here's one type:
> > > >
> > > > Create a table somewhere on your sheet that lists the %
> > > > Profit scale and the corresponding Sales Pay Rate. The
> > > > table should be in ascending order if there are ranges
> > > > involved. For example:
> > > >
> > > > Profit % Pay Rate %
> > > > 0 10
> > > > 5 15
> > > > 10 20
> > > > 15 25
> > > > 20 30
> > > > 25 35
> > > > 30 40
> > > >
> > > > Assume that table is in the range G2:H8, G1 and H1 being
> > > > headers, Profit % and Pay Rate %.
> > > >
> > > > Then you could use a formula like this:
> > > >
> > > > A1 = Prod #
> > > > B1 = Sold
> > > > C1 = Profit
> > > > D1 = Profit %
> > > > E1 = Sales Pay Rate formula:
> > > >
> > > > =IF(D1="","",VLOOKUP(D1,G2:H8,2,1))
> > > >
> > > > Biff
> > > >
> > > > >-----Original Message-----
> > > > >I have created a spreadsheet that looks like below:
> > > > >
> > > > >Prod # Sold Cost Profit Profit % Sales Pay
> > > > Rate Sales Pay Amt
> > > > >
> > > > >
> > > > >I type in the prod#,sold, and cost fields and I've gotten
> > > > Excel to figure
> > > > >out and print the correct answer for profit and the
> > > > profit percent cell. Now
> > > > >where I have sales pay rate and sales pay amt, I can't
> > > > remember how to get it
> > > > >to look at a table and based off of the profit percent, I
> > > > need it to tell me
> > > > >how much the sales persons' commission rate for that sale
> > > > would be. I need
> > > > >it to give me the % and also the amount that it equals.
> > > > If I can get some
> > > > >help on this, I have a step 2 problem to figure out.
> > > > Thanks very much in
> > > > >advance for help with this, this project is due Thursday
> > > > in order to be
> > > > >presented "working" to the sales staff on Friday morning
> > > > so I am pretty
> > > > >desperate for some help. This is my first excel project
> > > > ever so...thanks!
> > > > >.
> > > > >
> > > >

OK, I got your file.

You need to redo all of your lookup tables.

This will take some time but I'll fix it for you!

Biff

>-----Original Message-----
>Hi!
>
>You can send me your file and I'll have a look.
>
>
>Biff
>XLHelp<at>comcast<dot>net
>
>>-----Original Message-----
>>Mike,
>>
>>do you have an email address I can send the file to you
>to show you more
>>specifically what I'm trying to do?
>>
>>Thanks,
>>Buckwheat
>>
>>"Mike" wrote:
>>
>>> I'm not sure that I understand the problem, but I
think
>you just need to use
>>> the vlookup function 4 times. If you provided more
>info i could possibly do
>>> the same...
>>>
>>>
>>> "Buckwheat" wrote:
>>>
>>> > Biff, thanks you so much for your help. I mentioned
>there is a step 2 to
>>> > this problem. Not only do I need for excel to
figure
>out the sales
>>> > commission % and the amount that it equals, I have 4
>total tables. One for
>>> > sales commission, one for the sales mgr (who gets a
>cut), one for the product
>>> > buyer who gets a cut and one for the senior buyer
who
>may also get a cut. So
>>> > Excel needs to figure out everyones' cut in the
>profit...the percentages for
>>> > each and the amount it equals for each....is this
>possible? Thanks again in
>>> > advance.
>>> >
>>> > "Biff" wrote:
>>> >
>>> > > Hi!
>>> > >
>>> > > You want some type of Lookup formula to get the
>Sales Pay
>>> > > Rate.
>>> > >
>>> > > Here's one type:
>>> > >
>>> > > Create a table somewhere on your sheet that lists
>the %
>>> > > Profit scale and the corresponding Sales Pay Rate.
>The
>>> > > table should be in ascending order if there are
>ranges
>>> > > involved. For example:
>>> > >
>>> > > Profit % Pay Rate %
>>> > > 0 10
>>> > > 5 15
>>> > > 10 20
>>> > > 15 25
>>> > > 20 30
>>> > > 25 35
>>> > > 30 40
>>> > >
>>> > > Assume that table is in the range G2:H8, G1 and H1
>being
>>> > > headers, Profit % and Pay Rate %.
>>> > >
>>> > > Then you could use a formula like this:
>>> > >
>>> > > A1 = Prod #
>>> > > B1 = Sold
>>> > > C1 = Profit
>>> > > D1 = Profit %
>>> > > E1 = Sales Pay Rate formula:
>>> > >
>>> > > =IF(D1="","",VLOOKUP(D1,G2:H8,2,1))
>>> > >
>>> > > Biff
>>> > >
>>> > > >-----Original Message-----
>>> > > >I have created a spreadsheet that looks like
below:
>>> > > >
>>> > > >Prod # Sold Cost Profit Profit %
Sales
>Pay
>>> > > Rate Sales Pay Amt
>>> > > >
>>> > > >
>>> > > >I type in the prod#,sold, and cost fields and
I've
>gotten
>>> > > Excel to figure
>>> > > >out and print the correct answer for profit and
>the
>>> > > profit percent cell. Now
>>> > > >where I have sales pay rate and sales pay amt, I
>can't
>>> > > remember how to get it
>>> > > >to look at a table and based off of the profit
>percent, I
>>> > > need it to tell me
>>> > > >how much the sales persons' commission rate for
>that sale
>>> > > would be. I need
>>> > > >it to give me the % and also the amount that it
>equals.
>>> > > If I can get some
>>> > > >help on this, I have a step 2 problem to figure
>out.
>>> > > Thanks very much in
>>> > > >advance for help with this, this project is due
>Thursday
>>> > > in order to be
>>> > > >presented "working" to the sales staff on Friday
>morning
>>> > > so I am pretty
>>> > > >desperate for some help. This is my first excel
>project
>>> > > ever so...thanks!
>>> > > >.
>>> > > >
>>> > >
>>.
>>
>.
>