Gruss Forum • View topic - Excel Question

by **one cool dog** » Tue Aug 28, 2007 8:49 pm

I have Raceform Interactive and export data to a spreadsheet.

In the text file that is created, I have asked for SPs in fractions.

I viewed the file in Excel and sorted the data to just include winners (so it removed all horses that did not win)

This removes all the horses that lost, so numbering is something like 1,3,4,9, 21 etc

At the bottom of the column where the fractions are I did the following formula (sorry I am only learning)

=SUM(D2:D265)

I get a total that does not make any sense. Although I believe, and tested, that the total is correct if I added all of the cells together without the ones missing.

How can I get it to add the remaining cells without it adding the full amount together, or am I doing a completely stupid way?

by **Ian** » Tue Aug 28, 2007 9:08 pm

Change the formula to =SUBTOTAL(9,D2:D265)

However Excel often treats fraction as dates eg 15/8 becomes the 15th of August.
If it hasn'y done this then some of the cells may be text so it won't sum them correctly.

It is generally better to use decimal odds in Excel.

by **one cool dog** » Tue Aug 28, 2007 9:32 pm

Hi Ian

Many thanks for that.

I am guessing the bit about subtotal is used instead of sum when there are gaps in the numbers ?

My only other question for now

is why do I add 9 comma before the range? I am using a Teach Yourself Visually book - which I highly recommend for newbies - but not seen a reference to that use of 9 comma.

by **one cool dog** » Tue Aug 28, 2007 9:49 pm

I should say, as well, is it all was 9 comma, or other numbers used?

by **Ian** » Wed Aug 29, 2007 8:11 am

The "9" specifies what type of subtotal it is. For instance, "1" is the average of the visibe cells.

by **one cool dog** » Wed Aug 29, 2007 2:53 pm

Ian wrote:The "9" specifies what type of subtotal it is. For instance, "1" is the average of the visibe cells.

Hi Ian

Many thanks for that. May I ask just one conlcuding question?

=SUBTOTAL(1, A12:A25) is the average of those cells?

=SUBTOTAL(9, A12:A25) is the addition of all those cells?

If that is correct, can you tell me what 2,3,4,5,6,7,8,10 etc equate to or, better still, give me a link to look at. I just tried googling it and found zip!

Cheers

by **GeorgeUK** » Wed Aug 29, 2007 3:25 pm

http://www.ozgrid.com/Excel/excel-subtotal-function.htm :wink:

by **one cool dog** » Wed Aug 29, 2007 5:45 pm

Brilliant, does the job perfectly.

Cheers

Excel Question - Beginners help

Excel Question - Beginners help

Who is online

Sports betting software from Gruss Software