Re rating columns

**brettv68** · 09-02-2008, 05:53 PM

Good Morning,

I have an interest in horse racing and I use spreadsheets to help with my form study. I have reasonble knowledge generally of excel but am self taught.

What I want to be able to do automatically is to re rate 6 columns (shown with yellow at the top)(columns BA-BF)in a worksheet after scratching occur. I have away of eliminating the ratings out of the 6 columns after scratchings but would like to rerate the columns so the ratings start at 1 and rate up. Now when a zero appears it needs to stay a zero as that means it has no rating but where a blank occurs it would indicate a scratched horse. I want to keep the number of rows without deleting any for the scratched horses so it matches other worksheets I use and also need to keep the horses in race order.Hope this makes sense.

Any pointers or help would be fantastic.

Thanks
Brett

**Paul** · 09-03-2008, 12:07 AM

Brettv68, welcome to ExcelForum.com.

Please take a moment to review our Forum Rules, paying close attention to #8 with regards to cross-posting. If you feel you must cross-post between websites, please be courteous enough to provide links to each post - and definitely post any results in all locations.

There is a link to our Forum Rules in my signature, as well as a post at the top of every sub-forum here at ExcelForum.com.

Thank you.

Cross-post at: http://www.mrexcel.com/forum/showthread.php?t=339494

**brettv68** · 09-03-2008, 12:15 AM

Sorry for that I didn't realise the two were linked and it was only on the recommendation of another member that I posted in the other forum as well.

Won't let it happen again.

Regards
Brett

**ratcat** · 09-03-2008, 10:28 AM

G'day Brett,

I like to ask a few questions

How do you intend to tell the spreadsheet when the horse(s) is scratched within one race.

What determines the rating. Is it one of the six columns or the whole five other columns

Cheers

**brettv68** · 09-03-2008, 04:59 PM

Hi Ratcat

The spreadsheet will have a blank in the column which will indicate it is scratched.

Each of the 6 columns are different ratings so I would like to re rate each of the columns

Cheers
Brett

**ratcat** · 09-06-2008, 08:58 AM

G'day Brett,

Sorry for the slow response.

Try a IF statement.

Example: the col for your scratching is BA and and you want to rank the 'Wizard Panel' (Col BB) and base on Race One, which ranges from rows 3 to 15. And col AD 'Average Prizemoney' is the col we are going to rank for the 'Wizard Panel'

=IF(BA="",0,RANK(AD3,$AD$3:$AD$15))

HTH