Getting around circular reference

It would be more efficient to give a screenshot showing the set of datas to teat.

You know them but none of us are behind you.

Yvan KOENIG (VALLAURIS, France) mardi 12 juillet 2011 13:59:16

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

SteveMPYC wrote:

The array will contain the back calculated handicaps for the previous 5 weeks for each boat, so after this weeks race all I need to do is add the current result and the new handicap for next week is calculated.

I take it from this that the only reason to "save" the array from week to week is that "saving" it is necessary to the means of calculation you are currently using. Is that a correct assumption, or are the nine values you noted (one of the most recent and two each of the previous four) needed for some other purpose?

Regards,

Barry

PS: Today was a long day, and my thinker needs a nap. Some progress, but not enough to report. 😕

Hi Steve,

It's proving to be a bit of a tough nut to crack; not so much in the actual calculation of the rolling handicap, but in keeping track of where the date is being pulled from. Here's where I am at the moment.

The first image is the handicap results for one of the boats after the 12th race; the second shows the results one race later. Finish times for the three boats used in the calculations are listed on the data sheet (third image). The only change to the data sheet between the first and second images was the entry of the three finish times for the race on July 24.

Image1: handicap calculation after race of July 17.

The blue warning triangles are notes that the formula refers to some cells without numbers, a factor that does not create errors in the calculations.

Image2: handicap calculation after race of July 24.

Not that the calculation from the results of the most recent race have been placed in the #1 column, and the rest of the results have been pushed left one column. The maximum value, 80.8% remains the same, and is not included in either average.

Image 3: Race date (finish times in HH:MM:SS for each race. The minutes and seconds parts of the race times for each boat were chosen as an aid in tracking the results of parts of the formulas I'm working with.

The data table is just that—it contains only the dates, numbers and results of each race for each boat, all entered directly from the keyboard. The results table contains only calculations, using data collected from an intermediary table.

That table contains two rows for each boat; on one it collects the race number for each race the boat has finished. on the other, the times for those races for each boat. It also calculates, on a separate line, the 'best' time for each race. The table is then used as a Lookup table for the next step.

The results table above collects the 'best' result for the most recent five races completed by each boat and that boat's times for those races, and uses ('best' - 'my time')/'my time' to calculate the deduction necessary for the boat to have the same finish time as the winner. The five results are added, then the worst (largest) thrown out (by subtracting it from the total), and the result divided by four to obtain the current handicap for that boat.

Somewhere along the way, though, I'm picking up errors and missing values in the calculations. I'm hoping for a quieter day tomorrow (we're in the midst of moving a 50,000 volume library into (smaller) temporary quarters this week, in preparation for building of a replacement), and another look at the formulas.

There is light at the end of the tunnel, but it looks like there are still a few more curves to be negotiated before reaching it.

Onward!

Regards,

Barry

Hi Steve,

I spent some time re-examining the formulas and made some changes. Here's the current version, with some explanation of how the numbers are obtained.

The spreadsheet consists of three tables:

• Data: A table on which the results (time taken) for each race and each boat are entered. The best (ie. least) time is also calculated for each race.
• Index-1: A table with two rows for each boat. the upper one creates an index of races for which results have been recorded; the lower one calculates the percentage of that boat's time that must be deducted for it's adjusted time to match the winning time. The descriptive formula for this is (my time-best time)/my time.
• Last 5 and HC: A table with one row for each boat. The table extracts the five most recent percentage deductions calculated in Index-1 for each boat, then determines the average of the best (smallest) four of those percentages.

Here are the three tables, with sample data for three boats, and a listing and description of the formulas used in each table.

Date:

Row 1 contains the date for each weekly race.

Row 2 contains a serial number, used in the index to identify each completed race for each boat, and in the Last 5 table to determine which results to include in the current handicap calculation and as search values for the lookup formula extracting those values.

Row 3 shows the winning time (duration) for each race. B3 contains the formula =MIN(B4:B20)

Rows 4 to 20 (some are hidden in the image to save space) are each used to contain the race results for one boat. Cells in these rows are formatted for Duration, and may be empty, or contain a text notation for races that have not yet occurred, or in which that boat did not participate, or did not finish.

Index-1

Rows 1 and 2 repeat the first two rows on Data.

Row 3 is empty to allow the first boat to be in the same line on this table as it is on Data.

Rows 4-20 (again, some hidden) copy the race number for each race completed by the boat assigned to that row, using the formula (in B4):

=IF(ISBLANK(Data :: B4),"",IF(ISERROR(Data :: B4+DURATION(,,,,1)),"",B$2))

The two IF statements insert a null string ("") if cell B4 on Data is empty or if it contains a non-Duration value. If a Duration value is found (indicating the boat has completed that race), the formula inserts the race number found in B2.

Rows 21-23 are empty.

Rows 24-40 calculate the percentage handicap that would be necessary for the boat assigned to that row to match the winning time for each race in which the boat finished. Here's the formula, as it appears in B24:

=IFERROR((HLOOKUP(B4,Data :: $2:4,ROW()-21)-HLOOKUP(B4,Data :: $2:$3,2))/HLOOKUP(B4,Data :: $2:4,ROW()-21),"")

Reformatting to show the parts:

=IFERROR((

HLOOKUP(B4,Data :: $2:4,ROW()-21)-

HLOOKUP(B4,Data :: $2:$3,2))/

HLOOKUP(B4,Data :: $2:4,ROW()-21)

,"")

The top and bottom lines are an error trap that blocks the error message where 'B4' is empty, and HLOOKUP consequently has no value to lookup and returns a 'can't find' error.

The first and third HLOOKUP lines treat Data as a rolling lookup table . It's top row (row 1 of the Lookup table) is always row 2 of Data, where the race numbers are listed. Its bottom row is calculated from the row containing the formula. For the formula in ROW 24 (of Index-1), the bottom row is ROW 4 of Data, the 3rd row of the Lookup table, and the row containing the actual finish times for boat 14. ('my time' in the description above).

The second HLOOKUP line is similar, but the associated Lookup table contains only two lines—ROWS 2 and 3 of the Data table. This part returns the winning time ('best time' in the description) for each race where the boat assigned to its row has a finish time.

Together, the three center parts construct " ('my time' - 'best time')/'my time' ", with the result formated as a percentage with two decimal places.

Last 5 and HC%:

Rows 1 and 2 are empty.

Row 3 contains the numbers 5 to 1 in descending order, indicating the fifth most recent result, etc. The numbers are used in the forst formula in row 4 (and rows below that).

Row 4 contains three formulas. In B4, and filled right to F4:

=IFERROR(HLOOKUP(LARGE('Index-1' :: 4:4,B$3),'Index-1' :: 4:24,21),"")

Ignoring IFERROR (it's there for the same purpose as earlier), the rest of the formuls says:

"Look for the fifth (5 in B3) largest number in ROW 4 of Index-1 (the first row of the Lookup table—rows 4 to 24 of Index 1) and return the value 21 rows below it in the same column (boat 14's handicap as calculated for its fifth most recent race)."

As the formula is filled down to the rows for the rest of the boats, the top and bottom rows of the lookup table will adjust to match the two rows on Index-1 assigned to the next boat. Each lookup table will contain 21 rows.

The blue triangles in these cells are 'warning' triangles noting that the formula 'refers to cells without numbers.' They may be ignored.

In G4: =MAX(B4:F4)

Lets you know which value in the set is not being used. The result is used in the next formula, but it wold be as easy to place the calculation into that formula (as shown in the 'revised' version).

In H4: =(SUM(B4:F4)-G4)/4

H4 revised: =(SUM(B4:F4)-MAX(B4:F4))/4

Both version add the five values in B4—F4, subtract the largest value, then divide the result by 4 to find the average value.

That should provide enough to digest for the moment.

I've given a bit of thought to the carry over of percentages from the previous season(s), and an idea is beginning to gel. more to come.

Regards,

Barry

Regarding carryover from previous season:

I rejected the idea of transferring the results of the 'last five races' of each boat as these are scattered through the years results, and for boats that have finished fewer than five races in the season, are recorded in the season previous to that.

Instead, I've opted to transfer the single race handicap collections from each boat's most recent five races, as this can be done by copying those calculations from the Last 5 and HC% table, then using Edit > Paste values to paste the calculated values into a new table, "Prev.season,end"

Results for boats 15 and 16 were added to this table manually after values from the end of the previous season were pasted in, so the percentage figures in the last two columns (also pasted values) were not updated in this table. The two columns aren't needed (or used), but it's simpler to Select All the cells in Last 5 and HC% than to remember from year to year just which cells are needed.

With the previous results now saved in fixed form, a revision to the formula in Last 5 and HC% was necessary to have the calculation use the n most recent of these to make up the total five most recent results needed for the handicap calculation.

Revised formula in B3 (and filled down and right to F20:

=IF(COUNTIF('Index-1' :: 4:4,">0")<B$3,HLOOKUP(COLUMN()-1,Prev.season.end :: $B$3:$F$20,ROW()-2,0),IFERROR(HLOOKUP(LARGE('Index-1' :: 4:4,B$3),'Index-1' :: 4:24,21),""))

=IF(COUNTIF('Index-1' :: 4:4,">0")<B$3, • This will be TRUE if the boat has completed fewer than five (#in B3) races this season.

HLOOKUP(COLUMN()-1,Prev.season.end :: $B$3:$F$20,ROW()-2,0), • Gets the most recent result from the previous season.

• If more races have been completed than the "5" in B3, then the original formula comes into play, and gets the fifth most recent result from the Index-1 table:

IFERROR(HLOOKUP(LARGE('Index-1' :: 4:4,B$3),'Index-1' :: 4:24,21),""))

Note that the previous year's figures are pulled into the HC% table in reverse order—the oldest is in the 'most recent' column—see the results for boats 15 and 16. This is so that as new races are run (in the current season) the new results replace the oldest results first.

Interesting question. Thanks for asking. 🙂

Regards,

Barry