Jump to content
  • 0

Worksheet Multiply Formula?


trashcan

Question

24 answers to this question

Recommended Posts

  • 0
1 minute ago, trashcan said:

spreadsheet application

Remember this is not a spreadsheet in the traditional sense. This is a database in a spreadsheet style format. You can make a Spreadsheet with a worksheet which will behave in a more familiar fashion, but then you won't necessarily be able to process the data from the file in the way that you can with a database.

 

I'm in danger of opening a can of worms.........!

  • Like 1
Link to comment
  • 0

As I said. Its a can of worms!

 

When you create a worksheet from the resource manager you will create an empty spreadsheet. This behaves to all intents and purposes like a spreadsheet. However it has no data.

 

To add data from your file to it a row needs to be converted to a database row. That Row will then crunch data from your file. To do that you need to right-click on a Row header and select Database. From there you will get a criteria dialog which will ask you questions about the objects in the file. Say you have 3 objects in your file, then potentially three sub rows are created. Each row refers to each object.

 

(btw a database row has a diamond symbol in the header. A sub-row will be 1.1,1.2,1.3 etc.....)

 

The columns need to then interrogate the various parameters of those objects. It is the column headers where the formulas for doing that are placed. Back in the mists of time you would have to know the formulas to put in the column headers. Now there is the Edit Report command which will do that for you.

 

There are many more here who will surely elaborate on this (and probably correct me!)

 

Over and out!

  • Like 1
Link to comment
  • 0

@trashcan Every spreadsheet I have ever used, starting with Visicalc in 1983 has allowed formulas like =A1*B1 or =A2+B3.  The Sum type functions are great when you want to work on a range of cells, but not necessary for a single cell.  In VW, =SUM only allows for addition.  =Sum(A1:Z1) would add up all the values in Row 1 columns A to Z. Much easier than =A1+B1+.....+Z1.

 

Now for an update on @markdd's can of worms. Elaborate maybe, but I don't think correct you ;-)

 

1. Worksheets can contain both Spreadsheet rows and Database rows.  Spreadsheet rows operate on only the data that is in that specific cell. Database rows use criteria to specify which objects from the drawing to create subrows (3.1, 3.2, etc.) for. Subrows can be SUMmarized to include more than one object in a single displayed subrow.

 

2. Beyond the basic math functions, most of the built in spreadsheet functions in VW will accept a criteria. If used in a spreadsheet cell, the criteria needs to be specifically stated. If used in a database row, the object(s) that are listed in the database row will automatically be passed to functions that require criteria. So in a spreadsheet cell you might use a formula like. =WALLAREA_GROSS((C='None') & (T=WALL)). While in a database row header cell you would only need =WALLAREA_GROSS.

 

3. Each subrow of a database will use the formulas in the header row and apply them to each object SUMmarized in the subrow. 

 

4. Formulas in either spreadsheet cells or database header row cells can access data in different cells by referencing the cell. If you want to use a reference to a different column you reference the header row cell.  So for a database in row 3 if cell C3 has a formula of =A3*B3, then in subrow C3.1 it will actually use the values in A3.1 and B3.1.  You can reference spreadsheet cells in a database row by just referencing the cell. So if you have a "constant" in spreadsheet cell A1, then in database row C3 you could have a formula of =A1*B3. This would result in subrow C3.1 being equivalent to =A1*B3.1

 

Lots of other gotchas, but hopefully this will help wrap your head around what spreadsheets can do.

  • Like 3
Link to comment
  • 0

OK, let's take this one step at a time. And work backwards. So starting with number 3 (and I guess part of #1).

 

Do you understand Database Rows? For a database row you right click on the row header (it can be any row, but I always use 3 as my example. It leaves a couple of blank rows above in case I need to add a title and labels.) and choose Set Criteria. You then use the criteria builder to set the criteria so that only the objects you want to report are displayed. Criteria include things such as layer, class, object type, record attached, value in a Record.Field, Color, Fill Pattern, etc.

 

Any formula you put into a cell in the database header row (3, not 3.1, 3.2, etc.) will be applied to the objects in each subrow and the result displayed in the subrow. A formula of =L will display the Layer the object is on. =C will display the class the object is in. =N will display the name of the object. And then there are all of the Worksheet Functions for displaying things like wall or component area or volume.

 

Since often there are a lot of similar or identical objects in a drawing, a Database Row can be SUMmarized based on the criteria of any column. If you click on the disclosure triangle in the header row cell and check the SUMmarize check box, objects that have identical criteria in that column will be SUMmarized into a single subrow. Columns that have identical data for every SUMmarized item will display that value. Columns that have SUMmarized items with different values will show as a dash.

 

For numerical values, there is a second check box option under the column disclosure triangle called "Sum Values". When that box is checked for a column (it does not matter if the column is SUMmarized or just displaying data based on a different SUM, then the individual values for each object SUMmed will be displayed.

 

Another trick with SUMmarized values is to use a formula in the database header row of =COUNT. This will return the total number of objects that have been SUMmarized into each displayed subrow.

 

If your head has not exploded yet ask again and I can try to do a better explanation of #2 or answer any questions this thread has brought up so far.

  • Like 1
Link to comment
  • 0
18 hours ago, Nicolas Goutte said:

 

Have you tried simply =B2*C2 ?

 

Well, I did not start with VisiCalc like @Pat Stanford did, but I had the chance to use one day Multiplan. (Otherwise I have really started with old Excels, the successor.)

 

However for me too, it is standard not to use SUM. I am not sure that using =SUM(B2*C2) would work on such old spreadsheet (e.g. old Excel) either, as if I remember well (but only very vaguely), SUM needed at least 2 parameters.

Link to comment
  • 0

Guys, sorry to but in in this very specific conversation, but I would do with your expert advice about using formulas in spreadsheets. 

I think I am doing something wrong or something that is not possible. Or both.

I have a plant schedule, i.e. database rows with such information. I was asked to find savings in the design, so I would like to add columns with rates next to each plant, to calculate the final cost as I change the design. HOW TO DO THAT?

In the database row headers I can either choose records or functions. I tried to change the header criteria to be Price and hoped that the value would be transferred to each plant style, but this doesn't seem to work either, i.e. the Price record stays as defined (0 to be precise). If I change the Price record in the Plant Style, this is then not taken to the schedule.

I cannot input Price value for plants in the Landscape Areas.

Can I have some steer on that from someone who understands the relationships here?

Link to comment
  • 0

If the price is a record.field combination then if you change it in the worksheet it should change in the record.

 

A formula of 'Plant'.'Price' appears to update every instance of that plant that does not have a set value. A formula of 'Plant Record'.'Price' only updates a specific instance of the plant.

 

If you have rows SUMmarized, then any change you make should impact every instance that is SUMmarized in that subrow.

 

You will have to manually recalculate the worksheet after you make changes.

 

Link to comment
  • 0

@Pat Stanford, thank you for your prompt response, as usual.

 

I must say I don't get the way this works.

I added Price values as a test for several plants. Schedule doesn't get it. When I go to Advanced Settings of these plants, the price I used in plant style is set under Custom, whilst Use Plant Style shows 0.

Plus, the same issue as always - Plants within Landscape Areas are not being updated as their styles change. I need to go one area after another, open settings and close, to get these updated in the schedule. Apparently this is a bug and has been filed as such.

Link to comment
  • 0

@Pat Stanford, I have tried both header formulas.

When I use the Plant.Record it doesn't update the Price record in the Plant Style.

It appears that I need to navigate to Advance Settings within OIP and choose what I want to do with the Price information. I think I should add the price to each Plant Style and in the Advance Settings make the Price field by Style.

Technically, in the schedule I can manually type the price value for the plants inserted with Plant tool and figure out the most time-efficient way to apply the price to those in Landscape Areas. I think for these ones, I would add that info to their styles and the schedule will pick it up as I start amending the planting design.

Another way is to export the schedule to Excel and do it flexibly. The downside is that I will need to go back and fro (between the model and the Excel spreadsheet) to see how the cost changes following the design tweaks.

Or perhaps you can see a way to do it in the live schedule in VW?

Link to comment
  • 0

I am having a hard time understanding your problem. I don't work in Landmark very much. Any chance you can put together a simple sample file that demonstrates what you are trying to do? I think I need to see how you are using your symbols/plants to make many more suggestions.

 

To go back to very basics, Database rows can only show worksheet functions or record.field combinations. I think the issue for you is to figure out what record.field is best to use for your pricing. It could a be a field in the Plant record, or the Plant Record record. Or it could even be a custom record that you attach.

 

I need to see more about what you are doing to be much more help.


Sorry.

Link to comment
  • 0

@Pat Stanford, thanks for your time anyway. I know that sometimes it's difficult to understand the problem without seeing anything.

I just feel quite disappointed that it takes me longer than I thought when I put together a quote for the client.

I scrapped what I did yesterday and start with the previously saved version of my design.

I took my time to add price to each plant [style] and then tweaked each planting area, as my client wished.

This is what I managed to achieve:

image.thumb.png.d2d83db9c85da2396a2c3ea8bd572456.png

I am quite surprised again, because the values in the Rate column are not added based on number of plants. I unsummarised the Latin Name column to show numbers in each Landscape Area. For example, Achillea [...] has been found in 10 LAs, but the price is still shown as a unit rate, rather than the product of Quantity and Rate. Even though I selected the values in the Rate column to be summed (see the plus sign).

Probably there is a simple thing I need to do to get this added up. I just don't know what. In simple terms, I want the schedule to show how much each plant is and what the grand total is.In Excel, I would just use the right formulas at the end of each row and the Price column. Can I have some help with this - pretty please ?

 

Link to comment
  • 0
4 minutes ago, Pat Stanford said:

You need to check the Sum Values check box in the cell H2 the Rate database header cell.

@Pat Stanford , that column is already summed up.

image.thumb.png.97d8c777b80df3e7115c51d9c84cbd0d.png

When I do Summarize items by Latin Name, the values in column H show now the total price per species, but a total of the number of instances, so 10 number of planting areas with Achillea will produce £79.6, not £358.2 (45x£7.96). 

image.thumb.png.6912cac033aa11c5e8ce49602b916963.png

Link to comment
  • 0

OK. I see more of the problem now.

 

You have Plant Record.Quantity set differently for different plants.  Plant Record.Quantity is summing to 45, but there are only 10 subrows for the millefolium.

 

The Sum Values does not do a multiplication,  it only takes the number in each record and adds them up. So since you have 10 records at $7.96 each you get $79.60.

 

To do it the way you are doing it, you have to set the Plant Record.Price for each Plant Group to be the total value of the group.

 

Easier is to set up the worksheet to do the multiplication for you.

 

Turn off the SUMmation.

Enter a formula in Column I of = G2 * H2

Turn on the SUMmation.

Set Column I to Sum Values.

 

Column I should have what you are looking for.

 

Turn off the Sum Values for column H and it will be the unit price. If a SUMmarized row has a dash in it instead of a number that means that different items have different prices.

 

HTH

  • Like 2
Link to comment
  • 0

@Pat Stanford, thanks for that last one. I knew it must have been something super easy.

 

What I was confused by was the criteria under the drop down menu in Column I. And I couldn't use the multiplication formula in each cell - I needed to type it in the first row/ heading. I get that now. At least for now, I suppose.

 

11 hours ago, Pat Stanford said:

You have Plant Record.Quantity set differently for different plants.

What do you mean here? The column criteria is indeed set to Plant Record.Quantity, but I didn't do anything more than that. Is there a different, better way?

 

11 hours ago, Pat Stanford said:

To do it the way you are doing it, you have to set the Plant Record.Price for each Plant Group to be the total value of the group.

Could you explain this concept a bit more?

You say "the way I am doing it", but I think I am following what I have learnt from the online guides and in the training with VW. 

How to set the Plant Record.Price for each group, as you mentioned?

Link to comment
  • 0

Plant Groups have a single Plant Record record format. But the Plant Record.Quantity for that group is the total of individual plants in the group. But the Plant Record.Price is the price for EACH plant.  But since the database header options are SUM (meaning addition), this increased quantity from the Plant Groups does not get accounted for unless you do the multiplication manually as I described above.

 

If you want to use only the auto SUM then you have to select each plant group. Click the Advanced button in the OIP and manually enter the price equal to the unit price times the quantity of plants in the group. If you do that for every plant group then you can use the auto sum. But every time you change an plant group you will need to manually update the Price for the group. Far better to just let the worksheet do the multiplication.

  • Like 1
Link to comment
  • 0

@Pat Stanford, thanks for expanding on that. That's absolutely clear for me. I just didn't understand if I had been doing something in an odd way when you mentioned "the way I do it".

Using the worksheet to do the maths was my goal from the outset. I just hadn't realised things like using the formula in the column header. I suppose this is because the rows are divided into sub-rows within the DB.

What I wanted to do initially was to type in a detailed breakdown of the cost per plant in the last couple of columns. However, I realised that I probably couldn't just type random information in the DB cells (subrows), unless associated with some function or record.

Glad I managed to do it with your great help and direction.

I just wished it didn't take so much time to review all the landscape areas to enable the spreadsheet/ schedule pick up the Price record from styles. Fortunately, the project is not enormous.

Many thanks again.

  • Like 1
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
Answer this question...

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