We want to share an important update with our Betfair Community users.
Over the coming months, the Betfair Community platform will be winding down. The final active day for the platform will be December 21st. From December 22nd, users will no longer be able to access the platform.
Your discussions, insights, engagement, and, most importantly, feedback have helped shape the platform and guided innovation. Your feedback continues to be important to us, and we will continue to listen via our dedicated feedback channels, as well as through product testing and innovations.
To share your feedback and experience, visit: https://support.betfair.com/app/home
General Betting
Welcome to Live View – Take the tour to learn more
Start Tour
There is currently 1 person viewing this thread.
10 Jun 10
09:05
Joined:
Date Joined:
11 Oct 07
I have a large Excel 2007 spreadsheet (400,000 + rows and columns to HZ) that contains my horse and pedigree ratings. I am having trouble trying to analyse some of the data, which I am sure I should be able to do using COUNTIFS.
In columns AJ through AM, I have the horse name, sire, dam, and dam sire. In column AP I have the horse age, in column BR a rating for each individual run and in column BU an annual master for each horse.
I want to know many horses say, Montjeu as sire, had run as three year olds. I can get how many total runs he had by using COUNTIFS, but not the number of individual runners irrelevant of how many times they ran.
Any help would be greatly appreciated, the formula I use for the total runs is:
=COUNTIFS ($ak$2:$ak$462869, $a$5 (which is the sire), $ap$2:$ap$462869, $a$40(which is the horse age))
This gives me 200 for instance, but this is the total number of times a Montjeu sired three year old ran, not the number of actual runners.
Thanks
In columns AJ through AM, I have the horse name, sire, dam, and dam sire. In column AP I have the horse age, in column BR a rating for each individual run and in column BU an annual master for each horse.
I want to know many horses say, Montjeu as sire, had run as three year olds. I can get how many total runs he had by using COUNTIFS, but not the number of individual runners irrelevant of how many times they ran.
Any help would be greatly appreciated, the formula I use for the total runs is:
=COUNTIFS ($ak$2:$ak$462869, $a$5 (which is the sire), $ap$2:$ap$462869, $a$40(which is the horse age))
This gives me 200 for instance, but this is the total number of times a Montjeu sired three year old ran, not the number of actual runners.
Thanks
Show More
I'm sure someone will come along and answer your question.
But for a 'professional' service with a £1.50/min phoneline and charging 'atleast £30' per report, you'd expect someone within the company to be able to answer this.
But for a 'professional' service with a £1.50/min phoneline and charging 'atleast £30' per report, you'd expect someone within the company to be able to answer this.
CB,
I wouldn't have asked the question if I knew the answer. I don't really get you point.
Would you expect a dairy farm to know how to run a clothes shop?
I wouldn't have asked the question if I knew the answer. I don't really get you point.
Would you expect a dairy farm to know how to run a clothes shop?
With a spreadsheet that large I would use SUBTOTALS in
conjunction with filters. Would be a lot faster than
COUNTIFS or SUMPRODUCTS.
Insert a couple of extra rows at the top of your sheet
Make 1 row above your data a header row
Select the cells in the header row, click data menu, click
filter.
In any cell in Row 1 Enter formula:
=SUBTOTAL(3,A4:A500000)
That is if your data started in row 4 (adjust formula if not)
Now filter away to your hearts content
conjunction with filters. Would be a lot faster than
COUNTIFS or SUMPRODUCTS.
Insert a couple of extra rows at the top of your sheet
Make 1 row above your data a header row
Select the cells in the header row, click data menu, click
filter.
In any cell in Row 1 Enter formula:
=SUBTOTAL(3,A4:A500000)
That is if your data started in row 4 (adjust formula if not)
Now filter away to your hearts content
CMagic,
Thanks for that will try it later today or tomorrow.
Cheers
Thanks for that will try it later today or tomorrow.
Cheers
sounds like a job for a pivot table to me. they are a bit of a pest to get set up, but very handy for drilling down into data sets like yours. use the help section and have a play with a smaller (subset) of your data. very powerful once you get the hang of them
structuredbet is right - it's a pivot table you want.
That data sounds as though it belongs in a db too
That data sounds as though it belongs in a db too
At this stage it maybe best to move the data to mysql or the like. You are using excel as a database and its not really upto the task for large datasets.
Thanks for your replies, which I will have to think about and try.
The idea of creating a database has been thought about, I am loathe for two reasons.
Firstly I have little idea how to create one.
Secondly, in the existing Excel data set up,the data is filtered down by way of "COUNTSIFS" etc. I also filter the sires, dam sires etc. by Data Validation.Those numbers are then analysed using a variety of Deviation methods, probability distributions and impact values. In Excel I know how to do it, apart from the problem currently, as I know the values I want to analyse are in certain cells and I write the formulas I need.
In a database I wouldn't have a clue where to start.
The idea of creating a database has been thought about, I am loathe for two reasons.
Firstly I have little idea how to create one.
Secondly, in the existing Excel data set up,the data is filtered down by way of "COUNTSIFS" etc. I also filter the sires, dam sires etc. by Data Validation.Those numbers are then analysed using a variety of Deviation methods, probability distributions and impact values. In Excel I know how to do it, apart from the problem currently, as I know the values I want to analyse are in certain cells and I write the formulas I need.
In a database I wouldn't have a clue where to start.
Post Your Reply
Please login to post a reply.
Wonder
Instance ID: 13539