Getting around circular reference

Question

Level 1

4 points

Getting around circular reference

I use a spread sheet (currently excel) to calculate sailing results but have been trying without success to re do in numbers.

At the end of each race a number is moved into an array and the previous number moves along, I have used a formula " if (a1=?, If (b1<>?, X1,y1),y1)"

This has the effect of moving the number in x1 to y1 if the if statement is met but leaves the number in tact if not.

However in numbers I get a "This formula can't reference it's own cell" error but I have not found a way around this issue. Now Lion won't support Rosetta I need to get this to work in numbers (as my old excel version won't run in Lion) and with a bit of luck be able to use in the iPad version also.

In effect there are multiple boats and multiple race results each that the last five are stored and averaged to calculate a new handicap. With the above set up I have been able (for about 10 years +) to easily keep the last five results everything else works fine just not the array.

Any ideas?

Posted on Jul 10, 2011 5:38 AM

Reply

Answer 1

KOENIG Yvan

Level 8

41,845 points

Jul 10, 2011 8:09 AM in response to SteveMPYC

I apologize but I don't understand.

What's the meaning of the question mark in your formula?

In Numbers, a formula set the value of the cell in which it sit, it doesn't move other ones.

Yvan KOENIG (VALLAURIS, France) dimanche 10 juillet 2011 17:09:18

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 !

Reply

Answer 2

Barry

Level 9

60,344 points

Jul 10, 2011 10:24 AM in response to SteveMPYC

Hi Steve,

You might want to check one of the free open source 'office' applications, NeoOffice, LibreOffice, or OpenOffice.org.

Regards,

Barry

Reply

Answer 3

Jul 10, 2011 10:48 AM in response to SteveMPYC

Steve,

What you are describing should be easy enough to accomplish, but not necessarily with the same expressions you used in Excel. Numbers doesn't support all the array functions that are available in Excel. Difficult though to say more about what you might want to do because I have no idea how the expression you listed relates to the task or what your data layout looks like.

Jerry

Reply

Answer 4

Badunit

Level 7

31,331 points

Jul 10, 2011 2:57 PM in response to SteveMPYC

I assume the question marks in your formula mean something else, that they are not actual question marks. But I don't know what they refer to.

I assume your formula is in cell X1 or Y1. If so, that is the circular reference. If it is A1 or B1, that too would be a circular formula.

Do you have automatic recalculation turned off in Excel? Excel can work with circular references if it automatic recalculation is turned off, otherwise it should have the same problem you are seeing in Numbers. If you have it turned on in Excel and it is working that way, something must be different between the two spreadsheets.

If you can walk us through this spreadsheet and how you use it, it would be helpful. All we know right now is that after a race a number magically moves into an array (how did it get there, was it entered into a cell, which cell?) and all the other numbers move down (though it would appear from your formula that they move across. What is in B1, what is in A1? Is this the only formula in the entire spreadsheet or is there a column or row of these formulas?

Reply

Answer 5

SteveMPYC Author

Level 1

4 points

Jul 10, 2011 7:32 PM in response to KOENIG Yvan

Ok, in order: I do have auto calc turned off in excel.

Here is a copy of the actual sheet (part of)

V9 W9

these two are repeated 4 times in X, Y, Z, AA, AB, AC, and AD9 is the final cell.

T9 is the switch 0 = don't move and 4 = move R9 is the newly calculated handicap

R9

Hope this makes sence.

Reply

Answer 6

SteveMPYC Author

Level 1

4 points

Jul 10, 2011 7:34 PM in response to Barry

Thanks Barry,

Yes I have down loaded Open Office (last nignt) and it looks like it will do the job.

I would however like to use Numbers if I can work it out, also it's a bit of a challenge.

Reply

Answer 7

SteveMPYC Author

Level 1

4 points

Jul 10, 2011 7:46 PM in response to Jerrold Green1

Jerry and Badunit,

Please see above for more detail on the real formula.

In numbers I have changed the cell type to time so in R9 the *24*60 is redundant. But the above is how it looks in excel and at least that works. The first part of the sheet (the results) works fine in numbers it is just the array (from T on) that is giving me grief.

Also U is a blank cell so has no effect.

Steve

Reply

Answer 8

Barry

Level 9

60,344 points

Jul 10, 2011 9:22 PM in response to SteveMPYC

OK.

Definitely a circular reference here, which Numbers will not accept.

Numbers cells may contain either an entered value, or a value determined by a formula in the cell. The formula cannot reference the value that is already in the cell. The formula in V9 generates the error message by attempting to return it's own value if either of the stated conditions is false.

User uploaded file

Take a step back from the formula itself and look at what determines the values in the formula. What determines whether T9 is 0 or 4? (Are these the only possible values for T9?).

All of the values in your example are in row 9. I'm assuming that each row is the record for a single boat. Unless there are no factors external to the record of that single boat, I think we need a bigger picture in order to suggest how to accomplish this in Numbers.

Regards,

Barry

Reply

Answer 9

SteveMPYC Author

Level 1

4 points

Jul 10, 2011 10:32 PM in response to Barry

Barry,

Correct each row represents a different boat and so are all the same as the one above.

Yes T9 is just a switch currently activated by a macro I run after each race to calculate the handicap for the next race. My issue is if I take away the last V9 in that cell then the result is either False or 0 if either T9 is not 4 or R9 has no result (ie that boat did not finish and therefore no new handicap).

I use the double cell set up V and W so the result only flows into the next cell on 0 and then onto the next after the following race when T becomes a 4. If I remove that set up the numbers just flow through the entire array.

I need the double check T & R so 0 handicaps are not picked up.

Looks like open office is the go unless you can come up with a clever trick.

In summary the array will fill up one result at a time over 5 weeks and after that the last number drops off as the newest result comes into V and so on over the season. I drop the worst result average the other 4 and that becomes the new handicap.

R9 gives a percentage (handicap) calculated from the winner so each boat would have the same carrected time for that race.

Steve

Reply

Answer 10

Barry

Level 9

60,344 points

Jul 11, 2011 12:39 AM in response to SteveMPYC

Can we take a step back, set aside the formulas used in the Excel solution, and consider the data used to calculate the handicaps and how that would be done using pencil and paper.

If I'm understanding it correctly, the calculations for a team's/boat's handicap uses:

The boat's actual recorded time for the most recent five races that boat has finished.
The recorded finish time for the winning boat in the same five races.
For an individual race in which the winning boat's time was 16 minutes and the subject boat's time was 20 minutes, the subject boat's handicap would be 0.20 (20%), the 'discount' from it's time of 20 minutes needed to match the winning time of 16 minutes.

The handicap assigned to the boat for it's next race is the average of the smallest four handicaps calculated from that boat's five most recently completed races (this could be from the last seven—or more—weeks if the boat did not race or did not finish).

Example:

Here are the calculated handicaps for a sample boat over the first 12 weekends of the season. "--" = did not race, "dnf" = did not finish. "|" marks groups of five weeks/races. Numbers shown in bold are the ones used in the calculation of the current handicap.

15 17 20 -- -- | 17 17 17 22 18 | dnf 16

This boat's handicap for the next race would be 17%

If those statements all fit the calculation, then I think I can see a Numbers solution. Before jumping into it an working out the details, though. I'd like a confirmation that my description is accurate, OR, if I've made some errors, a correction of those errors.

Regards,

Barry

Reply

Answer 11

SteveMPYC Author

Level 1

4 points

Jul 11, 2011 4:41 AM in response to Barry

Barry,

You are correct I do of course need to store the array from week to week, very interested in your solution.

Thanks

Steve

Reply

Answer 12

Barry

Level 9

60,344 points

Jul 11, 2011 11:11 AM in response to SteveMPYC

Steve writes: "I do of course need to store the array from week to week"

Which values are in "the array"?

I should be able to get to this this evening (It's just after 11:00 AM as I post).

Regards,

Barry

Reply

Answer 13

SteveMPYC Author

Level 1

4 points

Jul 11, 2011 11:47 PM in response to Barry

Thanks Barry,

The array being the 5 back calculated results from R, stored in V to AD the first 4 numbers twice (in my set up) and the last once.

These numbers need to be carried over week to week without any re entering or looking up old results.

Steve

Reply

Answer 14

Barry

Level 9

60,344 points

Jul 12, 2011 12:50 AM in response to Barry

Progress is being made, and the task looks doable. I ned to take a fresher look at it tomorrow to see if some simplification is possible.

Would still like to know which values are in 'the array' that you "need to store from week to week."

Regards,

Barry

Reply

Answer 15

SteveMPYC Author

Level 1

4 points

Jul 12, 2011 4:07 AM in response to Barry

Barry,

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.

The result calculation piece is in A to Q part of the sheet.

Steve

Reply