Count with two criteria

[SpareBank1]

Hi,
I have a dataset with odds for soccermatches, and results.
I want to count each time a specified odds has won the match, but i cannot find out which formula to use.

For example:

HTeam ATeam Res H-Odds D-Odds A-Odds
1.Team1 Team2 H 1.60 2.10 4.30
2.Team1 Team2 D 1.64 2.30 3.30
3.Team1 Team2 H 1.60 2.60 4.35
4.Team1 Team2 A 3.10 2.80 1.60
5.Team1 Team2 A 1.60 2.10 3.30
6.Team1 Team2 D 1.60 1.60 4.30

HTeam= Home team
ATeam= Away team
Res = Result, Home, Draw, or Away
H-Odds= Home odds
D-Odds= Draw odds
A-Odds= Away odds

Defining my problem:
I want to count the times the odds; 1.60 was the winning odds.
I imagine i need to set up 3 columns: Homewins, Draws, Awaywins.

Kind of like:
=COUNTIF(A1:A50,"1.60"),IF(B1:B50,"H")
like that, only that is not correct

Can anyone help me?

[whallgren]

Please Login or Register  to view this content.

CTRL + Shift + Enter

The array way.

[SpareBank1]

hmm maybe i misunderstood the formula, but it didnt work

Lets say:
The Results (H, D, or A) is displayed from G2 to G381 (G2:G381)
The Home odds is displayed from X2 to X381 (X2:X381)

So if i want to know: How many times did the Home-odds 1.60 win?
Could you please explain more specific like if I were stupid?

Like: How many times does 1.60 appear in X2:X381, when "H" is in G2:G381

What does CTRL+SHIFT+ENTER do?

[SpareBank1]

Come on, can anyone please help me with this? At least tell me if its possible / not possible. It can be others sollutions than counting? Just ways to do it? I'm on a time schedule and some tips would be very very helpful

[DonkeyOte]

Based on the below

Lets say:
The Results (H, D, or A) is displayed from G2 to G381 (G2:G381)
The Home odds is displayed from X2 to X381 (X2:X381)

So if i want to know: How many times did the Home-odds 1.60 win?
Could you please explain more specific like if I were stupid?

Like: How many times does 1.60 appear in X2:X381, when "H" is in G2:G381

Using XL2007

=COUNTIFS(X2:X381;1.60;G2:G381;"H")

[SpareBank1]

Thanx alot for your help, but still i can't get it to work
I'll add the sheet here, and the actual cells are the ones i mentioned X2:X381 and G2:G381, so you can see for yourself I tried your formula but it says the formula contains an error. Tried with both COUNTIFS and COUNTIF (couldn't find COUNTIFS in the formula-list). There is a COUNT, a COUNTA, and a COUNTIF. COUNTA is counting number of blank cells.

Heres the file:
http://www.football-data.co.uk/mmz4281/0910/E0.csv

[DonkeyOte]

OK your profile says you're using XL2007 - if you can't see COUNTIFS I suspect you're actually running an earlier version.

Does the below work ?

=SUMPRODUCT(--(X2:X381=1.60);--(G2:G381="H"))

Going forward please attach files here directly.

[SpareBank1]

OK your profile says you're using XL2007 - if you can't see COUNTIFS I suspect you're actually running an earlier version.

Does the below work ?

=SUMPRODUCT(--(X2:X381=1.60);--(G2:G381="H"))

Going forward please attach files here directly.

Oh huge mistake by me there. I have the "Office 2007" package, so i expected it was Excel2007, but when i check it it says Microsoft Excel 2002.
Sorry about that

I'll attach the file here:

http://www.football-data.co.uk/mmz4281/0809/E0.csv
Odds in: X2:X381 and Results in G2:G381, as in the example above

[JBeaucaire]

I have the "Office 2007" package, so i expected it was Excel2007, but when i check it it says Microsoft Excel 2002

Be sure to edit your profile to match.

[DonkeyOte]

The formula provided previously does what you want - you may need to alter the delimiters per your locale settings.
I had assumed from your profile that you were in Norway and thus would be using ; delimiter - if not adjust my example as necessary.
(note however in your sample file Col X does not contain any 1.60 values so you will get a 0 output).

[SpareBank1]

The formula provided previously does what you want - you may need to alter the delimiters per your locale settings.
I had assumed from your profile that you were in Norway and thus would be using ; delimiter - if not adjust my example as necessary.
(note however in your sample file Col X does not contain any 1.60 values so you will get a 0 output).

Hmm ok sorry mate, my english isn't very good so i don't understand what you mean? I have an english excel version, so 1.60 is in my excel as we say 1,60 in norway. I use a dot instead of a comma in my excel. So in my excel, 1/2 = 0.5 (not 0,5)

Could you please post a formula with english delimiters(?) and i would be very greatful I'm a newbie in Excel, and my english isn't top notch, sorry

Edit: Thanx for the note, but i was just saying the X column to make it simple.
I'm doing the same for
-AA2:AA381
-AD2:AD381
-AG2:AG381
-AJ2:AJ381
-AM2:AM381
-AP2:AP381
-AS2:AS381
-AV2:AV381
-AY2:AY381
It was just to get a formula

[DonkeyOte]

your .csv saved as .xls with formulae attached

[SpareBank1]

your .csv saved as .xls with formulae attached

Ah thanx ALOT DonkeyOte, you just saved my day. ";" = "," in my version but i have to lock ($) the G2:G381, don't I?

Please Login or Register  to view this content.

like this? because the results are only displayed in G2:G381

And so another little question:
I am doing the same thing for all odds [1.60,1.61,1.62...,2.38,2.39.2.40]
Is there away to make excel count +0.01 to the odds, when i copy the formulas, or do i have to punch this in manually, like:

=SUMPRODUCT(--(AD2:AD381=1.60),--($G$2:$G$381="H"))
=SUMPRODUCT(--(AD2:AD381=1.61),--($G$2:$G$381="H"))
=SUMPRODUCT(--(AD2:AD381=1.62),--($G$2:$G$381="H"))
....
=SUMPRODUCT(--(AD2:AD381=2.40),--($G$2:$G$381="H"))

I want it like this, do i have to adjust the odds manually then or is it any way to do it automatically?

I promise I won't bother you guys more after this one

[DonkeyOte]

OK before we go into this be aware that SUMPRODUCT is an expensive formula... if it is your intention to create a row of SUMPRODUCTs for each 0.01 increment (over 48 columns X:BH) your file will grind to a halt.

Are you not able to use a Pivot Table for this ? You won't necessarily get each increment returned to the table (ie 1.60 will only list if frequency > 0) but it will be a lot more efficient...

but to answer your question... yes - reference to Column G should be absolute ... you would by the sounds of it want:

=SUMPRODUCT(--(AD$2:AD$381=1.60),--($G$2:$G$381="H"))

ie lock rows for AD also such that as you copy down the rows remain fixed but as you copy across the column adapts accordingly.

[SpareBank1]

OK before we go into this be aware that SUMPRODUCT is an expensive formula... if it is your intention to create a row of SUMPRODUCTs for each 0.01 increment (over 48 columns X:BH) your file will grind to a halt.

Are you not able to use a Pivot Table for this ? You won't necessarily get each increment returned to the table (ie 1.60 will only list if frequency > 0) but it will be a lot more efficient...

but to answer your question... yes - reference to Column G should be absolute ... you would by the sounds of it want:

=SUMPRODUCT(--(AD$2:AD$381=1.60),--($G$2:$G$381="H"))

ie lock rows for AD also such that as you copy down the rows remain fixed but as you copy across the column adapts accordingly.

Well. I'm not to experienced with this "Pivot table", but as i understand it, you get what you are asking for, but only when the value is >0. The problem is i have 14 more sheets with football results whole the way back to 1993, and i'm going to add these together for the odds 1.60-2.40. To make it easy to add together i need also =0, because some sheets may have values where others don't. ... Or?

In the end i will have a list something like this (this is an example):

Bookmaker: BetOnBet
Odds: 1.60
Used: 643times
Hits: 389 times
Win%Home: XX.X%
Win%Draw: XX.X%
Win%Away: XX.X%
Win%Total: 60.03%
1/1.60 = 63.0%

So my conclusion will be like:
The winning-value for the odds 1.60, Should be 63.0%, but is actually 60.03% at BetOnBet. Which i find interesting.

So I want to do this for all odds from 1.60 to 2.40. From all the bookmakers in the dataset (10 different bookmakers)

This way, and with other statistical measurements like 95% confidence intervals i can conclude things like:
-"I am 95% sure that "2.30" have a 9% bigger winning-value at NordicBet, than BetOnBet."
-"The odds 1.85 at BetKing gives you the biggest winning value overall, +9.6%."
I will also find out what the different bookmakers take in cover for each odds, like in my example, BetOnBet takes 2.97% cover for the 1.60 odds.

So this is what i want in the end, so that is why i want the data for ALL odds in the interval.
Is it any easier way to do this than what i'm already planning?

[DonkeyOte]

Well in theory to get to your final summation what you're planning is not a bad approach per se though I would certainly suggest you ensure your summation calcs on each annual sheet appear in the same place - this will make it easy to do 3D calculations at the end.

What I would suggest given your data is historic (perhaps with exception of current year) look to setup and process the sheets one sheet at a time given:

a) 81 rows * 10 Columns of SUMPRODUCT required

b) each SUMPRODUCT is referencing say 300 rows of data * 2 columns

this is pretty heavy on poor old XL but given you seemingly only need to calculate the sheet once (data static) you could calculate and then override the formulae with the results, no?

You could do likewise using VBA - ie calculating results in memory and returning only results to the worksheet.

[SpareBank1]

Ok, I've worked with it for a while now, but still there is something missing.
I've used the formula which you said, and added it underneath the existing data. from cell A383 and out.
BUT: If you look for example at the Odds "2.40" you can see it's used 157 times, but it haven't won ANY of the 157 times. Thats wrong.
If you see in cell X324 for example, this cell contains the home odds 2.40, and the result (displayed in G324) = "H". STILL this doesn't count as a win..

What have i done wrong? I don't understand this?

[DonkeyOte]

Just looked at this and I'm about to run out of the door...I suspect it's a rounding issue (XL precision)

Change A465 for ex. to

A465: =ROUND(A464+0.1,2)
(ie round to 2 decimals)

O465: =A465
(no point calculating twice)

If you note your COUNTIF in M is not using value in A - if it were you would have noticed the problem - this is a good demo as to why it's always a good idea to keep things consistent - ie anything needing to refer to odds etc should refer to the same cell (don't mix references or approaches as this can lead to inconsistencies).

[SpareBank1]

Please Login or Register  to view this content.

=SUMPRODUCT(--(AD$2:AD$381=1.60),--($G$2:$G$381="H"))

Well i don't understand it either because the formula is right there. I was going crazy over this last night, so today i bought Excel 2007, so i could use

Please Login or Register  to view this content.

=COUNTIFS(X2:X381;1.60;G2:G381;"H")

Instead.
And guess what, i got the same result with this formula, No values for the last odds, approximately 2.30:2.40.

Then as you can see on the file i attached earlier, i didn't write in the odds (like i bolded in the code) instead of writing "=1.60" and so on. I listed the odds in one column and used ie COUNTIFS(X2:X381;=B386;G2:G381;"H") instead so i didnt have to manually write the odds in every formula.

Well, when i changed the formula to manually write the odds, THEN it worked, and now it is correct. So i had to write 1.60 to 2.40 in every formula, the first column manually. Then it worked.

BUT i didn't get any smarter by WHY it worked then because if ie cell A1=1.60
then =A1 , and =1.60 is the exact same. And i can't stand things that are unlogical:P

If you note your COUNTIF in M is not using value in A - if it were you would have noticed the problem - this is a good demo as to why it's always a good idea to keep things consistent - ie anything needing to refer to odds etc should refer to the same cell (don't mix references or approaches as this can lead to inconsistencies).

What did you mean by this? I'm sorry, my english aren't explicit

[DonkeyOte]

The point I was making earlier - to explain why you were getting 0 - is that XL's precision is not exactly as displayed... (unless enforced via Options - ill advised).
As such you can on occasion get miniscule variances which when looking for "matches" causes problems... eg: per your file earlier today (ie before my last post)

A465: =A464+0.01

this displays to 2 decimal points as

2.40 (UK format)

however, if you alter the format to say 14 decimal places (Xl stores to 15 significant digits)

2.39999999999999

So you can see that the number in A465 is not exactly 2.40 meaning when you try your COUNT based on A465 you're returning 0 as in your data set (X) there are no values of 2.39999999999999

So the key then is to ensure your criteria & source range are both operating to the same level of significance, in this case 2 decimals, hence I suggested you encase your formula in A with a ROUND call:

A465: =ROUND(A464+0.01,2)
(replicated in preceding cells also)

This then ensures that A465 is 2.40 exactly (and not 2.3999etc...) and thus matches the significance levels as used in Col X.

For more info. on XL precision see: http://support.microsoft.com/kb/78113

My point that you quoted in prior post was simply that had you used A465 as criteria for both formula in Cols B:L as well as M you would have spotted the issue straight away, eg

B465: =SUMPRODUCT(--($X$2:$X$381=A465),--($G$2:$G$381="H"))
--> returned 0 as did all others though to K465

whereas

L465: =COUNTIF($X$2:$BA$381,2.40)
--> return 157

The reason: L was using 2.40 whereas B etc were using value in A - ie 2.39999etc... had you used A465 as criteria for both functions you would probably have realised something was up sooner (and possibly found the cause)

[SpareBank1]

Ahh, i understand what you meant now, I tried to add 14 decimals and it turned X.99999999. Puh, i wasn't going crazy after all! Again, thanx alot for your help!

With sollutions on old problems, new ones pops up unfortunately..
So far I've added sheets from Premier League season 2005/2006 - season 2008/2009.

Ive made a sheet called "TOTAL STATS" to sum up the other sheets statistics, to have something easier to go after. Well when I add together the "WIN TOTAL in cell "C4", i Get the Value "0", which is false.

If i set C4

Please Login or Register  to view this content.

='PL 2006-2007'!AP386
I get the Value "0", but i can clearly see that the same cell in the "PL 2006-2007" sheet says "41".. Is 0 = 41 these days or am i still sane?

Og the file is now 1.64MB and exceeds the forum limit.
http://rapidshare.com/files/28989902..._2009.xls.html

You'd have to press "free user" and wait for 59 seconds, and I'm very sorry about that. Don't know of any easier upload sites.

[DonkeyOte]

Try zipping the file and uploading the .zip

[SpareBank1]

You always have to be so smart? :P

Keep in mind i have upgraded to Excel 2007

[DonkeyOte]

OK so just quickly

1 - you have circular references - ie you have formulae in cells referring to themselves (eg A1: =A1) ... these are present on the 2005 & 6 sheets in columns L, Y, AL - ie the summation ranges should cease in the column before (C:K rather than C:L etc...)

2 - as we mentioned waaaaaaaay back your setup of using identical tables on each sheet is not a bad thing as it allows you to simplify your summary sheet given you know the cells are located in identical fashion... for ex. to sum the Win Total for odds of 1.60 (ie AP386) you can change:

Total Stats
C4: ='PL 2008-2009'!AP386+'PL 2007-2008'!AP385+'PL 2006-2007'!AP386+'PL 2005-2006'!AP386

to

C4: =SUM('PL 2008-2009:PL2005-2006'!AP386)

this is a basic 3D sum across sheets.

To further simplify your life given you intend to add more sheets and so as to avoid the need to modify the above as and when you do you can create 2 new sheets to act as "bookends", we will name those sheets:

START and END

place START before the first sheet to be summed (at present this would be before (to left of) the 2008-9 sheet)

place END after the last sheet to be summed (at present this would be after (to right of) the 2005-6 sheet)

You can now change the formula to:

C4: =SUM(START:END!AP386)

As and when you add new sheets - as long as you place them within the bookend sheets of START & END the new sheet(s) will automatically be included in your calculation without you needing to modify anything.

[DonkeyOte]

Attached is your file with the prior suggestions in place (and corrections to formulae etc).

Note this attachement is in XL2007 format (.xlsx) - the XML file compression used in 2007 is much better than earlier versions hence the reduction in file size
(for ex. zipping a 2007 file of this size makes little significant difference)

For more info. on new file types etc see: http://office.microsoft.com/en-us/he...069351033.aspx

[SpareBank1]

ah, yes the problems you mentioned in 1. and 2. was something i had overlooked since i had to remove columns in those two sheets (One bookmaker wasn't present).

The START/END formula you mention seems very useful in my case, but when i type it and press enter this window pops up:

[DonkeyOte]

re: the error... you need to insert the two sheets first (!) else you're trying to reference something that doesn't exist in the file (hence the dialog box that is generated asking you to locate them)

[SpareBank1]

Ahhhhh, now I understand what you were doing:P Stupid me, please don't laugh Than you for the Help as usual Well atm there are actually no more problems, so again, thank you so much

[SpareBank1]

Ok, new problem just arrived.

If I want to copy a formula every 5th column, and still make it count +1cell. How is it done?

For example, this is how it is if i copy them now:
A4=B2/B3
F4=G2/G3
J4=K2/K3

But i want them like this:
A4=B2/B3
F4=C2/C3
J4=D2/D3

So that the cells in the formula is +1cell, even if the formula moves +5 cells. Is this possible? How?
Thanx in advance
Thing is i have 800 formulas to do it with so i don't want to do it manually


EDIT: Nevermind, I've made a new thread concerning this issue since it is off topic