Hi again ...
I'm stuck. I am retired and in my spare time, which is plenty, I am trying to learn Excel.
How do I find the number of soccer matches with more than 1,5 goals scored in a list of soccer matches results from the sheet named EFL?
I have tried all combinations I know of using COUNTIF, SUMPRODUCT, INDEX, MATCH, VLOOKUP etc.
Nothing seems to produce the right answer.
I say thanks in advance.
Would you explain the arithmetical calculation that results in 178 matches for 'over 1,5 goals'.
Please reference all contributing cells.
I'm alos puzzled by the over 1,5 goals. The data only contains integer values. This suggests that you are presumably using some sort of average statistic but no doubt your explanation will clarify this too.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
1. By 1,5 you mean one and a half goals?
How do you score half a goal?
2. You have only 71 matches (team versus team) listed, so how can the result be 178 matches?
3. Division Total!E5 should simply be a SUM, no IFs involved.
4. in E7
=SUMPRODUCT(--(EFL!F2:F100+EFL!G2:G100>1.5))
result 58 matches where the total number of goals in each of the 71 matches is greater than 1.5 goals.
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
I agree with the above
or
=SUMPRODUCT(--(E0[FTHG]+E0[FTAG]>1.5)) to stick with your notation, but the same result
My guess, is that he is using the 1.5 as in betting. That should probably be 1 or more goals.
Click the * to say thanks.
Not 2 or more goals?
You are right davsh, "In handicap betting, if you place a bet, for example over 1.5 goals to be scored, this means that there needs to be 2 or more goals scored in the game. The 0.5 is simply to remove the draw/tie as a possibility. The same goes for under. If you bet on under 1.5 this means that only 1 goal can be scored"
So what's the desired answer?
Is it still the 178 you indicate in the worksheet or Special-K's / davsth's 58
Hi ...
There is a betting parameter where you bet on more than one and a half goals pr game.
Yes, 178 is a little too much when only 70 games are played. That's my bad. Sorry about that.
Thank you to Special-K and davsth for a working solution. I need to look a little closer at why and how to use the (--( in a similar formula. Well, formula syntax in general.
If you have logical expression >1.5 it returns true or false. If this is multiplied by 1 or -- it turns it into 1s and zeros and so can be summed. I am sure someone will give you a more learned Answer!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks