Jump to content

Total soft goods counts


Recommended Posts

I'm working on a large project with a lot of pipe and drape. I need to provide some estimates on total drapery and hardware. 

I have a worksheet going with all the data I need, but I can't seem to get the header to sum everything properly. The header only shows the number of rows (57 in my case), not the sum of the rows below. 

 

Here are my columns:

Length of pipe and drape run

Sliders

Upright/Base

Total drape count

 

I've had similar issues in the past, but that was down to how the column is formatted (number, distance, etc). I've tried every combination, but can't seem to get it to work properly.

 

Any ideas what I'm doing wrong? Attached is a screen shot of what I'm running into. 

 

Screen Shot 2020-02-05 at 11.39.40 AM.png

Link to comment

Andy would know better than me, but my guess is that that values in the record are stored as Text instead as a number. It can't add the values of text directly so it just gives you the count of the number of rows.

 

The VALUE() function that Andy is proposing would convert the text value into a number that can then be summed.

Link to comment
  • 6 months later...

@Pat Stanford @C. Andrew Dunning

 

I'm trying to create a worksheet that displays the total "Running Length" of some Softgoods objects for rough budget calculations (summarized by drape height). I've tried editing the header to say =VALUE('Soft Goods'.'TTLSGLngth') based on the post above, however it's creating a new issue:

 

The Running Length field is storing the original value as 9'-4" (for example), but when I switch the header to VALUE it's now using the hyphen in the length as a subtraction sign and changing the value; in this example the length now reads 8'8". The column header is adding the lengths together which was the goal, but of course all the lengths are now wrong...

 

How can I calculate the combined Running Length of multiple Soft Goods objects as a total length without this subtraction issue?

 

Link to comment

@C. Andrew Dunning will probably have a better solution, but here is what I have.

 

The TTLSGLngth is actually a static text field that somewhere in the bowels of the code is being created based on the length. When you convert that to a value then you get the subtraction taking place.  

 

If you have the TTLSGLngth in Column C of a database starting in Row 4, then the following will get you the numeric version of the length.

 

=VALUE(CONCAT(SUBSTRING(C4, '-', 1), ' ', SUBSTRING(C4, '-', 2)))

 

If you Summarize your softwoods, then you have to use the Sum Values option in the Column header containing the above formula to get the value of all the Summarized items.

 

HTH.

Link to comment
4 hours ago, Pat Stanford said:

If you have the TTLSGLngth in Column C of a database starting in Row 4, then the following will get you the numeric version of the length.

 

=VALUE(CONCAT(SUBSTRING(C4, '-', 1), ' ', SUBSTRING(C4, '-', 2)))

 

If you Summarize your softwoods, then you have to use the Sum Values option in the Column header containing the above formula to get the value of all the Summarized items.

 

Oh wow - I would have never known that was even possible, thank you! That works.

Link to comment
  • 1 year later...

Hey Guys,

Similar topic - are you aware of any way to round the "TTLSGLngth" total running length to whole inches? Maybe its not possible since its text? I've used Pat's method to convert it to a value without the subtraction but it returns the value in feet with three decimal places. Is there a way to then convert that to feet and inches in whole numbers?Softgoods.png.a9c9e9c1e3f56a8bed65c291a8ad2ab8.png

Link to comment
3 hours ago, Grayson said:

Hey Guys,

Similar topic - are you aware of any way to round the "TTLSGLngth" total running length to whole inches? Maybe its not possible since its text? I've used Pat's method to convert it to a value without the subtraction but it returns the value in feet with three decimal places. Is there a way to then convert that to feet and inches in whole numbers?Softgoods.png.a9c9e9c1e3f56a8bed65c291a8ad2ab8.png

 

 

 

I round up to the nearest foot using this:

 

=ROUNDUP(0.5+VALUE('Soft Goods'.'TTLSGLngth'), 0)

 

Then use cell formatting to be a dimension.

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