Jump to content

Worksheet calculating Spare Fixture counts


Recommended Posts

I'm trying to create a worksheet that auto calculates spare lighting fixtures. So far I have it setup a database row that shows symbol image, instrument type, count per fixture. I want to add a column that calculates spares. I want the spare fixture count to be 15% of the total count. I would also like this number rounded up to the nearest whole number. For example, if I have 5 fixtures, instead of the column saying .75, I want it to say 1. I have tried using the Round command. I've been trying =(Round(C3*.15)+1). In my head that should get me a minimum of 1. What always seems to happen is the formula is calculated on the total of all the fixture below, instead of each row. See Image.

 

1273836157_ScreenShot2018-10-06at3_25_02PM.thumb.png.30ec71bc7a41671bb5d37bfdafaaec54.png

 

 

My next question is, how do I set the row height for the cells in 3.*. I know I can select the cells and change the height, but I want to change the default height for those cells, so anytime I add a fixture to the drawing it gets added at the correct height?

 

Thanks.

 

 

Link to comment

After messing around with the INT, and IF statements, I have not been able to get the desired result. I think I need to include the =COUNT function in the equation, because otherwise it seems to just be working off the total count of all items below. However any time I try to modify the COUNT beyond a simple math equation, I don't the desired answer.

So I've tried:   

=ROUND(COUNT*.15)

=INT(COUNT*.15)

 Both of those result in a 0

 

Then I try

 

=ROUND((COUNT*.15)+1)

=INT((COUNT*.15)+1)

both of those result in the original count.

 

I feel like I'm getting close, but I'm missing something here.

 

 

 

Link to comment

Jonathan, 

 

I don't really understand what this formula does. I'm trying to get a whole number that is at least 15% of the Value in a C3.* cell. So if I do your formula, and I have a value of 10 in C3.7 I get this:

=INT(10)+(If((3)-INT(10)>0,1,0)

=10+((3-10)>0,1,0)

=10+((-7)>0,1,0)

=10+0

=10

The number I would be going for in cell 3.7 is 2 (1.5 rounded up), and 1 in 3.1-3.6.

 

I think this is doing the calculation on the value in the C3 cell, and not the C3.* cells. And that is part of the problem I am having. I think have to include COUNT in the formula somewhere, but I can't seem to figure out how to make this work.

 

 

 

 

Screen Shot 2018-10-06 at 6.02.12 PM.png

Screen Shot 2018-10-06 at 6.07.18 PM.png

Edited by ChadL
Link to comment

I think I've figure out what my problem with the COUNT function is. Let me know if this is working as designed.

 

It seems as if you do a addition or subtraction it will always use the number as a multiple of COUNT. For example (we will use a value of 10 for count:

 

=(COUNT+1)+1     This would return a value of 30, I would expect this to be 12

=(COUNT*2)+1     This would return a value of 30, I would expect this to be 21

=(COUNT/2)+1      This would return a value of 15, I would expect 6

=(COUNT-2)+1      This would return a value of 0, I would expect 9

 

I imagine this is why I am having problems using the INT or ROUND function with COUNT

 

If this is working as designed, how would I do addition or subtractions?

 

 

Link to comment

Hi,
sorry to disturb the "high level worksheet configuration discussion" at this point, but to my experience the amount of spare units usually does not follow exact mathematical rules,- its often a question of availability and/or transportation space.

So my general "quick and dirty solution" for spare parts is simply to drag and multiply them on "spare parts layers". In that way it is even possible to generate spare part lists and/or general packing lists including or without spare, and as well make different layers and worksheets for different cases/transportation units.

 

Maybe that helps... 🙂

Link to comment
  • 1 year later...

Is there any solution by now? I had the same problem today. I would like to have a worksheet with all fixture types inside, summarized by instrument type. Then I would like to multiply the sum of each type with their wattage.

 

So for example I have a worksheet with the count in cell A, the instrument typ in cell B and the wattage in cell C.

 

If I multiply cell A and C, I only get the value from cell C. Like Chad said, it seems that in cell A the value is not really the sum of the instrument types but still the "1".

 

Any solutions?

Link to comment
  • Vectorworks, Inc Employee

This could presumably be created through a marionette script maybe @Sam Jonesor @JBenghiat would be interested in creating one?

 

A marionette script that could be run selectively and find moving light fixtures, make a proportional number duplicates to a specific layer and location into a class that is suffixed with 'Spare'

 

This could then be filtered through the instrument summary tool.

Link to comment
On 10/7/2018 at 11:01 AM, ChadL said:

I think I've figure out what my problem with the COUNT function is. Let me know if this is working as designed.

 

It seems as if you do a addition or subtraction it will always use the number as a multiple of COUNT. For example (we will use a value of 10 for count:

 

=(COUNT+1)+1     This would return a value of 30, I would expect this to be 12

=(COUNT*2)+1     This would return a value of 30, I would expect this to be 21

=(COUNT/2)+1      This would return a value of 15, I would expect 6

=(COUNT-2)+1      This would return a value of 0, I would expect 9

 

I imagine this is why I am having problems using the INT or ROUND function with COUNT

 

If this is working as designed, how would I do addition or subtractions?

 

 

 

The difficulty here is the way that Vectorworks does summary calculations for database rows. Any formula evaluate per row, and then VW will summarize all the combined rows to calculate the summary value. You can see this if you take away the summation on the type column. =COUNT essentially puts a "1" in each cell, and then when you summarize, you get all those 1's added together. So "=COUNT + 1" basically puts a "2" in every cell. Rounding will first round the value for each row, and then add them together.

 

Multiplying will give you the correct result because multiplication is associative: (a * b) + (a * b) = (a + a) * b

 

If you want to do this with a formula, I think the only option is to use DataBaseByScript. You would write a script that takes the unit type as an input, uses the Count() vs function to return the unit count, multiply it by your spares factor, and then round the total. You would then divide the value by the count, and return it to the worksheet. Essentially, you calculate your desired quantity outside of the worksheet context using vs, and return a fraction of the total count, such that when all those fractions add together the worksheet returns the correct value. 

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