Help With Formula For Horse Racing Excel Sheet

I need some help with a formula on an excel sheet i have developed a while ago to work out true odds for each horse in a horse race based on their past statistics. I have already designed the base sheet with all of the formulas and it works fine (rudimentary to experienced excel users I'm sure) but now I want to add in another column that adds additional points to a final score based on the prescribed prepost odds of each horse in a race.

The problem is this.

Based on what odds a horse is prepost I want to add points to the horses final points score

So if the odds are

Less than or equal to 1.50, receive 5 bonus to score
Between 1.51 to 2.50, receive 4 bonus to score
Between 2.51 to 4.00, receive 3 bonus to score
Between 4.01 to 6.00, receive 2 bonus to score
Between 6.01 to 9.00, receive 1 bonus to score
Greater than 9.00 receive 0 bonus to score


So on the sheet attached the odds are written into L17 to L36 down the page for each of the 20 horses in the race

Then AD17 to AD36 down the page is where I want to calculate the bonus points associated to each horses odds

I have attached the sheet, if anyone could help me with the formula to ascertain the bonus for each horse as per their odds it would be greatly appreciated

You can nest IFs, but it's messy to write and messier to maintain. I'd use VLOOKUP() on a table that holds the numerical breaks and bonus points. See here for details.

Hello all.
I have managed to put together a formula for soccer prediction.
The whole idea is based on simply adding the Home results against the Away results of the two teams
I have inserted the external link so as to download the league tables and it works fine.
What I wish to do is to be able to have the spreadsheet work once the fixtures are inserted.
The main idea is that we have two different tables
A (In Sheet 1) The newly refreshed league table and B ( In sheet 2) the fixtures.
The target is to go to the fixtures and see what the formula predicts for each fixture, based on the updated league table.
The formula works if three values for each team playing with each other are calculated.
Home Wins, Draws and Losses for Home Team, and Away Wins Draws and losses for the Away team.
If I ve given you a headache then you know EXACTLY how I feel.....
The problem is that I need a VLOOKUP formula or something to calculate as folows
From the league table to take the Home Wins, the Draws and the losses of the Home Playing team and place them in a row, and for the Away Team the Away Wins the Away Draws and the Away Losses and again put them in a row. From there all I need to do is to add the predicting formula....
Can Anybody help?
I will be obliged.

In cell AD17 type this formula and then copy it down to AD36:
=SI(L17<=1,5;5;SI(Y(L17>1,5;L17<=2,5);4;SI(Y(L17>2,5;L17<=4);3;SI(Y(L17>4;L17<=6);2;SI(Y(L17>6;L17<=9);1;0)))))

My excel program runs in Spanish, if yours run in English substitute "SI" for "IF" and "Y" for "AND". In this formula the "," is the decimal separator, if your decimal separator is the period (.) substitue "," for ".". I used this formula successfully in my Excel 2010 version.

Let me know how your program works! I once did a small excel spreadsheets that was very simple as it simply ranked the horsed according to their last speeds, and once it picked the 6 horses finishing in a race in the correct order!! but that proved to be an statistical anomaly... hehehe.

Best regards,

What are all the abbreviations in the excel spreadsheet for?