ZEROIN Posted July 30, 2008 Share Posted July 30, 2008 Hello. I am trying to have totals at the end of my "List" pricing column (1 of 6 columns, varying number of rows) on a furniture report. I tried creating a "SUM" formula in an empty Spreadsheet cell below the Database cell without success. Any suggestions? Quote Link to comment
Pat Stanford Posted July 30, 2008 Share Posted July 30, 2008 Redefine the List price field in your record to be defined as a number instead of as text. Then you can use a formula of =E2 to put the total at the bottom. Integers and Numbers automatically total in the database header row. Text Fields shows the count of the total number of objects in the database header row. You might also want to turn off the database header rows once you get it all worked out. Pat Quote Link to comment
ZEROIN Posted July 31, 2008 Author Share Posted July 31, 2008 Thank you for your reply Do you know if and how I could then multiply each ROW "LIST" by its "QTY."? In other words, I need to multiply each row item individual cost by the number of items, then add a grand total. I am trying to automate this process and avoid a further step of creating a Spreadsheet for my totals. I hope this makes sense, I added a pict. Thank you again. Quote Link to comment
Vectorworks, Inc Employee Hugues Posted July 31, 2008 Vectorworks, Inc Employee Share Posted July 31, 2008 Yes, you can do this. =E2*F2 is the formula you need to write in cell G2. Hugues NNA Quote Link to comment
ZEROIN Posted July 31, 2008 Author Share Posted July 31, 2008 Thank you for the reply I seem to be missing something, I get a "#VALUE!" error. Thank you! Quote Link to comment
Pat Stanford Posted July 31, 2008 Share Posted July 31, 2008 Yet again, you List Price column is set as Text. Until you get the price into the record and worksheet as a number, there is no way to do what you want. a number multiples by a text is an undefined #Value. See my post #107516 in this thread. Quote Link to comment
ZEROIN Posted July 31, 2008 Author Share Posted July 31, 2008 The formula you gave me seems to work and not work at times. See the attached pict. For some reason column E also multiplies with column F, there is no formula to do so in E2 only the record value (=('Furniture'.'LIST')). Columns E, F and G are formatted as numbers in the Record as well as the worksheet. Thank you Quote Link to comment
Pat Stanford Posted August 1, 2008 Share Posted August 1, 2008 If the file is less than 10MB, you can send me a copy to the address in my signature and I will take a look. I can't tell what is wrong based on your screen shots. Did you Recalculate the worksheet after you changed the filed to a Number type? Pat Quote Link to comment
ZEROIN Posted August 1, 2008 Author Share Posted August 1, 2008 Thank you for the reply and help Pat, I just sent you a file for your review. Quote Link to comment
Pat Stanford Posted August 5, 2008 Share Posted August 5, 2008 We were not understanding enough about the internals of the worksheet. I am surprised i never figured this out before, but I had not noticed it either. When you have a numeric cell in a group of summed cells, it is going to give the sum of the values, not the value of any one of the cells. Therefore, your Column E is actually the total for the row and is what you want in Column G. If you want a column to show the price of a single item, you need to add a new column with a formula of =count. For this example, let's say we put this in Column H. This will give you to the number of rows summed in the sub-row. Change your formula in Column G to be =(furniture.list) Change the formula in Column E to =G2/H2 These formulas should work to give you what you want as long as the QTY field of the record is 1. If the QTY is more than 1, we will have to dig a little deeper. I have not tried it, but I think that changing Column G to =(furniture.list)*(furniture.'Qty.'). Let me know if this makes sense. Pat Quote Link to comment
ZEROIN Posted August 6, 2008 Author Share Posted August 6, 2008 Hello again Pat First I want to thank you for helping me out with this, hopefully it will be a timesaver for me and others. I tried your suggestions and it seems to work, I will not know for sure until I use it periodically. I have emailed you a sample of the reworked formulas in a file with workstations. Let me know what you think and thank you again! Quote Link to comment
Pat Stanford Posted August 6, 2008 Share Posted August 6, 2008 The revised version looks good. I can't see a better, simpler way to do it. Again, it is important that your QTY field remains at 1 for each instance. If you don't like the extra column, you can set the column width to zero so it will not be visible. You can also turn off the database headers from the triangle pull-down menu so you don't have to show all the sums across the top row of the database. Good job. 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.