Excel Question!

[Win2Win]

May as well use my own forum for a bit of advice.

I always use VBasic for checking systems, but with little racing today I thought I'd have a go at Excel.

I've imported 2 years of results.

Column A - The horses odds. IE. 1.25, 2.0, 0.8...
Column B - Where the horse was placed. IE 1 (1st), 2 ,3 ...

I want the average odds of those placed '1' only, ignoring the losers. How would I do this in nice baby steps :) I have an idea, but don't want to waste time piddling about with it.

[MarcusMel]

Highlight all the data by clicking on upper left corner of the sheet.

Menu bar data/sort option. Use this to put all the wins together.

Then in an empty cell type =average(A1:A200) where A1 is your start cell and A200 your end cell of all the values you wish to average.

[Win2Win]

That seems like cheating to me. but hey if it works :)

Don't know why I didn't think about that, although I'm in the middle of installing a new monitor and other bit.

Cheers

[MarcusMel]

You said you did not want to piddle about :D

[Onlyforfun]

If you want to be flash:

If the positions are in cells A1:A10 and odds in B1:B10, type the formula underneath:

"=SUMIF(A1:A10,"=1",B1:B10)/COUNTIF(A1:A10,"=1")

Saves any sorting which if you are not careful can corrupt you data (lesson learned through painful experience!)

Also has the advantage that you can then sort anyway you like and you keep the average.

If you want the average for seconds just change the "=1" to "=2".

[MarcusMel]

I don't trust that countif to be accurate. Tried using on one of my sheets and got false result because it would not count right.

[Onlyforfun]

It has to be exact so if you use any kind of formula to create the "IF" value, it is liable to go tits up.

All a matter of personal preference. :D

[MarcusMel]

I checked and could find no difference between the cells, it just seemed to decide that the last part of the range did not exist.

[Onlyforfun]

Spooky!