Formulas

[Guest]

Howdy
I have a price list of items in a spreadsheet format. There are roughly 2000 rows of items. Column A is the ITEM NAME. Column B is the MSRP. Column C is one of 25 different discount codes.(DIS1, DIS2, DIS3, etc.). I need to create a formula that calculates my cost for each item. Unfortunatly the Nested IF fomula is limited to 7. The formula needs to first look at the discount code in Column 2(1 of 25), figure out the discount from a table, then subtract that percentage from the MSRP in Column A

Any ideas

Thank you.

 

[William]

Hi Boomer

1) In Column G, create a unique list of all the discount codes you use in
column C and alongside each cell, (in column H) enter the corresponding
discount rate.

2) Select all the cells you have just input in Columns G and H
and name that range "discounts".

3) In cell D2, enter the following formula (one line if text wraps), and
copy down as far as you need.
=IF(ISERROR(VLOOKUP(C2,discounts,2,FALSE)),B2,(1-VLOOKUP(C2,discounts,2,FALS
E))*B2)

--
XL2002
Regards

William

(e-mail address removed)

| Howdy,
| I have a price list of items in a spreadsheet format. There are roughly
2000 rows of items. Column A is the ITEM NAME. Column B is the MSRP.
Column C is one of 25 different discount codes.(DIS1, DIS2, DIS3, etc.). I
need to create a formula that calculates my cost for each item.
Unfortunatly the Nested IF fomula is limited to 7. The formula needs to
first look at the discount code in Column 2(1 of 25), figure out the
discount from a table, then subtract that percentage from the MSRP in Column
A.
|
| Any ideas?
|
| Thank you.