[Solved] Formula for determining who scores first

[AlexandraNyx]

I need a formula to determine which team scores first.

The box scores look like this with 901 playing 902 and 901 going first. 901 scores first in inning #2 than 902 scores in inning #2. In the second game 903 hits first and plays 904. 903 scores first in inning #8. The third game, 905 goes first and plays 906. 906 scores first in inning # 2. . .

......Innings.........1st.2nd.3rd.4th.5th.6th.7th.8th.9th

901 San Francisco....0...1...0...0...0...0...0...0...0
902 Arizona ...........0...2...0...0...0...0...0...0...0

903 Chi. Cubs........0..0..0..0..0..0..0..3..0
904 St. Louis.........0..0..0..0..0..0..0..0..1

905 N.Y. Yankees...0..0..1..0..0..0..0..0..0
906 Tampa Bay......0..2..0..0..0..0..0..0..0

Thanks for any help
Love alexandra

[Lupp]

Your table is of the "every-other-row" type which causes trouble again and again. The structure may be appropriate for a human making notes with a pencil on a paper sheet. It is not well suited for keeping and evaluating data in spreadsheets.

See the attached example for a suggestion how you might do it.

[AlexandraNyx]

Thank you for the suggestion. The table is the way scores are posted online. Here is what it looks like. Sorry for not posting it before. I'm looking for a formula to get the results in column O.
Thanks again.
Love Alexandra

Formula for determining who scores first.ods

(13.69 KiB) Downloaded 134 times

[AlexandraNyx]

Would appreciate help on getting a formula to get the results in column O.
Thanks so much
Alexandra

[acknak]

Here's one approach. I've used an extra column to first find what inning each team first scored in, then a separate column to decide which team scored first.

The "what inning" formulas are limited to 9 innings; that will have to be expanded if you need more.

[AlexandraNyx]

That should do it.
Thanks very much!
Love Alexandra

[Lupp]

The valuable approach by acknak has one little flaw, imo. It does not allow to fill the formulas down row by row. They must be pasted as a pair of different formulas into their place for every new couple of rows (box scores) or filled down under additional precautions as a triple.

Trying to avoid this I got slightly more complicated formulas. See attached demo. You have the choice.
(I also chose a different approach concerning the helper column. The MATCH there would evaluate in array in LibreOffice also if not explicitly ordered.)

[acknak]

The valuable approach by acknak has one little flaw, imo. It does not allow to fill the formulas down row by row. They must be pasted as a pair of different formulas ...

Oops. I meant to mention that. Good work!

[AlexandraNyx]

I second that. Excellent. Thank you Lupp. You are the best!
Love Alexandra

[AlexandraNyx]

Baseball season has started again. I'm doing something wrong here.

Here is the box scores for yesterdays games.

Odd numbered team is the road team and bats first.

The problem is if one team DOESN'T score then I'm getting #N/A.

What do I add to the =MATCH(1;B6:J6>0;0)+(A7="")*0.5 to make it work?

thanks for any help
Alexandra

[AlexandraNyx]

also how to include column K?

[Lupp]

Sorry.

You didn't understand the relevance of the empty rows in the solution I suggested last year.

Based on the example you gave in your original question I took for assured that between any two pairs of rows used for a game there should be at least one empty row. In specific the being empty of a cell in column A was used by the HELPER formula as the indicator for the team placed immediately above(if any) to be the second team of a pairing.

If you fill the table without interrows this cannot work. To adapt my formula in the helper column you need another assured indicator for the mentioned fact then. If you can assure that every team is listed with a decimal number of exactly 3 digits prefixed, and that the second team of any pairing has an even number there while the number of the other team is odd, you can use the being even of that number as the indicator. I have no means to know in what way you fill your table and what kind of numbers (if any) you use prefixed to team names.

You need to give an explicit assurance about how you will actually do it.

The column for a tenth inning is a simple thing: Just included it with the range inside the call to the MATCH() function.

Please also note that the runs/hits/errors columns ar not evaluated in any way by my formulas.

[AlexandraNyx]

Thank you for the answer my friend. You are right. I didn't understand the significance of the empty row.

That however would create more work than the formula is to alleviate. So on a practical level it's counter productive.

The pretext to each team would always be three numbers but not always the same number for the same team. Different days, different three digit number. But as you asked, it would always be odd team bats first, even team bats second.

In another column on the sheet, the teams are listed without the number if that helps.

"The column for a tenth inning is a simple thing: Just included it with the range inside the call to the MATCH() function."

What would that look like?

Thank you so much for your valuable time. It's very much appreciated. Hopefully I can help someone else when I begin to understand some of this.

love alexandra

[Lupp]

See attachment.
No guarantee of any kind.
Errors expected.

[AlexandraNyx]

Thank you Lupp

You reworked both formulas. That's great!

In the 957/958 game, there was no score until the 10th inning and that didn't register.

Although it doesn't seem to matter, if the first score was in the 2nd inning, it registers as 8 in the helper column.

9th inning as 1.

7th inning as 3 and so forth.

If first score was in the 10th (column K) it doesn't register.

appreciate this my friend.

love alexandra ...

[Lupp]

Although it doesn't seem to matter, if the first score was in the 2nd inning, it registers as 8 in the helper column. 9th inning as 1...

Code: Select all

MAX(COLUMN($K2)-COLUMN($B2:$K2))*($B2:$K2>0))

The formula now used in the helper column is looking for the leftmost non-zero inning by finding the MAXimum >0 of products of a kind where unwanted elements of the respective set of numbers are factually excluded by annulling them applying as a factor a comparison coming out FALSE and thus being 0 (zero) if used in an arithmetic expression. Same concept used with MINimum wouldn't work because a set of non-negatives containing a zero number would always result in a 0.
Of course, you can "undo" the flipping of order by prefixing a

Code: Select all

COLUMN($K2)-

to the complete helper expression. You would then have to replace the comparator ">" in the ScoredFirst column with "<" twice, and to adapt the handling of the unbroken tie by slightly more complcated means. I judged this to be an unnecessary complication, but if you want to see the number of the first inning with a score for each team in each game, you can do it the way shown in the new attachment. There may be better (simpler) ways, but I didn't want to spend much time for the variant.

[AlexandraNyx]

Thank you Lupp

I will be sharing your work with other people at the sports-book in Las Vegas. They will be very happy as they were entering this data by hand.

Everything seems to work except when the first score is in the 10th inning (column K).

It then registers 0 0 for both teams. Example games 957 958.

Again, most appreciative of you spending your time and sharing your knowledge.

love

Alexandra

[Lupp]

The pair-of-row design is made for human eyes (in specific those, probably, installed in front of American brains). For a spreadsheet not just keeping the data, but having to deliver results based on formulae, it's definitely a mess. Everything completely simple if using a design with one row per game is getting really complicated.

I still do not understand two things. Please explain!

-1- The human eye is easily adjusted to evaluate information presented in pairs of rows. It would, however need interrows (or a similar aid) to actually handle the situation. Why are the data presented the way they are. Somebody lazy, indifferent, incapable?
(I established a usable aid of the mentioned kind for the demo by Conditional Formatting.)

-2- What good for? Why should I want to know which team scored first, not just for a specific game where I simply would look, but for a multitude where only a kind of statistics can be delivered?
If this is about betting and making money, don't forget to donate at least half of the profit for undoubtedly charitable objectives like fighting betting addiction or the power of the NRA.

See attached new demo.

[AlexandraNyx]

Thank you for your thoughts Lupp -

Your formula works great. To a point.

Example game 957/958. If one team doesn't score then it's value will be zero. But since that is lower than the team that really scores first, the result will be incorrect. So we're very close to getting it solved. Just not there yet.

To your two points.

All the box scores are loaded from a web page and it's formatted in pairs not in one line. I could rearrange it the way you suggested but that would be inefficient. It's not an incapable thing. I could do it but I'm looking to be more efficient on a practical level.

All the data is arranged by pivot tables so I'm not looking at the data on this spread sheet rather the pivot table sheet. It's not set up this way for the view of the eyes. That's what the pivot table is for. It's not lazy because I'm not looking to avoid work. I spend hours on this daily. It's not indifference either because I'm open minded to your suggestion but all other formulas already work. It just this one thing that seems to be difficult to solve.

The second point about why should you want to know. The better question would be why wouldn't you want to know. Sports-betting is all about stats and who scored first is a valuable stat.

Donating to a worthy cause is a nice idea. Thanks for that.

Betting addiction can be a serious problem. Every person have a choice is life to do what they know is right or not. If betting is not a right thing for someone to do, then they shouldn't do it. If they do choose to do what they know is wrong, they are not going to feel right in their life. That's the down side. There is a utility to morality. You do it not because you have to but because it works. That's what makes it a principle instead of a rule.

I don't know anything about the NRA except what they pro port to stand for. I'm not sure what power you are referring to. I'm guessing they support like minded people like other organizations do. Not all politicians are corrupt. I'm not that cynical. Some can be bought and that's a choice people make. We all have a choice to do right or not. That's what individual power is. The power of choice. Can't blame the NRA for that. That falls on the individual. The only choice we have in Life is to be happy or have a type of conditional unhappiness. Someone wrote, You will not be punished for your anger, you will be punished by your anger.


You've done really fine with sharing your time and sharing your knowledge. You've helped me on some different questions I've asked and I appreciated that. If you've come to a dead end on this one, other than me rearranging my data base, then maybe there is no answer. I don't know. I came here because in my estimation, if there was an answer to be found, this was the place to come.

Appreciate your time and sharing your thoughts Lupp.

love always
alexandra

ps attached new demo?

[Lupp]

(There seems to be a problem with the site for the moment. I'm logged out every few minutes.)

Example game 957/958. If one team doesn't score then it's value will be zero. But since that is lower than the team that really scores first, the result will be incorrect. So we're very close to getting it solved. Just not there yet.

I thought this was solved with the new demo aoo88469_WhoScoredFirst_6.ods.

ps attached new demo?

It's my turn to not understand now.

Concerning my additional points:
I sometimes express poitions in a way probably looking funny to persons not accustomed to it. I didn't want to patronise anybody. I am not interested in betting, and I tend to emphasise the disadvantages of treating it as just another business. On the other hand I have to notice that we seem to get more and more betting shops (How do you call it?) for some time now in Germany, and I don't like that. From my regional perception I suspect this an unwanted result of a general tendency to interpret freedom and liberal positions in a faulty way. The US also aren't unanimous about the subject. I took the opportunity to read

Nevada is the only state in the union that allows legal betting on all major individual sporting events. That fact alone draws thousands to Vegas every year. This is their guide.

on https://www.vegas.com/gaming/sportsbooks/ e.g.
Of course, that's off topic here. Sorry.

[Lupp]

Seems you were right. There was still a flaw in the formuale causing an error in cases where a tenth inning was played, and the first team again didn't score while the second one did.
In addition I didn't consider that you seemingly want to paste "boxes" of exactly 15 columns width with 3 extra fields behind the innings and an empty column .
This should now be fixed. You may also remove the Conditional Format tghough it surely doesn't any harm.

Please report if the new variant actually meets the needs now, or in what way it still fails.

[AlexandraNyx]

Lupp

You are the best. That's genius. I admire your persistence.

Works like a charm. I mostly agree with you. Gambling can be a problem. But it's like most things. You get out of it what you put into it. Most people play to get something for nothing without any work. And that, for the most part doesn't work.

Let we share this with you. I have a friend who uses apple excel. I don't particularly like either company, She gave me a formula using excel for "who scores first" without the helper column.

=IF(ISODD(LEFT(A2,3)+0),IF(INDEX(B2:K2,MATCH(TRUE,
(B2:K2+CHOOSE(ISODD(LEFT(A2,3)+0)+1,B1:K1,B3:K3)>0),0))>0,1,""),IF(OR(O1=1,SUM(B2:K2)=0),"",1))

Don't understand any of it. It does work in Excel I'm not going to use. I love Open Office. United Free Office forever!

I don't know if this can translate into open office cause that's all i'm going to use.

I notice Germany plays Mexico in World Cup competition June 17 in Moscow.

Any insight on that?

Thank you very very much Lupp

Appreciate the conversation and your knowledge.

Love Always

Alexandra

[Lupp]

I didn't understand clearly the formula your friend suggested. Of course, it uses the comma in place of the semicolon, but, this fixed, it should not need any "translation". There isn't anything there I would expect to work differently in Excel as compared with AOO or LibreOffice.

Did you notice that the formula as given MUST be placed in column O to be able to work? Otherwise it needs adaptions.
In addition the force-array disposition of a parameter of MATCH() is not yet correctly implemented in Apache OpenOffice. Therefor your friend's formula needs to be entered explicitly for array-evaluation using Ctrl+Shift+Enter.

Obviously it's often easier to design a new solution than to understand an "alien" attempt. Actually I spent another couple of minutes to get a single-formula-solution without helpers based on the only assumption that the "boxes" are entered contigously and start in a known fix row. Unfortunately it has to check internally for NA-errors, and this is really complicated in AOO as it still not has implemented the IFERROR() and/or the IFNA() function. For LibreOffice, the sister of AOO, the formula for row 3 (assumed the first one of all the rows containing data), and placed in column P, would read

Code: Select all

=IF(ISEVEN(ROW(P4)-ROW(P$3));IF((IFERROR(MATCH(1;$B4:$K4>0;0);11)<=IFERROR(MATCH(1;$B5:$K5>0;0);11))*(SUM($B4:$K5)>0);1;"");IF(P3=1;"";IF(SUM($B4:$K4)>0;1;IF($A4<>"";"UT";""))))

See your reworked example attached here.

PS I would still prefer the solution with helpers.

[AlexandraNyx]

Hello Lupp ...

I'm totally fine with the formula you created. It works fine. I would like to be able to cut and paste it someplace else on the spreadsheet. Is that possible?

thanks
alexandra

[Lupp]

Suppose you are talking of the columns P through W of the "7-sheet".

You can select any of these columns (or the complete group) and drag it, using one cell as the grip, elswhere to an empty range. If you want to insert it as a new column between some columns already having content, press 'Alt' in addition. The formulae should automatically adapt as needed.

Regards
Wolfgang

PS No insight concerning soccer!

[AlexandraNyx]

Wolfgang

Glad to meet you.
It's interesting what you suggested. The select and drag works great but not cut and paste.Can't say I understand it but it works wonderfully. If I was to add, say 3 new columns, B, C, D, with different data and innings starting in E to N, what kind of move does that take?

love
alexandra

Amazing. Germany in the World Cup and no interest at all?
I'm not a great soccer fan but the World Cup is really cool.

[Lupp]

Concerning Drag'n Drop vs Cut'n Paste: I tested with AOO (4.1.5 in my case) and with LibO 6.0.3. and both proceedings worked.

To create three new empty columns B, C, D for additional content simply select the existing columns B, C, D, and then call 'Insert' > 'Columns' from the menu or 'Insert Columns' from the context menu.

Amazing. Germany in the World Cup and no interest at all?
I'm not a great soccer fan but the World Cup is really cool.

A bit of kidding acceptable? Germany simply is excellent in too many fields to take interest in each one of them.
(I cannot remeber a soccer WC since 1954 -fifth in history- Germany was not qualified for.)
Facts: I'm 73 and not exactly a clean version of 'Sporting Life'. Thus my interest in soccer cooled somehow, and insight there never was - nor was a relevant amount of practise. Nowadays I'm not only too slow in moving, but also in watching. Kidding again; Women's soccer is still "watchable" for me.

[AlexandraNyx]

When I inset the 3 columns, the formulas then change in relationship to the new columns and thus is not correct. How do i make the formulas know that the innings now start in column E instead of column B?

Okay Wolfgang. I must say, age is a state of mind. Our consciousness has some effect on our physical body. You working on these open-office type solutions illuminates the connection between mind and body. Exercises for the mind is healthy, a kind of sport. That's part of why you enjoy doing this? There is a beauty to sports. Beyond the physical aspect, your mind has to be attentive to the moment, being present instead of lost in the mental. Life could be looked at this way. How old would you be if you didn't know how old you were? That's the question we all answer.

[robleyd]

Go to Tools | Options | OpenOffice Calc | General, look under Input settings and ensure Expand references when new columns/rows are inserted is checked.

From the offline Help - F1:

Expand references when new columns/rows are inserted

Specifies whether to expand references when inserting columns or rows adjacent to the reference range. This is only possible if the reference range, where the column or row is inserted, originally spanned at least two cells in the desired direction.
Example: If the range A1:B1 is referenced in a formula and you insert a new column after column B, the reference is expanded to A1:C1. If the range A1:B1 is referenced and a new row is inserted under row 1, the reference is not expanded, since there is only a single cell in the vertical direction.
If you insert rows or columns in the middle of a reference area, the reference is always expanded.

[Lupp]

I would judge the recommended setting generally as dangerous and as counterproductive in the current case.

The actual advice should be:
Go to Tools | Options | OpenOffice Calc | General, look under 'Input settings' and MAKE SURE 'Expand references when new columns/rows are inserted' is NOT checked.

Otherwise, some formulae would no longer be as needed for the given purpose if 3 columns were inserted left of B,C,D the new columns would be treated as containing additional innings. The OQ even expressed the problem to this effect. She should have changed the bad "B" in the respective formulae of the first row to an "E" and have then filled down the rectified formulae as far as needed.

(If you want expanded ranges always insert columns / rows inside the previously used ranges and fill formulae / formats afflicted by that down / right as needed. In many cases insertion of columns / rows does not just need adapted formulae, but will also create cells not containing the needed formulae at all.)