newbie to excel, excel formula help please

http://www.excelforum.com/

cheers gandalf, I have tried them before and their not as clever as people on here, but will post the question too and see who comes up with best way

http://www.techonthenet.com/excel/index.php

General Betting section is the best place on here to ask

Delta is pulling your leg

http://www.techrepublic.com/blog/10things/10-cool-ways-to-use-excels-conditi...

cheers Stow have posted on General betting as well because not sure which section had the brainiest forumites... and presently had no replies on there, will try those links as well ta

how do you get betfair prices into your excel, do you manually input?

lol your asking me something now, the prog uses vba and macro I think and logs me in to lite.betfair and gets them for me and everytime they refresh it compares with original prices so I have an idea of whats being backed or drifting, but I want to have highlighted top 26.9% of runners to lay below say 16.0 that are not in the top 26.9% of my rankings

http://www.contextures.com/xlCondFormat01.html

You might be better off producing an odds forecast to 100% and betting those which were 15-20% better odds and laying those that were 15-20% under your odds forecast. It would very much depend upon the quality of your ratings. I believe Ghengis had a go at this a number of years ago. His ratings were the nuts mind.

so you'll need a column with number of runners in every row of the field and another column to divide ranking number by the field size for what % of the field size each runner ranks, then a further column to ask if % is below 26.9% and price below 16 to return 1 for true and 0 for false and use conditional fomratting to highlight this cell if value is 1.

lol...that sounds a bit complicated for a newbie like me to create,

surely this is a conditional formatting  solution

i'd have a read up on that

i don't really understand what you want highlighted but this will highlight cells based upon a condition you set?

to lay top 26.9% of horses under a variable price in J e.g 15.0 but not in my top 26.9 of ranking

i think i've got monday lethargy, i cannot understand this at all

it would help to see how your spreadsheet is laid out is there a column with race number or time in it

*tuesday even

stridingedge he has all runners in a race in order of rating, say there are 19 runners he wants to lay those in the list if you divide their position by 19 you get less than 26.9 ... in this cae 1-5 as 6/19 is over 26.9 and he wants to lay any of these 5 if their projected odds are 15.0 or lower

sorry other way around those that aren't in his top 5

so a simple conditional format of the odds position/number of runners when odds=

goodyd is there anythin in column AH as the first thing you need to do is put a formula in there to make field size in every row

half my post just disappeared

i need to visualize this, find it hard without seeing the actual data

it doesn't sound complicated

am sure conditional formatting is what he's after

it is ultimately stridingedge but he needs to add formula to make a column read 1 for true and 0 for false

i'll see if i can follow exactly what he wants in a spreadsheet

goodyd, why don't you take a photo of whats on your screen and post it in this thread so we can see the spreadsheet

hi the first vacant cell is BP the number of runners is in column A43 but it changes for every race have uploaded / inserted a screenprint but waiting for aproval for it, out now until tomorrow so will check back then
thanks again for all your help and advice
dave

you read my mind grendel

have you got some marker so the sheet recognizes each race?

ie just having number of runners isn't helpful

need to be able to recognize what data each race covers in the sheet

is there a colums with the race meet,time etc

column A43??? dont you mean two letters? ... a43 is a single cell

does the spreadsheet only contain one race at a time then?

need to see this on the screen

i'm sure i've done far more complicated things than what's being asked for here

but without seeing how the sheet actually looks

impossible to start trying to solve.

does he have a column wth the number of runners?  If so its just a nested if formula ie if rating/columnrunners*100 >25 and if betfair price is less than 15 "1" otherwise "0".  Then format the cell to go red if the value is "1"

if he hasn't dave he needs this

just been fiddling with a sheet

race	runners	bfsp	my rank	actual rank	rank %
runners
19	1	5.7	2	1	5.3
19	2	9.75	3	2	10.5
19	3	25.75	12	12	63.2
19	4	9.85	1	3	15.8
19	5	15.2	4	6	31.6
19	6	41	14	14	73.7
19	7	12.5	7	4	21.1
19	8	17.5	5	8	42.1
19	9	42	15	15	78.9
19	10	23	6	10	52.6
19	11	44	16	16	84.2
19	12	85	18	19	100.0
19	13	44	17	16	84.2
19	14	19	9	9	47.4
19	15	13.5	8	5	26.3
19	16	39	13	13	68.4
19	17	24	11	11	57.9
19	18	73	19	18	94.7
19	19	17	10	7	36.8

from the above

he wants to lay horses with rank % lower 26.9 that are not in his top 5

is this right ?

if i have this right (obviously columns will need  proper references for his sheet formulas


a simple conditional format will apply a colour based on the specified condition(s) being met

in my example h would want to lay numbers 7 and 15 ?

how do you copy from spreadsheet onto here like that stridingedge, i've never got round to figuring out how to do it

you just insert colums in between data and use  a pair of || (shift\) as a divider

have i got the jist of what he wants here now grendel?

if that table is right its easy from there to get to what he wants highlighted

yeah, i'm sure it is easy it's just incorporating it into what his spreadsheet looks like

78	Tom Sawyer
76	Tom Sawyer
75	Danzoe
73	Tom Sawyer
72	Love You Louis
72	Beauty Pageant
71	One Kool Dude
71	Irish Boy
71	One Kool Dude
71	Love You Louis
71	Speedyfix
71	Liberty Ship

aha !!!, thanks

if nothing else, atleast i've learnt sumink new

race	runners	bfsp	my rank	actual rank	rank %	operation
runners
19	1	5.7	2	1	5.3	no bet
19	2	9.75	3	2	10.5	no bet
19	3	25.75	12	12	63.2	no bet
19	4	9.85	1	3	15.8	no bet
19	5	15.2	4	6	31.6	no bet
19	6	41	14	14	73.7	no bet
19	7	12.5	7	4	21.1	lay
19	8	17.5	5	8	42.1	no bet
19	9	42	15	15	78.9	no bet
19	10	23	6	10	52.6	no bet
19	11	44	16	16	84.2	no bet
19	12	85	18	19	100.0	no bet
19	13	44	17	16	84.2	no bet
19	14	19	9	9	47.4	no bet
19	15	13.5	8	5	26.3	lay
19	16	39	13	13	68.4	no bet
19	17	24	11	11	57.9	no bet
19	18	73	19	18	94.7	no bet
19	19	17	10	7	36.8	no bet

i added a nested if and function to meet the criteria specified and got the above table with the 'operation' to be performed on each runner.


you can then easily colour the rows with a simple conditional format

when (operation column ref) = lay choose the format colour

if i've understood it i think this is what he wanted

obviously needs to change for his sheet but it's very easy.

=IF(AND(M3=5, G3

FFS why are my posts being cropped today

if you use either the more than or less than sign in this forum it doesn't submit what comes after it

the nested function that does the working out

=IF(AND(M3=5, G3

how can i post a formula then grendel

took all this trouble to solve this now cannot post the important formula

you could replace the signs with MT and LT and they can be replaced back again if the OP uses them

but put MT and LT in bold or italics

right try again

the nested if and function that works out whether to lay or not is based on 3 values

=if(and(m3 LT=26.9,i3 MT 5, g3 LT=15),"lay","no bet")

where m3 = rank%
i3 = his rank
and g3 = betfair sp

the i3 value requirement is variable according to field size though stridingedge and not fixed at 5 ... this value is determined by the 26.9% and therefore should be taken out

i see so i3 isn't needed

race	runners	bfsp	my rank	actual rank	rank %	my rank%	operation
runners
19	1	5.7	2	1	5.3	10.5	no bet
19	2	9.75	3	2	10.5	15.8	no bet
19	3	25.75	12	12	63.2	63.2	no bet
19	4	9.85	1	3	15.8	5.3	no bet
19	5	15.2	4	6	31.6	21.1	no bet
19	6	41	14	14	73.7	73.7	no bet
19	7	12.5	7	4	21.1	36.8	lay
19	8	17.5	5	8	42.1	26.3	no bet
19	9	42	15	15	78.9	78.9	no bet
19	10	23	6	10	52.6	31.6	no bet
19	11	44	16	16	84.2	84.2	no bet
19	12	85	18	19	100.0	94.7	no bet
19	13	44	17	16	84.2	89.5	no bet
19	14	19	9	9	47.4	47.4	no bet
19	15	13.5	8	5	26.3	42.1	lay
19	16	39	13	13	68.4	68.4	no bet
19	17	24	11	11	57.9	57.9	no bet
19	18	73	19	18	94.7	100.0	no bet
19	19	17	10	7	36.8	52.6	no bet

so now an extra column to show 'my rank %'

conditions that need to be met to lay

rank% is equal to or less than 26.9
my rank is greater than 26.9
bfsp is less than or equal to 15.0

nested function would be

=if(and(m3 LT=26.9, o3 MT 26.9, g3 LT=15),"lay", "no bet")

m3 = rank%
o3 = my rank %
g3 = betfair sp

how to conditional format operation cell alone or entire row with lay selections....

http://www.howtogeek.com/howto/45670/how-to-highlight-a-row-in-excel-using-conditional-formatting/

if the columns are A to H the formula should read

=if(and(F3LT26.9,C3LT15),1,0)

then conditionally format H3:H33 to highlight if value =1

i started from c3 with first runner data

his rank has to be bigger than 26.9% (dependent on number of runners) too grendel

where as the actual rank % (dependent on no of runners) needs to be less than 26.9

he needs the 3 conditions met not 2

anyway goodyd

this should be of some help to you

the conditional formatting is easy once you've applied it a few times

the link i posted should help highlight your selections with colour

how to highlight the whole row with qualifying selections, in the above example 'lay' selections.

in my example the 'operation column' has data from q3 down wards, selections are either no bet or lays.

go to q3 (in my example)(or the first cell in your sheet that has same data!)

click conditional formatting
manage rules
new rule
click on 'use a formula to determine which cells to highlight'
in the formula box type in =$q3="lay" obviously you need to change for your sheets reference
then click format
apply the desired formatting, whether it be fill colour, text colour etc
click ok you should get a preview box to show what formatting will look like
click ok again

you will then see a box with the rule you have assigned, the format and the 'applies to reference'
click on little box to right of sheet reference this will take you back into the sheet
drag this cell to left of sheet and down to cover the whole sheet range
the selection should have a flashing light around it now
press return
click apply

the rows that contain lays should now be highlighted with the format you assigned

as long as your table has the 'lay/no bet' in the last column you should be able to make this work as dragging over all cells to left and down will then always highlight the entire rows you want.

it may look daunting but its very easy once you've done it

hopefully follow these steps and yu will have what you want.

thanks guys,just got back, this is the screenshot which might help I hope.....

you will see that bright green cells are top rated  and green is top 30% and the  pink is bottom rated 30% the darker pink the worse rating/ ranking, I am going to change those to 26.9% to match the rest,( 26.9% is the easiest way to decide number of runners for the lay slections because depending on runners it gives correct number to lay ) I wanted to use column W for the suggested lay bet, next to the back and lay columns but it is part of an array so don't think I can, column J is the lay price and AG is the ranking,just want the appropriate lays highlighted orange even if it has to be using the ranking column AG to do i, I think all it needs is conditional formatting changed to top and bottom 26.9% and the formula applying

I take it that this is a program you have bought or subscribe to and not your own creation as it's far more complicated than any excel spreadsheet I have compiled and certainly not the work of a 'newbie' to excel.  My own opinion is that this is over complicating betting and not something I would try to do but good luck

hi grendel
thanks for your opinion but quite contrary, it is a program I have had developed over the past ten years or so to produce what I personally need,the man who did most of the work on the program is sadly no longer with us and I keep thinking of ways I would like to improve it.
I can do the simplest thing in excel only which is why I am asking for help.
I always think the more input you can have to any racing strategy the better and no way can you over complicate it, the program collects data from many sites and tells me lots of things in a glance, and can be set, adjusted, to produce different results depending on lots of different variables.
Presently I select the backs and lays manually and add the stakes to suit my strategy manually which takes up time which could be better spent doing more studies in relation to each race.
Currntly I have someone working on a database which will incorporate reverse engineering to tweak the settings to achieve what would have been the best settings for the past days racing results and then incorporate them into the settings available next time those identical conditions apply, eg, going,distance,course, betting patterns
but thanks very much for your help Grendel

there is a different set of ratings for each handicap race of the day in similar format to the screenshot, I just scroll down to the relevant race

did you read through the stuff i put on here


as long as you get to a point where your formulas are right and lays/no bets are selected i did say how you can then conditionally format your data

did you read through the stuff i put on here


as long as you get to a point where your formulas are right and lays/no bets are selected i did say how you can then conditionally format your data

just switched to new site to see your sheet

far more in depth than i thought i must admit

what are the colours on there now, have you done these manually?

conditional formatting is easy once you've got to the stage where you are happy with the rules of your sheet and exactly when you want the formatting applied.

a nested if(and) function will easily select the outcome(s) dependant on the results of the tests you have assigned over the data

once the outcome is determined you are ready to conditionally format

hi yes striding read through some of the stuff thanks and the colours are top 26.9% green brightest best and bottom 29% pink darkest being worst,they are done through conditional formatting but wrote in code, I think from what you say that I need ag to have the formula for the conditional formatting as an extra rule to highlight lays

what are you stuck on?

i dont get what you cannot do

you need to be very specific what you actually want and where, obviously i have seen the sheet now but i can't see from the screen shot where everything is, ie the columns needed in a function to determine the bet outcome.

what do you want the sheet to select backs/lays/no bets ?

what exactly do you want highlighted on the sheet, horses name or just the back/lay/no bet column


it's very difficult indeed for someone from afar to try and work on a sheet they have not got in front of them.

how do I get the formula to work on just AG column, when new formatting rule I get no options....told you I was a dummy at this

the first thing is to get the formula right so you dont have to pick the bets manually from the data on the screen, you want a column that tells you what you are to do on each selection?

is this right?

i gather at the moment you are typing in what action to do on your selections by looking at the screen data and applying your rules?

hi striding,
I want the sheet to highlight lay bets
top 26.9 at odds of less than 16.0 ( column J )
and not in top ( top = 1) 26.9% of column AG
the cell to be highlighted orange

the first thing is to get the formula right so you dont have to pick the bets manually from the data on the screen, you want a column that tells you what you are to do on each selection?

is this right?

  yes thats right striding

first thing to do

what are the specific rules of your sheet
when do you want to lay/back or perhaps do nothing

then you can see what columns are needed in the formula to work out your bets

need this info before anything else

got to go out in ten mins but will be back, your help is much appreciated striding
these are the rules I think I need
hi striding,
I want the sheet to highlight lay bets
top 26.9 at odds of less than 16.0 ( column J )
and not in top ( top = 1) 26.9% of column AG
the cell to be highlighted orange

goodyd will have to leave this soon but when you have time


try and leave the following on here for me to look at later, probably this evening.

1.) what are the rules of your sheet

ie i want to back when so and so equals this or that etc

i want to lay when etc etc

i want to not bet when etc etc

then i need to know each column where the data needed for the forumla is


finally where do you want the backs/lays/no bets (only 1 column is needed, a different colour can be applied on each outcome.

try and do this and i'm sure we can get this sorted

at the moment i've not got the whole picture

if i had the sheet in front of me would probably take 10 mins but need everything clearly stated.

i will message you with my email address and can send you the workbook if that will help

ok lay bets only i see

so you just need lay bets identified orange if lay if not leave alone

which column is the lay bet to be put in w?

your %ages are in ag
betfair oidds in j

what column is the other % ratings, ie not yours but the markets rank/runers ?

I wanted to put it in W because it is next to betfair prices but when I try it says part of an array, so look like I might be best with AG

no my rank is AG

you know you bet when your percentages are different to the actual market rank/runners

need to compare the 2 so where is the actual market/no of runners in your sheet?

yes your rank is ag

where is the other percentage figure market rank/no of runners

in my table example i used i had 'my rank' and 'actual rank'

where is actual rank in your data

need this to be able to write the formula in w

no I don't compare I just use my ratings and compare against the odds,
actual market is I that is the latest price,
number of runners is in A43 but that changes with each race

top 26.9 at odds of less than 16.0 ( column J )

so in column j are the betfair odds already sorted so they are less than 16?

or is column j the top 26.9 (actual) and then you need to filter out the odds you want ie less than 16?

not got actual rank just betfair prices which are ranked I suppose
sorry got to go now
thanks once again for all your help, I have pm'd  you with my email if you want the actual book to work with

ok mate

no all the betfair odds are there,
yes column J is actual lay odds and need to filter out the ones below 16.0 then the top priced 26.9%