Sqrt function works in one sheet, not in another - [solved - was circ. ref.]

[unearthed]

I have two near identical worksheets  where =SQRT works in one and not the other.

 

=SQRT works with one cell which is calculated from other cells, and fails on all the other cells where I want to use it. It's infuriating. Can anyone see what's going on?

It Does work on other cells if I simply enter the number but that defeats the purpose of having a computer. 

 

Worksheets are standalone with no links, Cells formatted the same, both set as decimal to same signif. places.

 

My desired solution is to generate mean planting distances for each of my planting types (which I have working the exactly same setup in other jobs).

 

I'm doing this in worksheets as I usually understand and trust them more than the DB which I've had very bad (co$tly) experiences with.

Peacock_sqrtBug.vwx

[Pat Stanford]

You have a circular reference caused by the SUM in Row 35. It is set to SUM rows 11 to 38 which means it is counting itself.  Change the SUM to rows 11 to 34 and all should be well.

[unearthed]

Hi Pat, Thanks so, so much for seeing that. I went round and round looking, comparing the two sheets and I missed that - yes it's perfect again now 🙃.

I need to set up some sort of validation so that doesn't happen again.

 

Are worksheets more functional in later versions? Helping one avoid, or at least find such errors?

[Pat Stanford]

Worksheet have changed the interface fairly significantly since VW2012 to make it easier to SUM and SORT in databases using a more typical interface than dragging the tiles to the columns, but Worksheet formulas are effectively a programming language. Nothing has been added to help prevent programming errors.

 

I tested your file in VW2021 and I was still scratching my head about what was going on.