Excel Sreadsheet Help

[Captain_Wang]

I'm quite inept with excel so you'll have to bare. with me if I've been doing something really dumb.

Okay, I've been trying to create myself a bookkeeping spreadsheet to keep track of my eBay transactions. I've hit a brickwall when it comes to the the fees formula's though. I tried to look around the net first for others examples of this sort of thing but you have to pay for them which sort of defeats the object of me creating my own. Besides I want to learn a bit about this.

I believe the best way of doing this is to use the tables on the right but that's as far as I can get, I know I'll probably have to use VLOOKUP or MATCH I believe but I'd love some help off of one of you guys. I need to figure out a formula for the 'Insertion Fee' and 'Final Value' fee columns using the tables on the right. I think I could figure out the 'Paypal Fee' column for myself if someone can help me with these.

The eBay final value fees table may need me to change some things to make it work I think too. The values in this table below show what's meant to be in the table but I wouldn't know how to go about inputting this.

Thanks heaps to anyone who helps.

Edited October 9, 2006 by mashw

[boogerjones]

Learn the power of VBA! Press Alt+F11 to open the VBA editor. Go to the "Insert" menu and insert a module. Open the module and paste the following code:

Function InsertionFee(ClosingPrice As Range) As Currency
	Select Case ClosingPrice.Value
		Case 0#
			InsertionFee = 0
		Case 0.01 To 0.99
			InsertionFee = 0.15
		Case 1# To 4.99
			InsertionFee = 0.2
		Case 5# To 14.99
			InsertionFee = 0.35
		Case 15# To 29.99
			InsertionFee = 0.75
		Case 30# To 99.99
			InsertionFee = 1.5
		Case Is > 99.99
			InsertionFee = 2
	End Select
End Function

Function FinalValueFee(ClosingPrice As Range) As Currency
	Select Case ClosingPrice.Value
		Case 0
			FinalValueFee = 0
		Case 0.01 To 29.99
			FinalValueFee = 0.0525 * ClosingPrice.Value
		Case 30# To 599.99
			FinalValueFee = 1.57 + 0.0325 * (ClosingPrice.Value - 29.99)
		Case Is > 599.99
			FinalValueFee = 20.1 + 0.0175 * (ClosingPrice.Value - 599.99)
	End Select
End Function

Then, in the "Insertion Fee" column, put the formula "=InsertionFee(F5)" and in the "Final Value Fee" column, put the formula "=FinalValueFee(F5)"

That ought to ****in' do it! Holy ****! VBA custom functions are fun! I've attached a spreadsheet with the formulas already placed.

VBA_Custom_Functions.zipFetching info...

[Captain_Wang]

Wow, thanks for the effort you saved me tonnes of time with that. I got it working with nested IF functions but this is much better. :) Good karma out to you.

[Captain_Wang]

Okay thanks to booger I've got the fees table sorted. I need a hand with something else now though if anyone's got the time.

With reference to the picture; I need the profits table to report the monthly profit for that particular month. I'm really confused how I'd go about this, my main problem with excel is not the formulas or getting my head around things it's just knowing what functions to use in what situation.

I think I'll need the formula to extract somehow the second part of the date in the B column (UK format) to report and tot-up the profits for say 01 meaning January, repoting in cell Q20.

I hope that's clear enough. I don't expect anyone to go to the effort booger did I just need a bit of help figuring out how to go about this.

(P.S I know this could be done ad-hoc at the end of the month's with a SUM formula but I'm trying to automate this as much as possible)

Cheers for any replies.

Edited October 9, 2006 by mashw

[boogerjones]

You could also do the monthly sums with VBA, but I think it would be easier to create a new column that contains just the month and then use the SUMIF function. Note that the month can automatically be extracted from the full date using the MID function.

[S_Herbie]

  boogerjones said:

You could also do the monthly sums with VBA, but I think it would be easier to create a new column that contains just the month and then use the SUMIF function. Note that the month can automatically be extracted from the full date using the MID function.

You don't need to use the MID() function, you could just use the MONTH() funtion, this returns a number (1 - 12) representing the month.

eg cell B5 contains 25/01/2007, in cell A1 enter =MONTH(B1) and the result will be 1.

You could nest this into a VLOOKUP to give the 3 digit code for the month:

=VLOOKUP(MONTH(B5),{1,"Jan";2,"Feb";3,"Mar";4,"Apr";5,"May";6,"Jun";7,"Jul";8,"Aug";9,"Sep";10,"Oct";11,"Nov";12,"Dec"},2,FALSE)

This can then be used with a SUMIF() to build your summary (in Q20 to Q31)...

[Echilon]

I used to have the same setup for tracking my bank accounts, but I switched to Quicken and it's loads easier. Quicken might be able to do what you want but might not offer the reports you need.