knickers Posted June 29, 2010 Share Posted June 29, 2010 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 Quote Link to comment
gScott Posted June 29, 2010 Share Posted June 29, 2010 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... Quote Link to comment
knickers Posted June 29, 2010 Author Share Posted June 29, 2010 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 Quote Link to comment
Pat Stanford Posted June 29, 2010 Share Posted June 29, 2010 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. Quote Link to comment
gScott Posted June 30, 2010 Share Posted June 30, 2010 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! Quote Link to comment
Recommended Posts
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.