Performing math on Spreadsheet and Database cells in a worksheet

[Bill Treen]

I have a worksheet for calculating roof height. Roof height is based on the weighted average of the individual roof areas relative to a geodetic height.

 

In this worksheet, I have a database cell that picks up polygons that are linked to a record.

 

The next column lists the areas of those polygons. I now have a nice list of the polygons, their names and their areas.

 

In the next column I want to perform a division function where the individual areas are divided by the total area.

 

Since the total area shows up in the column header. I can link a spreadsheet cell to this column header and the total area shows up in the spreadsheet cell. ie. same value

 

HOWEVER,

 

When I then try to divide the spreadsheet cell area by the database header value (to get the weighted average) the spreadsheet cell changes it value to zero and the operation fails.

 

Is there a way to get information from a database cell and perform a math function on it without ruining the value of that cell?

 

[Pat Stanford]

Can you post a sample file?

 

It is definitely possible to do math in databases.  There is an issue with SUMmarized cells in that it does the math on each of the subcells and then adds those results rather than doing the math on the SUMmation. I have worked around that a few times using a Worksheet Script. I will be happy to help with that if you end up needing that.

 

 

[Bill Treen]

Hi Pat,

Cells A2... are getting objects from the database

Cells B2... are showing their areas.

Cell B4 is reporting the value of B2 (total area). It shows a value of zero because cell C2 has the math function "=B4/B2". Somehow linking the cells with a math function causes cell B4 to show a zero value.

What I want is for Cell B4 to show 2696.782

I want Cell C2.1 to show 2138.992/2696.782

I want Cell C2.2 to show 396.393/2696.782

I want Cell C2.3 to show 161.397/2696.782

 

thanks for looking at this. It seems like it should work but it doesn't. I tried it in VW 2021 too. It didn't work there either. I guess it's not a regression.

 

 

[michaelk]

In cell B4 enter 

 

=B2

 

That should solve your division by 0 problem 🙂

 

[Pat Stanford]

I did the best I could to replicate your situation. I am seeing the same thing. If you reference a database header cell into a worksheet cell and then use the worksheet cell in another formula in the database header row, the value of the worksheet cell is forced to zero and displays as zero.

 

It does not seem to depend on the recall order of the worksheet as I tried worksheet cells above, below, left, and right of the database header row cell using the cell as part of the formula.

 

I exported back to VW2022 and it shows the same behavior.

 

@michaelk Do you have any ideas?

 

It definitely looks like a bug.

 

My test file (VW2023) is attached.

 

 

WS Zeroing formula from Database Header.vwx

[Pat Stanford]

Try the test file Michael.  When B4 has a formula of =B2 as soon as you use B4 as part of the formula in C2, the value switches to zero.

[michaelk]

I'm seeing the same thing.  You can work around it with a spreadsheet area call.  See attached.

 

Looks like a bug.

WS Zeroing formula from Database Header~.vwx

[Pat Stanford]

I found a work around.

 

Most of the WS functions can be used in a Database Header Row without specifying any criteria and they will automatically take the object in the database subrow as the object to operate on.

 

But most can also be used in a Worksheet cell by specifying the criteria.

 

So you can enter a formula of:

 

=ROOFAREA_TOTAL(((T=SLAB))) into B4 and you will get a value that does not reset to zero when used in a database header formula.

 

Or you can even use a formula if C2 of:

 

=B3/ROOFAREA_TOTAL(((T=SLAB)))

In either case you will have to make sure the database criteria and the function criteria are the same or you will be doing math on different areas.

 

 

 

 

 

[michaelk]

🙂  Yeah.  What he said.

 

 

[michaelk]

I used =AREA(((ST=ROOFFACE))) and it got the values he wanted.  

 

Not sure if that is using projected area or actual area.

[michaelk]

And it's definitely a worksheet issue.  I tried it with just areas of rectangles and got the same result.

[Pat Stanford]

Bug filed:  VB-191196: Spreadsheet Cell Value read and displayed as zero when used in database header calculation

[Bill Treen]

Wonderful, guys !

Thanks for looking at this problem and for filing a bug report.

I'd like to see this fixed. I'll try your workarounds in the meantime.