Excel eBay Fee Calculator Formula

[rdonovan1]

How do I create a formula in Microsoft Excel 2010 that will take a monetary amount like a price from a cel, calculate the first $50 dollars at 11% and then take the remaining amount between $50.01 to $1,000.00 and calculate that at the rate of 6% and then take both answers, add them together and return them to a cell.

I have been trying to figure out how to get Excel to do this, but so far have had no luck at all. If anyone can tell me how to create a formula that will do this and can even given me some examples I'd appreciate it.

 

[radfordc]

Try
=IF(E5<50,E5*1.11,(E5-50)*1.06+55.5)
assume amount is in cell E5

 

[MickG]

Perhaps:-

=IF(A1<=50,A1*0.11,(A1-50)*0.06+(50*0.11))

 

[Skybluekid]

try this

Excel Workbook
	A	B	C	D
2
3		Cost	$ 1,000.00
4
5		tax	$ 62.50
6
Sheet1

 

[rdonovan1]

I would like to thank both MickG and Radfordc. Both of the formula's that you provided me with worked exceptionally well with very little tweaking and I greatly appreciate it. Skybluekid's formula however did not produce the correct results. All that it returned was a value of false.

The other two formula's did return the value's that I needed. MickG's formula gave me the final value fee to the penny. That greatly helps me to know as to how much the final value fee might be. Radforc's formula gave me the total price with the final value fee included and that helps greatly as well.

I'm starting out with those two formula's because I doubt that anything that I try to sell on eBay will be over $1,000.00. eBay does add in a third contingency and that is that in addition to the first two tiers they will charge an additional 2% on anything $1,000.01 and over. Creating a formula that would take that into consideration would also be helpful.

They also tend to get a little funny with the category as well. They start out at 7% for electronics, for clothing shoes and accessories they charge 10%, and for books, DVD's, Movies, music, and video games they start at 13%.

Most of the stuff that I plan to sell at the moment will be in the 11% category so the formula's that Mick and Radford provided me with should work just fine. If I can elaborate on it all by creating formula's or Excel modules that will do all this, then that would be great because the easier and more user friendly I can make it, then the better. The whole idea here is to let the computer do most of the work since that's what it's best at.

Just thought that I would take the time to say thank you to both Mick and Radford for the formula's that you provided me with. I very much appreciate it.

 

[shg]

Another way:

       ---A---- ---B----
   1     Amt      Fee   
   2                    
   3        $0       11%
   4       $50        6%
   5    $1,000        2%
   6                    
   7     Amt      Fee   
   8       $50    $5.50 
   9      $100    $8.50 
  10      $200   $14.50 
  11      $500   $32.50 
  12    $1,000   $62.50 
  13    $2,000   $82.50 
  14    $5,000  $142.50 
  15   $10,000  $242.50

The formula in C8 and down is

=SUMPRODUCT((A8>$A$3:$A$5) * (A8-$A$3:$A$5) * ($B$3:$B$5-$B$2:$B$4))

The blanks in row 2 are necessary.

 

[SQUIDD]

this also

=(50*0.11)+(A1-50)*0.06

 

[barry houdini]

this also

=(50*0.11)+(A1-50)*0.06

....but I don't think that gives the correct result if A1 < 50

Here's another option for your original question

=A1*6%+MIN(50,N(A1))*5%

or to accommodate values over 1000 that can be

=A1*2%+MIN(1000,N(A1))*4%+MIN(50,N(A1))*5%

4% and 5% being the difference between 2% and 6% & 6% and 11% respectively

 

[SQUIDD]

barry, you are right, my mistake.

This would be correct though i think.

=IF(A1<50.01,(A1*0.11),(50*0.11)+(A1-50)*0.06)

 

[mikerickson]

=min(a1,50)*.11+max(0,a1-50)*.06