SkzDaLimit
Board Regular
- Joined
- Dec 1, 2002
- Messages
- 54
Greetings to everyone, I come once again to ask for your assistance on a nice head scratching problem I have.
I have a sales spreadsheet I created for eBay that uses macros to make fee calculations. I have had a few requests from people for a version without macros since they contain VBA code and some people are leery of such things.
I want to do this using VLOOKUP but I am not sure how to incorporate the formula into it. It's pretty extensive and uses different %'s and other calculations just to give you a fee.
This is an example of what I have so far......
Excel 2003
In this example, A2 is a drop down using Data Validation from column F. B2 is the price an item sold for and C2 will be the fee when calculated. F3:C10 is where I am going to put the VLOOKUP but how would I integrate the formulas in. Here are the formulas:
The formulas are in the correct order to the appropriate category in column F starting with "All Other Items" and working down.
Many thanks for your help as always!
I have a sales spreadsheet I created for eBay that uses macros to make fee calculations. I have had a few requests from people for a version without macros since they contain VBA code and some people are leery of such things.
I want to do this using VLOOKUP but I am not sure how to incorporate the formula into it. It's pretty extensive and uses different %'s and other calculations just to give you a fee.
This is an example of what I have so far......
| Excel Workbook | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Item Category | Selling Price | eBay Fee | eBay Categories | Formula | ||||
| 2 | $19.99 | ||||||||
| 3 | All Other Items | ||||||||
| 4 | Books & Media | ||||||||
| 5 | Car Electronics | ||||||||
| 6 | Car Parts | ||||||||
| 7 | Clothing | ||||||||
| 8 | Consumer Electronics | ||||||||
| 9 | Regular Auction (No Store) | ||||||||
| 10 | Regular Auction (Store) | ||||||||
Sheet1 | |||||||||
In this example, A2 is a drop down using Data Validation from column F. B2 is the price an item sold for and C2 will be the fee when calculated. F3:C10 is where I am going to put the VLOOKUP but how would I integrate the formulas in. Here are the formulas:
Code:
=IF(C2<=50,C2*11%,IF(C2<=1000,(C2-50)*6%+3.75,(C2-1000)*2%+41.75))
=IF(C2<=50,C2*13%,IF(C2<=1000,(C2-50)*6%+3.75,(C2-1000)*2%+41.75))
=IF(C2<=50,C2*7%,IF(C2<=1000,(C2-50)*6%+3.75,(C2-1000)*2%+41.75))
=IF(C2<=50,C2*10%,IF(C2<=1000,(C2-50)*6%+3.75,(C2-1000)*2%+41.75))
=IF(C2<=50,C2*10%,IF(C2<=1000,(C2-50)*6%+3.75,(C2-1000)*2%+41.75))
=IF(C2<=50,C2*7%,IF(C2<=1000,(C2-50)*6%+3.75,(C2-1000)*2%+41.75))
=SUM(C2*9%)
=IF(C2<=50,C2*7.5%,IF(C2<=1000,(C2-50)*6%+3.75,(C2-1000)*2%+41.75))Many thanks for your help as always!
