Excel Help

[Benny]

I'm going to use excel to help with my class and weight ratings. My problem is working out the formula for the following.

CLASS RATING-BEATEN MARGIN+WEIGHT=RATING

The headings for my spreadsheet are:

Name of Horse...Jockey...Class Rating...Beaten Margin...Weight...Rating.

Love Street......J. Bloggs....56.................4.9................56.5....

Benny

[Chrome Prince]

I'm going to use excel to help with my class and weight ratings. My problem is working out the formula for the following.

CLASS RATING-BEATEN MARGIN+WEIGHT=RATING

The headings for my spreadsheet are:

Name of Horse...Jockey...Class Rating...Beaten Margin...Weight...Rating.

Love Street......J. Bloggs....56.................4.9................56.5....

Benny

If your headings are in row 1 and your data starts in row 2.....

In cell F2 (your rating column) type in =(C2-D2)+E2

Copy this cell and paste it all the way down the column, as far as you have data.

HTH.

[Benny]

I forgot. CR-BM*1.5+wt-lim wt=Rating.

Benny

[Zlotti]

Not sure if this is what you want Benny

[Benny]

It's a starting point.

Name of Horse...CR...BM...Wt Carr...Limit Wt...Rating.

Now the formula I came up with is

=(c3-d3*1.5)+e3-f3

Now how do I set it up so that I don't have to enter the formula each time. I tried copy and paste but it didn't work. I must be doing something wrong.

Benny

[Zlotti]

It's a starting point.

Name of Horse...CR...BM...Wt Carr...Limit Wt...Rating.

Now the formula I came up with is

=(c3-d3*1.5)+e3-f3

Now how do I set it up so that I don't have to enter the formula each time. I tried copy and paste but it didn't work. I must be doing something wrong.

Benny
Send me the file and I will sort it out retireme@mailup.net I'll also put some comment so you know how I did it. It's the best I can offer...

[Benny]

Your right except you have to subtract the weight carried from the limit weight.

Benny

[Zlotti]

No Probs

[monkeyinjapan]

Now how do I set it up so that I don't have to enter the formula each time. I tried copy and paste but it didn't work. I must be doing something wrong.

Benny


Hi Benny,

I'm not trying to be rude, but I hope you're not going to bet a lot of money based on your spreadsheet at this early stage. It's easy to make a small mistake with a formula that buggers up the whole sheet, and if you're just starting out with the software (as you obviously are) I would suggest constant checking with pen and paper before you are confident of the results.

Monkeyinjapan

[DR RON]

While on the subject could somebody please help me with the following problem.

In a spreadsheet in cell A1 I have a figure, in cell A2 I want to divide the figure in A1 by 11.5 and then round up or round down the answer to the nearest .25
for example 52.4322 i want to become 52.5 52.8976 I want to become 53 52.2134 I want to become 52.25 ecetera . could some body please supply the formula I need for A2 or does the rounding need to be done in the next cell ?

Thanks

[Chrome Prince]

While on the subject could somebody please help me with the following problem.

In a spreadsheet in cell A1 I have a figure, in cell A2 I want to divide the figure in A1 by 11.5 and then round up or round down the answer to the nearest .25
for example 52.4322 i want to become 52.5 52.8976 I want to become 53 52.2134 I want to become 52.25 ecetera . could some body please supply the formula I need for A2 or does the rounding need to be done in the next cell ?

Thanks

Hi Doc,

Format cell A2 (or any others used in your weight answer) as number with two decimal places.

Then type into cell A2
=MROUND(A1/11.5,0.25)

I have only run it on a few examples...it should work fine.

If you get an answer which is #VALUE!, then make sure you have all the addins installed for Excel.

[DR RON]

Thanks Chrome, it works just the way I wanted.

[Benny]

I'm using the spreadsheet for my daily racing. But the formula i have posted and the in the attachment don't work. They are different.

I'm sorry if i didn't explain my request properly.

Benny

[DR RON]

Chrome, if your out there somewere I have one further prob. Now that I have my final rating figure running down column f on my spreadsheet in TAB no order,I now wish to subtract the final figure for each horse from the highest figure in column F so that in the cells in column G I will have the difference in KGs from the highest rater if you know what I mean. The thing is that I would like to keep them in TAB order as well. Ive tried the help function but cant seem to find exactly what Im looking for.

[Heavyweight]

Hi Dr Ron,

If your ratings are in cells F1 down to F24, try typing the following into G1:

=(MAX(F$1:F$24))-F1

Then drag the formula down to G24.

Good luck.

Cheers,

H.

[DR RON]

Thanks for that H I'll give it a try.

[DR RON]

Heavyweight, didnt quite work , but I messed around a little and got what I was after thanks, You put me on the right track.

One last problem now, I now have the margins in kgs in column H, so in the cells next to them in column i I need the formula to say that if H1=0 then 100 points, if H1=0.5 then 90 points if you get my drift.

[davez]

dr ron, the easiest way probably is to use "lookup" functions, so in an unused area of the worksheet, for example at AA1 enter 0, AA2 enter .05 & so on, in cell AB1 enter 100, AB2 enter 90 & so on. then create a lookup function in cell I1 which will look something like this -

=LOOKUP(H1,AA$1:AA$11,AB$1:AB$11) & copy it down to cell I2 & so on.

basically it looks at the value in cell H1, finds that value in the cell range AA1 to AA11 & reports the value of the neighbouring cell in the range AB1 to AB11.

note that the ranges AA1 to AA11 & AB1 to AB11 will vary depending on how large your kg & points list is.

if the above makes no sense you are welcome to email me your spreadsheet & i will setup for you.

mango_madness@bigpond.com

[DR RON]

Thanks Davez, suprisingly it does make sense to me as it is exactly what I wanted. I will put it in and see how it goes. If it dosent work I will take you up on your kind offer.

[DR RON]

Works a treat, thanks Davez, thanks to all who have helped me, my spreadsheet is now up and running. Not the tidiest piece of work, but it does the job i want. it will certainly save me the pen and paper work.