Execl help (again!)

[wb]

As mentioned in an earlier post, I am tidying up some records, and am wondering how to do 2 things:




1. Column A contains the stake, Column B contains the price, and Column C contains W/L (won or lost)

How do I get column D to contain the result.
i.e. if a cell in column C contains W (won) I want the spreadsheet to multiply the corresponding cells in column A and B (stake and price), but if the cell in Column C contains L (lost) I want the figure in column D to be a minus.

Hope I explained this ok.


-------------------------------------------------------------------------


2. Column A contains the horse name, column B contains the 'type' of bet. (for example A or B bet etc.)

I want to to 'sort' them so that if a horse has 'A' beside it, it will be sorted together in a new Column, likewise with the B bets

Hope this makes sense.

Any takers?
Good rep offered :)

[mathare]

OK.

1)

In column D enter

=if(C1="W",A1*B1,-A1)

That means if C1 is W then D1=A1*B1 (you may need to use A1*(B1-1) if you have Betfair prices and want profit instead of return), else D1=-A1 ie you lose the stake

2) Not sure what you're getting at here. You could use an IF again so...

=if(B1="A",A1,"")

That means if B1 is A then the cell in which you entered the formula will be populated with the name of the horse from A1. You can create similar formulae then for B bets etc.

And a handy tip for both of these:

Use the ISBLANK function to neaten things up. For example

=if(isblank(B1),"",if(B1="A",A1,""))

Then the formula is only applied if cell B1 is not blank

[wb]

ok mat, first part worked great, going to try part 2 now :)

[wb]

Ok mat, sorted it out thanks a million! :)

One more question :yikes: :yikes: :peeky

column A has a list of figures in it, (profit or loss on each selection) This list gets added to daily.

I want to display the result of all the figures in Column A, in Cell B1.

I Can't do a simple auto calc sum, because I Column A keeps getting bigger.

Is there any way of adding every number in column A, despite its size, and will allow more figures to be added to it?

[wb]

In other words, I need the sum of an entire column, no matter how long it is.

[mathare]

In other words, I need the sum of an entire column, no matter how long it is.=SUM(A:A) :)

[wb]

Fantastic. That worked perfect. I'm much indebted to you for all the help. Got all my spreadsheets tidyied (or is it tidyed, - how do you spell it :( )


Thanks a million

[mathare]

Fantastic. That worked perfect. I'm much indebted to you for all the help. Got all my spreadsheets tidyied (or is it tidyed, - how do you spell it :( )Tidied :)

[vegyjones]

Thai Deed