[Solved] Formula for determining who scores first

[AlexandraNyx]

There are 5 formulas used in the Wolfgang creation.

Here is what it looks like BEFORE inserting columns (B,C,D)-


=IF(ABS($R3)*($S3<OFFSET($S3;$R3;0))=1;1;""&IF($Q3=1;"unbroken tie";""))

=IF($R3=0;"";(COLUMN($K3)-SUMPRODUCT(MAX((COLUMN($K3)+1-COLUMN($B3:$K3))*($B3:$K3>0))))+IF(CURRENT()=COLUMN($K3);NA();0))

=ISNA($P3)*ISNA(OFFSET($P3;$R3;0))*ABS($R3)

=(A3<>"")*(-R1+(R1=0))*IF(CURRENT()=1;A4<>"";1)

=IF($R3=0;"";IF(ISNA($P3);COLUMN(K3)-COLUMN($B3)+2;$P3-$R3/4))

AFTER inserting 3 columns (B,C,D) -

=IF(ABS($U3)*($V3<OFFSET($V3;$U3;0))=1;1;""&IF($T3=1;"unbroken tie";""))

=IF($U3=0;"";(COLUMN($N3)-SUMPRODUCT(MAX((COLUMN($N3)+1-COLUMN($E3:$N3))*($E3:$N3>0))))+IF(CURRENT()=COLUMN($N3);NA();0))

=ISNA($S3)*ISNA(OFFSET($S3;$U3;0))*ABS($U3)

=(A3<>"")*(-U1+(U1=0))*IF(CURRENT()=1;A4<>"";1)

=IF($U3=0;"";IF(ISNA($S3);COLUMN(N3)-COLUMN($E3)+2;$S3-$U3/4))


After inserting the columns, values change in the second and fifth formula rendering an incorrect result.

Thanks alexandra

[AlexandraNyx]

ps. Using David's suggestion didn't seem to change anything. Originally expanded references was unchecked.
thanks for the try

[Lupp]

There are 5 formulas used in the Wolfgang creation.

-1-
Would you mind to post the link to the attachment you actually refer to as "Wolfgang creation"?
I meanwhile attached at least 6 files to my posts in this never-emding thread.

-2-
Assuming you talked of "aoo88469_WhoScoredFirst_7.ods" (download/file.php?id=34680)
-2a- Your "first formula" looks different from the one I have in my version of that file.
-2b- I wouldn't like to count and study formulae and to guess a bit how I might readjust them.
-2c- Simply attach your file "before" and your file "after" the insertion of columns, and I can alanyse the problem.

-3-
My version of "number 7" here accepts insertion of columns as long as
-3a- the option I judged to be counterproductive above is NOT enabled.
-3b- the insertion was not inside a range evaluated by the formulae.
-3c- the new columns are not expected to be evaluated by any of the formulae contained.

-4-
In the end you cannot use spreadsheets of some complexity without either
-4a- learning about how spreadsheets work or
-4b- getting someone to design / maintain them for you and then to lock everything except the cells where you are expected to make entries.

[AlexandraNyx]

Hello Wolfgang
The before is the same one you posted.
Here is the after with 3 columns added.
love alexandra

[Lupp]

When I designed the formulae I obviously assumed as assured that no columns would be inserted later in front of the columns of scores (B:K) while I wanted to allow for additional innings (tie-breaks e.g.) by inserting columns between B and K.
These decisions led to the usage of "COLUMN($K3)" in cell R3 reperesenting the maximum number of innings increased by one (11). The insertion of an additional inning would change the formula to "COLUMN($L3)" now in cell S3 and thus deliver there the correct number 12. However, the insertion of a blank column left of B would also produce the "COLUMN($L3)" shifted to S3 without the creation of a new inning. The result 12 now being wrong.

Instead of "COLUMN($K3)" in the original we can use "COLUMN($B3:$K3)+1" "COLUMNS($B3:$K3)+1" to get the correct result 11 also if new columns left of B are inserted, but 12 (or more) if one (or more) columns for innings get inserted.

Errors like this one will occur again and again as long as relevant assurances are guessed due to the lack of knowledge about the actual precise intentions and needs.

See new attachment.
PS: AOO does not correctly adapt the ConditionalFormat used for the listing paper style when columns are inserted. (LibreOffice does.)

[AlexandraNyx]

Thank you very much for your persistence Wolfgang.
The long and winding road has come to an end... for now!

Vielen Dank

Immer lieben

Alexandra