# 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?

Cheers,

Nick

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...

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

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.

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

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!

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×