Sorting?

HI DT,

The Ranking table should correctly sort the list based on point totals each time an entry is made in the data table.

The Aux table collects the names, points and dollar amounts from your original table, 2013 Pool Standings, and creates an index column containing the points total + tie-breaker value for each player.

Column A of this table contains the names of all participants in the pool, entered directly. these must exactly match the names as they appear in 2013 Pool Standings.

Column B contains the MATCH formula in my earlier post. This returns the row number in which each name appears in 2013 Pool Standings. This value was not calculated or shown in my example table above, as I did not reproduce your 2013 Pool Standings table. The values in column B set the base row from which the formulas in columns C and D build cell addresses from which to return each players points total and dollar total.

The formulas in columns C and D work for the state of the 2013 Pool Standings table that was supplied, but will not work for the same table through the season. My error here was to reference the cells in column S, where the results of Week 17 are recorded, and where, I suspect, the cells in rows 4 and 11 will be empty prior to Week 17.

If my suspicion is incorrect, and after the 4th week of the eason, S4 would show 410 (the same value as is in E4), and S11 would show -21.00 (the same value as is in E11), then my earlier formulas for these columns will work through the whole season.

If that's not the case, then a new formula, similar to the one in T3, must be added to 2013 Pool Standings to calculate the current money total for each player (in T10), and the formulas in columns C and D of Aux must be revised to point to these cells (for each player).

NEW formulas to collect totals from T3 and T10 (for Larry Beamer), and corresponding cells for other participants.

Aux::C2: =INDIRECT("'2013 Pool Standings'::T"&B2+1)

Aux::D2: =INDIRECT("'2013 Pool Standings'::T"&B2+8)

NEW formula for 2013 Pool Standings::T10: =SUM(B10:S10)

Note: This assumes that cell J10 will remain empty. If there's any chance of a number being placed in that cell, use this version:

T10: =SUM(B10:I10,K10:S10)

Repeat the formula in the corresponding row of column T for each participant. The Number part of each cell reference should be the number of the row containing the formula.

Column E of Aux calculates a points + tie-breaker value for each player to create an index used to fill the Ranking table.

Returns to the Ranking table are ordered by the rank of the points + tiebreaker value, with the largest brought in first.

Where two or more players are tied in points totals, the tie-breaker value will be greater for the person listed further down the original table, so that person will be listed first, although both (or all three, etc.) will have the same rank value.

The points totals (without the tie-breaker values) are listed in the Ranking table, and are used in calculating the rank of each participant in the list. Participants with equal point totals will have equal ranks. If two participants share a rank (eg. two are in first place), the next highest point total will rank third. (The explanationin my earlier post goes into more detail.)

Regards,

Barry

Possibly.

I'm assuming the = is missing from the beginning of that formulas to allow it to appear as text. The result should be a number—2 for Larry, 13 for Norm and 24 for Randy when the formula has been filled down three lines.

Try putting some text (eg, "error") between the quotation marks in the formula. If it them gives you te text as a result, you'll know the formula is running into an error of some sort.

Is "2013 Pool Standings" the actual name of the table containing the results to be returned? If not, select "2013 Pool Standings::$A" in the formula, then hover the mouse over Larry's name in the data table, and when the column labels appear above the table, click on the label for column A. This should insert the correct reference into the formula.

If that doesn't make thngs work, you should remove IFERROR( from the front end of the formula and everything after (but not including) 0) from the back end of the formula, leaving =MATCH(A2,table-name::$A,0)

This will either return a result or an error triangle. If it's an error triangle, click it once, then report the exact error message as shown.

Regards,

Barry

dt,

Be sure to check the performance with no game data for the final week or two. If that causes the calculations to fail, see the changes I recommended in my first Feb 9 post.

Regarding conditional formatting: If the 'rule' compares the cells content with a fixed value, you should be able to apply the same rule to a contiguous set of cells all at once. Select the cells then set the rule and results.

If the comparison is to another cell, and the comparison cell is different for each cell to be formatted, then it depends. If the comparison is to cells that all have the same position relative to their target cells (for example, the cell containing the comparison value is always two columns left and one row up from the target cell, then you should be able to do all at once, using a setting called 'Preserve xxx' or similar. If there's not a fixed relationship between the positions of the cells, then I suspect they'll have to be done individually.

I can't give you a more precise answer as I haven't installed Numbers 3. You may want to post this as a new question, rather than let it get buried in this five page thread.

Regards,

Barry

Hi dt,

Looks like:

1. An error on your data table. note that Jim's amounts are different in the grey row (column S) and the green row above it (column T). Should those amounts not be the same?
   
2. Did you make the changes suggested in my first Feb 9 post regarding where AUX picks up the total points and tota dollars, and adding a total dollars calculation to place this amount in a fixed position?

"In the answer where you first brought the new Ranking and Aux table into the mix I notice that index column in the Aux table is grayed out. Why and how was that done?"

Just an indication that this column may be hidden.It's used in the calculations, but isn't necessary (or useful) to those viewing the table. Done by seecting the column, then setting the Fill to a light grey, using the Fill Color well in the Format bar.

Regards,

Barry

Aux::C2: =INDIRECT("'2013 Pool Standings'::T"&B2+1)

Aux::D2: =INDIRECT("'2013 Pool Standings'::T"&B2+8)

Yes, these are the changes I was referring to. Plus the one for T10 (and the equivalent cell in each of the other records) to give a fixed location for the dollar totals.

Regarding saving as a template:

The template will contain whatever the document contained when you saved it as a template.

To prepare for saving as a templete:

• Make a duplicate of the document.
• Remove all entered data from the duplicate, leaving only the labels and all formulas.
• If names of participants will be differnt next season, replace the current names with short, meaningless text strings.
• Remove "2013 " from the name of the main table.
• Enter some dummy data into the Week 1 column to check that formulas are working correctly.
• The dummy data may be removed before saving as a template, or may be left in te document, and replaced in the first week of the new season.
• Save as Template, using a name that does not include a specific date value.
  (not terribly important, as a new document created from the tmplate will open as "Untitled")

Regards,

Barry