Array, circular reference problem
 

I've been building a huge data base and have run into a problem:

I have a set of data with record name, ancestor name, recorded
elevation, corrected elevation:
rec anc ele calc ele
1 root "" =j2
2 1 10 =j2
3 1 "" =j2
4 3 3 =j2
5 3 4 =j2

Then I set an array to look at a list of records, find all decendants,
and give max elevation in J2 (+35 is on the end because I want my tree
to grow 35 at each node)

{=if(isnumber(c2),c2,max(if(b2:b4=a2,d2:d4,0))+35) }

But if I fill this down I end up getting a circular reference warning,
and none of the elevations will calculate. When in reality it isn't
circular because the base elevations are already existant. the final
cells should look like this)

rec anc ele calc ele
1 root "" 74
2 1 10 10
3 1 "" 39
4 3 3 3
5 3 4 4

What can I do?


--
rareremedy
------------------------------------------------------------------------
rareremedy's Profile: http://www.excelforum.com/member.php...o&userid=33130
View this thread: http://www.excelforum.com/showthread...hreadid=529424

Array, circular reference problem
 

If D2 =J2 then there is a circular reference


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"rareremedy" wrote
in message ...

I've been building a huge data base and have run into a problem:

I have a set of data with record name, ancestor name, recorded
elevation, corrected elevation:
rec anc ele calc ele
1 root "" =j2
2 1 10 =j2
3 1 "" =j2
4 3 3 =j2
5 3 4 =j2

Then I set an array to look at a list of records, find all decendants,
and give max elevation in J2 (+35 is on the end because I want my tree
to grow 35 at each node)

{=if(isnumber(c2),c2,max(if(b2:b4=a2,d2:d4,0))+35) }

But if I fill this down I end up getting a circular reference warning,
and none of the elevations will calculate. When in reality it isn't
circular because the base elevations are already existant. the final
cells should look like this)

rec anc ele calc ele
1 root "" 74
2 1 10 10
3 1 "" 39
4 3 3 3
5 3 4 4

What can I do?


--
rareremedy
------------------------------------------------------------------------
rareremedy's Profile:
http://www.excelforum.com/member.php...o&userid=33130
View this thread: http://www.excelforum.com/showthread...hreadid=529424

Array, circular reference problem
 

Not exactly a circ ref. I increased the iteration and max change, it
went through, just took longer.


--
rareremedy
------------------------------------------------------------------------
rareremedy's Profile: http://www.excelforum.com/member.php...o&userid=33130
View this thread: http://www.excelforum.com/showthread...hreadid=529424

Array, circular reference problem
 

It is a circular reference that is why you increased the iteration


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"rareremedy" wrote
in message ...

Not exactly a circ ref. I increased the iteration and max change, it
went through, just took longer.


--
rareremedy
------------------------------------------------------------------------
rareremedy's Profile:
http://www.excelforum.com/member.php...o&userid=33130
View this thread: http://www.excelforum.com/showthread...hreadid=529424