Need help in Microsoft Excel 2007 (2 things)

[deadman_uk]

Please look at the screenshot below, it shows an eBay spreadsheet I am designing for this year. I use this as I have multiple members of the family using my eBay account (I will duplicate the form for each person when I am done so they have their own grand total figure) I have two problems with it.

1) See the Paypal fee tab? The formula for that is =SUM(D29+E29)*G29+0.2. Unfortunately when a row has not been completed yet, the total will show -£0.20. Considering how many cells I will have by December, that will be a lot of £-0.20 added to the grand total at the top, distorting the true money I have. To get round this, I have added an adjustment column, adding £0.20 back.... there must be a better way than this right? Can I tell Excel to only implement the Paypal fee formula when the Paypal type cell has been filled in?

2) I have the grey table at the top frozen so as I scroll down my spreadsheet, I am still able to see the grey table. This feature however has added some ugly lines to my sheet at the left and top (sort of like frames on a website page). How do I hide these lines?

Thanks in advance!

 

[arkamw]

How about something like:

=if(d29<>"",SUM((D29+E29)*G29+0.2),"")

See if that helps out.

As to the second part, I've never seen a way to get rid of the frame lines, but that doesn't mean there's not a way.

 

[Grentz]

For the first part, just use something like arkamw suggested. Simplified IMO:

=IF(D29>0,SUM(D29+E29)*G29+0.2,0)

For the second question, no, I have never seen a way to hide the freeze pane lines.

 

[deadman_uk]

Thanks guys but neither of them work how I want

I forgot to mention if money is deposited into the bank, it would show up on this spreadsheet (Payment type Bank). If other members of the household wanted to transfer money over to another member, it would be shown on this sheet too. Because of this, you can't have the Paypal fee formula dependant on whether or not D28 has a value. Things that aren't Paypal related will still show the £0.20 Paypal charge. It has to be dependant on the Paypal type box which is G28 (this is a validation list box with two options: 3.4% an 3.9%). I want it like this:

Paypal fee cell instructions
IF Paypal type has a value then
Activate Paypal fee formula
else
end (leave blank or the value of £0.00)

My attempt at pseudo code there...

 

[arkamw]

As I understand, if there is a value is G29, then perform formula as given. Couldn't you just change the IF formula to reflect that?

Using simplified code from above (thanks Grentz - never been good at code simplifications):

=IF(G29>0,SUM(D29+E29)*G29+0.2,0)

You could probably throw an AND in there if you need both fields (say D29 and G29) to be positive.

 

[deadman_uk]

As I understand, if there is a value is G29, then perform formula as given. Couldn't you just change the IF formula to reflect that?

Using simplified code from above (thanks Grentz - never been good at code simplifications):

=IF(G29>0,SUM(D29+E29)*G29+0.2,0)

You could probably throw an AND in there if you need both fields (say D29 and G29) to be positive.

Yes I could and I just did... works perefectly!!. I didn't think >0 would work because I was using a list function... shows how much I know about excel and formulas. I go away with my question answered but I feel a little dumb now

Many thanks all