Jump to content

Worksheet Formulas


Recommended Posts

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?

Link to comment

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

Link to comment

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.

Link to comment

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

Link to comment

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

Link to comment

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!

Link to comment

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

Link to comment

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.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

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

×
×
  • Create New...