Excel Formula Problem

Bouncy · 20 Jun 2007 at 10:23

Hi, i've made a simple spreadsheet in Excel to help me keep track of how much profit i have made on ebay but wish to create a formula to do the calculations for me but i have no idea how to do this, here is my layout:

A B C D E F G
Product Price Sold Cost Listing PayPal Postage Total
Description Inc. Delivery Price Cost Cost Cost Profit

Item 1 £26.00 £12.00 £0.18 £0.90 £3.89
Item 2 £18.00 £07.50 £0.18 £0.76 £1.89

What i need is for column G to do the Maths for me which is B-C-D-E-F=G

is it possible to do the formula once so every Item i add will get the answer in G without having to click in the box and create the formula each time ?

Many Thanks in Advance

Bouncy · 20 Jun 2007 at 10:25

Grrrrrrrrrrrrrrr i had my table all nicely alligned but when i saved it and viewed it everythings up tight to each other so i hope u can understand what i am trying to do

Gigi · 20 Jun 2007 at 10:45

Silly me, didnt realise you were adding and subtracting additional costs.

You may have to use sub-brackets i.e... =SUM(B+C(C-D)+F)) not sure how to work out your formula as i dont fully understand it, but hope this helps.

Bouncy · 20 Jun 2007 at 10:49

Unfortunately it's a little trickier than that

it's subtraction rather than addition i need and i don't want to be having to creat a new formula for every new product i put in, i'd like 1 formula to work for that entire Column

Gigi · 20 Jun 2007 at 10:57

Should go something like this;

Price Sold - Listing - Paypal - Postage = Total

=SUM(A-B-C-D)

You'll need to amend it to match your colums cos i dont understand this bit
Product Price Sold Cost Listing PayPal Postage Total
Description Inc. Delivery Price Cost Cost Cost Profit

TheKnat · 20 Jun 2007 at 11:16

I think the formula you need is =B2-SUM(C2:F2). So thats the cost of the item minus the sum of all the costs.

In answer to your other question all you need to do is type the forumla in the top G field (G2?) and then select all the cells below it which you want to also have the forumla. Then press ctrl-D to fill down and it will change the formula automatically to calculate it from the correct row.

Bouncy · 20 Jun 2007 at 11:18

TheKnat · 20 Jun 2007 at 11:24

Yeah I thought that's what the columns were meant to be. Just done this in excel myself quickly to test and my above post should do the job for you.

EDIT:

I always get carried away in excel, can't stop myself playing a bit more. Type the following formula in G2 and then fill down 50 cells or more so you don't have to re-do it anytime soon:

=IF(B2="","",B2-SUM(C2:F2))

That way it won't display anything in the Total profit cell until you enter a figure in the price sold. Not 100% needed but it annoys me to see a load of £0 figures running down the screen if the line isnt being used.

Bouncy · 20 Jun 2007 at 11:29

i must be stupid :confused:

i can only get the formula to work 1 at a time and not do the full column ? hmmmm ?? ok here is how i've done my layout if this can help you write my formula, Sorry for being slow at this

A1 to G2 Is my Headings as mentioned above

A4 is my 1st Product Description and G4 is where my profit should go
A9 is my last Product for now and G9 is where my profit should go

does this help you make my formula ?

Many Thanks

TheKnat · 20 Jun 2007 at 11:33

This is what should be in G4 (using my over the top formula):

=IF(B4="","",B4-SUM(C4:F4))

Then just select G4-G50 and press ctrl-D. If that doesnt work look under the insert menu (I think, can't check because I'm using office 2007) and look for the fill option. What you want is to 'fill down' the column and excel should modify each of the new cells it puts the formula into.

What version of excel are you using ?

Bouncy · 20 Jun 2007 at 11:39

i'm using Office 2007 also, typed the formula into G4 and it worked wooo hoooo

Cheers Gonna try your next step n see how i get on

Bouncy · 20 Jun 2007 at 11:42

THANK YOU so much it WORKED !!!!!! God your Good hehehehe now do you Fancy explaining what the hell you had me type into G4 to get that to work? Just incase i need to do this again sometime by myself

TheKnat · 20 Jun 2007 at 11:47

Sure don't mind explaining.

=IF( - start of the IF statement.
B4="", - checks if B4 is empty
"", - this is what goes in G4 if B4 is empty
B4-SUM(C4:F4)) - this is what goes in G4 isn't empty. This forumla takes B4 and minuses the all the charges you have to pay (C4,D4,E4,F4)

The way the IF statement works in excel is IF(condition you want to check,cell value if true,cell value if false)

Hope that helps you out

Bouncy · 20 Jun 2007 at 11:51

Thankyou so much, this was very helpful and much appreciated

nb- · 20 Jun 2007 at 16:43

You might also want to add alternate row colouring to make it easier to read;

1. Select the range of cells you wand to alternate row colour starting with the top left.
2. Go to Format>Conditional Formatting
3. Choose Formula is: and add the formula: =AND(MOD(ROW(),2),COUNTA($A1:$C1)) '// <- Note $A1:$C1 implies checking to see if there is data in the first three columns presuming you started on row 1 and only colouring it if there is.
4. Click Format and then Patterns and choose desired pattern
5. Click OK then OK again

Now as you add data to the table every second row will be colored using the chosen pattern.