Brett#
I have found two types of problem that occur with the Archon when opened
with Open Office 2.0 Calc:
First, when the MAX() function is evaluated over a range of empty cells,
it will return a value of zero under Excel, but will return an Error:502
under Calc. The worst instance of this is in cell Q6 in the Methuselahs
sheet resulting in the Archon failing to compute the adjusted rank in
the Methuselahs Sheet, any entries in the Standings sheet, and the
finalists in the Final Round sheet whenever there are no coin ranking
entries.
To fix this problem requires inserting a test condition to determine if
any numeric values exist in the cell range, and if not, to generate the
correct value as if each cell contained a zero.
Second, the Boolean logic value FALSE is treated as a nonnumeric type
under Excel, and a numeric value of zero under Calc. In all four Round
sheets, several cells use an IF() function which returns the value FALSE
if the test condition is false. Other cells in these sheets use the
COUNT() function to count the number of numeric entries in the previous
cells. COUNT() ignores the FALSE values under Excel, but counts them
under Calc, resulting in erroneous tournament point calculations and
game wins.
To fix this problem requires assigning a default value of "" --- a null
string --- when the IF() test condition is false. COUNT() ignores null
strings under both Excel and Calc.
The following modifications are intended to be made to the Archon 1.2
when opened with Calc 2.0.
================================================
NOTE #1: Calc uses the semicolon ";" to separate function arguments,
whereas Excel uses the comma ",".
================================================
NOTE #2: In order to modify the cells on a given sheet, you will have to
click
Tools --> Protect Document --> Sheet
to unprotect the sheet. Make sure to follow this same sequence when you
are done so that the sheet is protected again.
================================================
NOTE #3: The cells to be modified are hidden. Click on
View --> Column & Row Headers
to see the headers. Select the two adjacent column headers that sandwich
the desired column(s). Right click on one of the selected headers and
click "Show" in the dropdown menu that appears.
To hide them again, select the column headers to be hidden, right click
on one of the selected headers, and click "Hide" in the dropdown menu
that appears.
================================================
The adjustments follow:
------------------------------------------------
Methuselahs Sheet:
------------------------------------------------
Change Cell O6 to read: =IF(COUNT($I$7:$I$206)=0;1;MAX($I$7:$I$206)+1)
Change Cell P6 to read: =IF(COUNT($J$7:$J$206)=0;1;MAX($J$7:$J$206)+1)
Change Cell Q6 to read: =IF(COUNT($M$7:$M$206)=0;1;MAX($M$7:$M$206)+1)
Change Cell V6 to read: =IF(COUNT($V$7:$V$206)=0;1;MAX($V$7:$V$206)+1)
Change Cell W6 to read: =IF(COUNT(MPlayerNo)=0;1;MAX(MPlayerNo)+1)
Cells S7:S206 need to be changed as follows:
1) Change Cell S7 to read:
=IF(ISNA(MATCH($R7+1;$R$7:$R$206;0));IF($A$4=0;-1;IF($R7=MAX($A$7:$A$206);$R7;-1));$R7)
2) Select Cells S7:S206
3) Click Edit --> Fill --> Down
------------------------------------------------
Round 1 Sheet:
------------------------------------------------
Cells M7:P206 need to be changed as follows:
1) Select cells M7:P206.
2) Click Edit --> Find & Replace
3) In the "Search For" box, type: )))
4) In the "Replace With" box, type: ));"")
5) Click the button "More Options" and make sure the box labeled
"Current Selection Only" is checked.
6) Click the button "Replace All", then click the button "Close".
------------------------------------------------
Round 2 Sheet:
------------------------------------------------
Follow steps 1-6 as listed in the Round 1 sheet.
------------------------------------------------
Round 3 Sheet:
------------------------------------------------
Follow steps 1-6 as listed in the Round 1 sheet.
------------------------------------------------
Final Round Sheet:
------------------------------------------------
1) Select cells M14:P18
Then, follow steps 2-6 as listed in the Round 1 sheet.
------------------------------------------------
------------------------------------------------
I have tested the Archon with several data sets after making these
modifications, and it seems to function under Calc. Your results may
vary. I have only traced most of the obvious problems to their sources
and made the appropriate changes. I haven't yet gone over the Archon
with a fine-toothed comb to eliminate all possible bugs under Calc. If
you locate any further problems, please reply to this thread. Thanks.
- Brett Harris