Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Code Question - Calculate eBay Fees Automatically
I'm trying to create a spreadsheet that will help log eBay sales. I want to
set up a formula to figure the fees once an item is sold. I'm hoping to just enter the final sale price and in another cell with the formula in it, figure the eBay Final Value Fees. I've tried a bunch of times and cannot get the code correct. The fees are figured in the following way: 1- Item not sold - No Fee 2. $0.01 €“ $25.00 - 5.25% of the closing value $25.01 €“ $1,000.00 - 5.25% of the initial $25.00 ($1.31), plus 2.75% of the remaining closing value balance ($25.01 to $1,000.00) 3. Over $1,000.01 - 5.25% of the initial $25.00 ($1.31), plus 2.75% of the initial $25.00 - $1,000.00 ($26.81), plus 1.50% of the remaining closing value balance ($1,000.01 - closing value) Is it possible to do all that in just a single formula in one cell? I've tried doing "IF" formulas, but can't seem to get it right. Even if it has to be in a few cell, it's be fine. Any help would be great. Thanks! |
#2
|
|||
|
|||
If I understand you right:
=MIN(A1,25)*5.25%+MIN(975,MAX(A1-25,0))*2.75%+MAX(A1-1000,0)*1.5% HTH. best wishes Harald "shadrach" skrev i melding ... I'm trying to create a spreadsheet that will help log eBay sales. I want to set up a formula to figure the fees once an item is sold. I'm hoping to just enter the final sale price and in another cell with the formula in it, figure the eBay Final Value Fees. I've tried a bunch of times and cannot get the code correct. The fees are figured in the following way: 1- Item not sold - No Fee 2. $0.01 - $25.00 - 5.25% of the closing value $25.01 - $1,000.00 - 5.25% of the initial $25.00 ($1.31), plus 2.75% of the remaining closing value balance ($25.01 to $1,000.00) 3. Over $1,000.01 - 5.25% of the initial $25.00 ($1.31), plus 2.75% of the initial $25.00 - $1,000.00 ($26.81), plus 1.50% of the remaining closing value balance ($1,000.01 - closing value) Is it possible to do all that in just a single formula in one cell? I've tried doing "IF" formulas, but can't seem to get it right. Even if it has to be in a few cell, it's be fine. Any help would be great. Thanks! |
#3
|
|||
|
|||
That's it. Works good. Thanks!!!
"Harald Staff" wrote: If I understand you right: =MIN(A1,25)*5.25%+MIN(975,MAX(A1-25,0))*2.75%+MAX(A1-1000,0)*1.5% HTH. best wishes Harald "shadrach" skrev i melding ... I'm trying to create a spreadsheet that will help log eBay sales. I want to set up a formula to figure the fees once an item is sold. I'm hoping to just enter the final sale price and in another cell with the formula in it, figure the eBay Final Value Fees. I've tried a bunch of times and cannot get the code correct. The fees are figured in the following way: 1- Item not sold - No Fee 2. $0.01 - $25.00 - 5.25% of the closing value $25.01 - $1,000.00 - 5.25% of the initial $25.00 ($1.31), plus 2.75% of the remaining closing value balance ($25.01 to $1,000.00) 3. Over $1,000.01 - 5.25% of the initial $25.00 ($1.31), plus 2.75% of the initial $25.00 - $1,000.00 ($26.81), plus 1.50% of the remaining closing value balance ($1,000.01 - closing value) Is it possible to do all that in just a single formula in one cell? I've tried doing "IF" formulas, but can't seem to get it right. Even if it has to be in a few cell, it's be fine. Any help would be great. Thanks! |
#4
|
|||
|
|||
"shadrach" skrev i melding
... That's it. Works good. Thanks!!! Glad to hear that. Thanks for the feedback. Best wishe Harald |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Code Question - Calculate eBay Fees Automatically
Hi
On a smiliar note I've been trying to amend your formula for Ebay UK, but I just can't do it. Please help: The fee structure on Ebay UK is more complex than it's American counterpart. It is as follows: Item not sold - None £0.01- £29.99 - 5.25% £30.00 - £99.99 - 5.25% of the initial £29.99 (£1.57) plus 3.00% of the remaining closing value £100.00 - £199.00 - 5.25% of the initial £29.99 (£1.57) plus 3.00% of the initial £30.00 - £99.99 (£2.10) plus 2.50% of the remaining closing balance £200.00 - £299.00 - 5.25% of the initial £29.99 (£1.57), plus 3.00% of the initial £30.00 - £99.99 (£2.10), plus 2.50% of the initial £100.00 - £199.99 (£2.50) plus 2.00% of the remaining closing value balance £300.00 - £599.99 - 5.25% of the initial £29.99 (£1.57), plus 3.00% of the initial £30.00 - £99.99 (£2.10), plus 2.50% of the initial £100.00 - £199.99 (£2.50) 2.00% of the initial £200.00 - £299.99 (£2.00) plus 1.50% of the remaining closing value balance £600.00 and over - 5.25% of the initial £29.99 (£1.57), plus 3.00% of the initial £30.00 - £99.99 (£2.10), plus 2.50% of the initial £100.00 - £199.99 (£2.50) plus 2.00% of the initial £200.00 - £299.99 (£2.00) plus 1.50% of the initial £300.00 - £599.99 (£4.50) plus 1.00% of the remaining closing value balance I amended your formula as follows but it doesn't generate the correct figu =MIN(A1,29.99)*5.25%+MIN(70,MAX(A1-29.99,0))*3%+MIN(99.01,MAX(A1-99.99,0))*2.5%+MIN(99.01,MAX(A1-199.99,0))*2.5%+MIN(100,MAX(A1-299.99,0))*2%+MIN(300,MAX(A1-599.99,0))*1.5%+MAX(A1-600,0)*1% "Harald Staff" wrote: "shadrach" skrev i melding ... That's it. Works good. Thanks!!! Glad to hear that. Thanks for the feedback. Best wishe Harald |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Code Question - Calculate eBay Fees Automatically
You can use the technique he
http://www.mcgimpsey.com/excel/variablerate.html In article , freedonian wrote: On a smiliar note I've been trying to amend your formula for Ebay UK, but I just can't do it. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I automatically calculate YTD numbers by changing a date? | Excel Worksheet Functions | |||
How do I make a chart that will automatically calculate my bills . | New Users to Excel | |||
How do I make a chart that will automatically calculate my bills . | New Users to Excel | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions | |||
syntax question - cell references in VB code | New Users to Excel |