Selecting the correct number from a range of cells
 

Hi - hope someone can help with this.

I'm creating an order form in Excel, and would like to have a discount
schedule built in - the higher the list price, the higher the discount. In my
order totals section at the bottom of the page, I have a cell for List Price,
a cell for Discount, and a cell for Net Price.

Further over to the right I have the discount ranges entered into separate
cells (these will be hidden when it is completed so the customer can't
see/change it). Column AA is the low dollar value for the range, column AB is
the high dollar value or the range, and AC is the applicable discount for
that range.

Column AA Column AB Column AC
$0 $25,000 0%
$25,001 $60,000 5%
$60,001 $110,000 10%
$110,001 $275,000 15%
$275,001 $750,000 20%
$750,001 and up 25%

So basically, if the list price is between $0 and $25,000 then the discount
is 0%. If the List price is between $25,001 and $60,000 then the discount is
5%, etc... What I'm trying to do is have the Discount cell in my totals
section automatically insert the correct discount % depending on what the
value in the List Price cell is.

My knowledge of nesting IF and OR statements is pretty basic (which is what I
am assuming is needed). I've tried a few things but couldn't get it working.
I've also done some searching around here but couldn't see anything that
covers this situation.

Any help would be appreciated.
Thx, Phil

--
Message posted via http://www.officekb.com

Selecting the correct number from a range of cells
 

With your data table in AA1:AC6, try this:

For a List Price in A1

The discount would be
B1: =VLOOKUP(A1,$AA$1:$AC$6,3,1)*A1

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"pajones via OfficeKB.com" wrote:

Hi - hope someone can help with this.

I'm creating an order form in Excel, and would like to have a discount
schedule built in - the higher the list price, the higher the discount. In my
order totals section at the bottom of the page, I have a cell for List Price,
a cell for Discount, and a cell for Net Price.

Further over to the right I have the discount ranges entered into separate
cells (these will be hidden when it is completed so the customer can't
see/change it). Column AA is the low dollar value for the range, column AB is
the high dollar value or the range, and AC is the applicable discount for
that range.

Column AA Column AB Column AC
$0 $25,000 0%
$25,001 $60,000 5%
$60,001 $110,000 10%
$110,001 $275,000 15%
$275,001 $750,000 20%
$750,001 and up 25%

So basically, if the list price is between $0 and $25,000 then the discount
is 0%. If the List price is between $25,001 and $60,000 then the discount is
5%, etc... What I'm trying to do is have the Discount cell in my totals
section automatically insert the correct discount % depending on what the
value in the List Price cell is.

My knowledge of nesting IF and OR statements is pretty basic (which is what I
am assuming is needed). I've tried a few things but couldn't get it working.
I've also done some searching around here but couldn't see anything that
covers this situation.

Any help would be appreciated.
Thx, Phil

--
Message posted via http://www.officekb.com

Selecting the correct number from a range of cells
 

You can use the Look up function.
Put this formula in the cell where you want the percent to display.

Let's say cell D1 has the value you want to look up. You tell it to look in
columns AA9:AA14 to find a matching value and in AC9:AC14 to find the percent.

=LOOKUP(D1,AA9:AA14,AC9:AC14)

This is much easier than nested if statements.


"pajones via OfficeKB.com" wrote:

Hi - hope someone can help with this.

I'm creating an order form in Excel, and would like to have a discount
schedule built in - the higher the list price, the higher the discount. In my
order totals section at the bottom of the page, I have a cell for List Price,
a cell for Discount, and a cell for Net Price.

Further over to the right I have the discount ranges entered into separate
cells (these will be hidden when it is completed so the customer can't
see/change it). Column AA is the low dollar value for the range, column AB is
the high dollar value or the range, and AC is the applicable discount for
that range.

Column AA Column AB Column AC
$0 $25,000 0%
$25,001 $60,000 5%
$60,001 $110,000 10%
$110,001 $275,000 15%
$275,001 $750,000 20%
$750,001 and up 25%

So basically, if the list price is between $0 and $25,000 then the discount
is 0%. If the List price is between $25,001 and $60,000 then the discount is
5%, etc... What I'm trying to do is have the Discount cell in my totals
section automatically insert the correct discount % depending on what the
value in the List Price cell is.

My knowledge of nesting IF and OR statements is pretty basic (which is what I
am assuming is needed). I've tried a few things but couldn't get it working.
I've also done some searching around here but couldn't see anything that
covers this situation.

Any help would be appreciated.
Thx, Phil

--
Message posted via http://www.officekb.com

Selecting the correct number from a range of cells
 

=VLOOKUP(price list,AA2:AB7,3,1)

"pajones via OfficeKB.com" wrote:

Hi - hope someone can help with this.

I'm creating an order form in Excel, and would like to have a discount
schedule built in - the higher the list price, the higher the discount. In my
order totals section at the bottom of the page, I have a cell for List Price,
a cell for Discount, and a cell for Net Price.

Further over to the right I have the discount ranges entered into separate
cells (these will be hidden when it is completed so the customer can't
see/change it). Column AA is the low dollar value for the range, column AB is
the high dollar value or the range, and AC is the applicable discount for
that range.

Column AA Column AB Column AC
$0 $25,000 0%
$25,001 $60,000 5%
$60,001 $110,000 10%
$110,001 $275,000 15%
$275,001 $750,000 20%
$750,001 and up 25%

So basically, if the list price is between $0 and $25,000 then the discount
is 0%. If the List price is between $25,001 and $60,000 then the discount is
5%, etc... What I'm trying to do is have the Discount cell in my totals
section automatically insert the correct discount % depending on what the
value in the List Price cell is.

My knowledge of nesting IF and OR statements is pretty basic (which is what I
am assuming is needed). I've tried a few things but couldn't get it working.
I've also done some searching around here but couldn't see anything that
covers this situation.

Any help would be appreciated.
Thx, Phil

--
Message posted via http://www.officekb.com

Selecting the correct number from a range of cells
 

Thanks to all for your quick replies - this way seems to be working the best.
Appreciate the help from all who answered!
Phil

Office Helper wrote:
You can use the Look up function.
Put this formula in the cell where you want the percent to display.

Let's say cell D1 has the value you want to look up. You tell it to look in
columns AA9:AA14 to find a matching value and in AC9:AC14 to find the percent.

=LOOKUP(D1,AA9:AA14,AC9:AC14)

This is much easier than nested if statements.

Hi - hope someone can help with this.

[quoted text clipped - 30 lines]
Any help would be appreciated.
Thx, Phil

--
Message posted via http://www.officekb.com