Gruss Forum • View topic

by **Castillo** » Tue Mar 28, 2006 2:34 pm

Hi All

I'm developing a spreadsheet to use on Horses when the market goes into Play, part of my formula is :

COUNTIF(F5:F40,"<5.5")<2

Which basically means do not trigger the BACK command if there are more than 2 runners with odds of
5.4 or less, only BACK if there is one runner below these odds. However what I would like to do is instead of the value "<5.5"
I want to replace it with "<F5+3" So it looks at the F5 odds adds 3 and so if F5 was 2.1 BACK if there is one runner below 2.1+3 ie odds of 5.1
I can't seem to get it to work, can the criteria for COUNTIF contain a cell ref or must it always be a number.
Any help will be appreciated...

Cheers
Castillo

by **boycee** » Tue Mar 28, 2006 2:44 pm

Have you tried using brackets?

i.e. <(F5+3)

by **thunderfoot** » Tue Mar 28, 2006 3:17 pm

I think I understand that you want to avoid making a bet if 3 or more Odds are less than 5.50, if so, various ways around this:

1. as you're using formulae add an IF formula to return a '1' in another column if the Odds are over a set level. So you then SUM the 1's, 'n if there's too many of 'em ......!!
2. Try the SMALL statement. i.e. =SMALL(F5:F40,3). The '3' will return the 3rd smallest Odds, and if that's greater than your 5.50 target then ...........!!

Loads of other options, but these are two of the top-of-me-head :wink:

by **Castillo** » Tue Mar 28, 2006 3:37 pm

Not quite, but close. Basically I use the Rank function to put the selections in order depending on Odds available i.e =RANK(F5,F$5:F$40,1)
The Back trigger is only made if the selection meets the odds criteria specified and the RANK value is 1 i.e the leader
With the COUNTIF statement I'm trying to stop the BACK being made if another horse's ODDS are very close to the leaders, I want a gap of at lease 3
between the leader and the next placed horse before the BACK trigger is made. Hence me trying to use "<F5+3"
So if the Horse leading is odds of 4, 2nd placed is 6.9 NO BET, but if 2nd placed is odds of 7.1 then the leading horse will be the only selection Below F5+3 and the bet will be made. Thanks Boycee, tried the brackets didn't seem to work, I think it needs to be in quotation marks?

Thanks
Castillo