Jump to content
Sign in to follow this  
knickers

Showing percentages in databases.

Recommended Posts

Hi there,

I am putting together a simple spreadsheet/ database, where I have the spreadsheet calculating the area of certain (named) polygons in my drawing, but have hit a snag...

The criteria for the database is to search for polygons with a certain class. Then at the head of the columns, I can display the name (by using the '=N' function), and the area (by using the '=AREA/1000000' function to display the area in square meters (the units in my drawing are set to mm so I need to divide by 1000000 to show meters).

It all works fine, and the names of the polygons and the areas are caluclated perfectly.

Is there a way to get the database to display the percentage of each of these polygons of the total area - ie. what would I need to enter in the header of the database to display percentages?

Any advice much appreciated.

Cheers,

Nick

Share this post


Link to post

i don't think you can do it as a "database"

as long as there are not a finger crippling

amount of polys i do it this way...

1

polys are all named.

eg "block 1", "block 2" etc or whatever

2

on a spreadsheet make a column

listing all the named polys

eg.

.....A..............B..............C

1...block 1

2...block 2

3...block 3

4

5

3

in the next column enter the following

formula in the top cell, in this case B1,

and then copy + paste down the column +

the reference modifies itself to point

to the appropriate row:

=AREA((N=A1))/10000000

4

format the column for your area eg m?

you will now have all the areas next

to the names, eg:

.....A..............B..............C

1...block 1......20m?

2...block 2......20m?

3...block 3......10m?

4

5

5

total the areas column below this, say in B5

=SUM(B1..B3)

note the location of this total

(B5)for the next step

6

now in cell C1 enter the following

and then copy + paste down the column +

the reference modifies itself to point

to the appropriate row:

=(B1/$B$5)*100

of the area total, in this case B1 becomes

B2 as you paste, but the B5 remains constant>

7

format the column for percentages

you will now have all the percentages

next to the names, eg:

.....A..............B..............C

1...block 1......20m?......40%

2...block 2......20m?......40%

3...block 3......10m?......20%

4....................----........----

5..................50m?......100%

hope this helps...

Share this post


Link to post

Thanks Gideon - looks like I'll have to go the spreadsheet route then... Shame, because the database really does save a lot of leg-work.

Thanks for the tip with the $'s - I hadn't used them before!

Cheers,

Nick

Share this post


Link to post

Sorry Gideon, but I think you can do it in a database ;-)

There is a function in VW2010 worksheets called CriteriaArea. This takes a criteria and returns the area for all the objects that meet the criteria. But it does not seem to work correctly in a database row.

Assuming the criteria for the database is Class is MyClass

So, set up your column with the area of each object. I am going to assume this is in cell B3.

In a cell outside the database (I am assuming B2) enter the formula

=(CriteriaArea(C='MyClass')*144);

The 144 is the conversion from square feet to square inches needed in my drawing. You will have to figure out the conversion you need to get both the Area and CriteriaArea in the same units.

In cell C3 enter the formula

=B3/B2*100

Use for Format Cell command to set the number of decimal places and add a % sign trailer and you are done.

Share this post


Link to post

pat

brilliant! works on VW 12.5 too

at first i couldn't get it to work using

NAME (N=) as the criteria, and resorted

to the VW help...

guess what? the help didn't help!

and when i finally got to the VS reference

page the CRITERIAAREA example was wrong,

they'd just slapped in the AREA example...

gosh, was i surprised?! not...

the cell we needed using the "block 1", "block 2",

"block 3" names in my spreadsheet example is:

=CRITERIAAREA(N='block*')

in my example i put this at the bottom of the

database so that it looked like the total in

my spreadsheet version.

database in row 1,

in cell B2: =CRITERIAAREA(N='block*')

in cell C1: =B1/B2*100

in cell C2: =C1 just to 'total' the percentages

.......A..............B..................C

1.....=N....=AREA/1000000..=B1/B2*100

1.1...block 1......20m?..........40%

1.2...block 2......20m?..........40%

1.3...block 3......10m?..........20%

..........................................

2......................50m?..........100%

nice solution, pat!

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

 

7150 Riverwood Drive, Columbia, Maryland 21046, USA   |   Contact Us:   410-290-5114

 

© 2018 Vectorworks, Inc. All Rights Reserved. Vectorworks, Inc. is part of the Nemetschek Group.

×