Excel formula for playing in-running

[one cool dog]

All the red parts in my last reply were sort of questions.

I did not get the bottom part, or if it had anything to do with the question. I think it might just be an example but just wanted to make sure.

OCD

[one cool dog]

I know it does not seem like it, but I am cracking Excel slowly but surely. I have looked into Goal Seek tonight and Rank and have a grasp of that.

Here is a question. I did test this and could not find the answer (I do keep all answers in a word doc, so they are not wasted)

Say I have a mass of data, say 7000 rows

I auto filter it by winners. This cuts down the data by a lot. So from having all 7000 rows on show I have only the ones in the filtered column that have a 1 in it.

How do I count the remaining cells easily. Not the sum total of all the ones left, but the amount of cells left after a filter?

If I have 7000 cells, my goal is to find out how many won by counting the remaining cells the best/easiest way possible.

Hope that makes sense.

[Captain Sensible]

Not sure about autofilter as I never use it. You could always use COUNTIF. If your winners are identified by a 1 in some column just use

=COUNTIF(A:A,"=1")

Assuming column A contains the data for the winners

[one cool dog]

Thanks for the reply.

That formula was just about correct but it needed adapting a touch. The column was C in mine for the 1s and the following worked.

=COUNTIF(C1:C7000,"1")

The slightly weird bit, although I am fairly sure I know why this is, is that even though the Autofilter has just kept the 1s. If I change the formula to

=COUNTIF(C1:C7000,"2")

I get results as well, which is not the same as the results for the 1.

Cheers again.

[allgreen]

One Cool Dog, all I normally do is just copy a column of 1's adjacent my records then you can easily get a count of filtered records by selecting this column and looking at the 'sum' on the status bar (bottom right of screen)

[one cool dog]

I did my task this way in the end, which does what I was angling towards.

You used the Sort menu on Data by the position column, then the subtotal option on the sorted information.

I can see it on my screen, and it works. Does it sound sensible?

[Captain Sensible]

The slightly weird bit, although I am fairly sure I know why this is, is that even though the Autofilter has just kept the 1s. If I change the formula to

=COUNTIF(C1:C7000,"2")

I get results as well, which is not the same as the results for the 1.

Cheers again.

The auto filter just hides part of the data the formula COUNTIF(C1:C7000,"2") is still looking at the data within c1 to c700. When auto filtering you'll see the cell references haven't changed just some are hidden. Another advantage of using the formula will allow you to set a separate cell to choose between winners and losers. Say winners are 1 and losers 2 you set a cell aside to hold the number 1 or 2 say D1 and alter the formula to

=COUNTIF(C1:C7000,D1)


From there it's easy enough to start setting extra formulas showing you percentages of wins etc and using drop down boxes to show winners losers rather than entering the numbers in.

[one cool dog]

Cheers

As a test I ran that query through again using the above formula and it worked fine. I must have been doing something a bit wrong the first tim.

[mkristensen]

Hi

I try learn about excel, but shall i understand it correct that if i copy this linie =IF(AND(COUNT($T$5:$T$50)<1,$E$2="In Play",H5<=1.70,H5<>""),"BACK","") into Q5 then the trigger will fire a back bet when lay odds comes under or equal to odds 1.70 is that correct.

Still if i am correct, is it possible to make a stopploss on let us say odds 3.00 in case the selection going to lose, if i should make it i might be copy another linie when back bet is matched and look like this =IF(AND(COUNT($T$5:$T$50)<1,$E$2="In Play",H5>=3.00,H5<>""),"lay","") is that totally wrong or is there a easier way round that problem.

Now its time for work, have a nice sunday all around.

Regards

Michael

[remi1remi1]

captain sensible

The formula

=IF(AND(COUNT($T$5:$T$50)<1,$E$2="In Play",F5<=1.7,F5<>""),"BACK","")
captain sensible i have tried this formula at it works fine...especially inrunning i am amazed how many times horses get beaten in the last few strides and prices can go to 1.01 and the horse still looses..I have noticed many times in running prices in coloumn H(the lay coloumn) when 2 horses are challenging for the post prices can be avaiable to lay at less than 1.5 and you get 2 avaiable at this price or less in coloumn H...is it possible to lay them both at say a predetermined odds of less than 2.0 when BOTH these criteria has been matched..thank you..to YOU and GEORGE..FOR ALL YOUR HELP