Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.

Get a Professional Answer

Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.

100% Satisfaction Guarantee

Rate the answer you receive.

Ask The-PC-Guy Your Own Question

The-PC-Guy, Computer Manager

Category: Microsoft Office

Satisfied Customers: 1997

Experience: 20 years experience providing remote computer support

62934938

Type Your Microsoft Office Question Here...

The-PC-Guy is online now

I need a conversion for lumber from Net Tally (actual, after

This answer was rated:

★★★★★

I need a conversion for lumber from Net Tally (actual, after kiln drying) to Green Tally as though the widths were measured when the lumber green (fresh sawn) before shrinking during drying. The average shrinkage is about 7%.

I can supply the example tally sheet with formulas if you need it but here is what is should look like:

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

Packing List - Tally Sheet

Package Number

Length

Actual Net Tally

<- Widths ->

Piece Count

Board Feet

3"

4"

5"

6"

7"

8"

9"

10"

11"

12"

13"

14"

15"

16"

17"

18"

19"

20"

2

15

5

8

6

3

5

1

28

243

16

11

28

6

8

10

6

1

2

72

685

Package Number

Length

Converted to Green Tally

<- Widths ->

Piece Count

Board Feet

3"

4"

5"

6"

7"

8"

9"

10"

11"

12"

13"

14"

15"

16"

17"

18"

19"

20"

2

15

2

8

6

4

5

1

2

28

261

16

7

19

13

8

9

11

3

1

1

72

737

This tally shows widths from actual tally to allow for 7% shinkrage during kiln drying

Formula needs to convert widths so that they end in columns where end calculation matches Net Tally + 7%

Please note that the green tally results need to adjust the net tally widths to the green tally. I'm guessing it needs a formula in each cell in the width colums.

Maybe I can explain it a little better. IF you look at the formuals in the BOARD Feet column, that calculates the volume based on the number of boards (pieces) under each width. Number of pieces in each width on the net tally are shown as

5"

6"

7"

8"

9"

10"

11"

12"

13"

5

8

6

3

5

1

The green tally needs to take the widths from the net tally and adjust them such as (I did this by hand but need formulas which would move widths from the net tally width column to the green tally column so the end result of the board feed calculation on the green tally sheet equals the net tally board feet calculation +7%

Such as:

5"

6"

7"

8"

9"

10"

11"

12"

13"

2

8

6

4

5

1

2

7

19

13

8

9

11

3

1

1

Customer:replied 3 years ago.

we can do a remote session if you need it, or your can call me at XXX-XXX-XXXX.

Close, but no cigar, yet. The piece count total must remain constant. This much match the actual number of boards in the pack of lumber.

The green tally needs to pull pieces from the net tally and spread the pieces across the green tally width columns so that the end result is net footage+7%

Thanks. I'm attaching the full workbook so maybe you can see better what we use (Well, I can't get it to attach, do you have an email address I can send it to?). We enter the actual widths on the net tally page (different pages for different thicknesses). If you look at any of the green tally pages, you can see a formula that I can up with to convert widths from the net tally page to the green tally. Unfortunately, my formulas don't work out exactly. Maybe you have a better idea. Also, I couldn't ger your workbook using the "sumproduct" formula to round the totals correctly.

As I said, what I came up with doesn't work so another thought, could a formula in each cell of the "Piece Count" column force it. I'm not the pro, just bumbling my way through.

I do not know any formula that would properly calculate this for you unless everything was constant, and from what you said. It is not, inotherwords even though the total of the 2nd set will be 7% greater than the first one. You said that not all boards will be 7% greater some will be more and some less, just that the total width needs to add up to 7% greater than the initial width with the number of boards being the same.

So this is what solver does.

Did you have problems with using solver as I showed you?

The total piece count and the pieces per width must always be whole integers, not decimals, so in this case, solver doesn't work.

Customer:replied 3 years ago.

If it is impossible for Excel to do what I need, let me know. I certainly appreciate your efforts and of course will approve your payment for the work you've done.

it is possible to force the conditions to use whole numbers by adding a condition for that in solver.

Here is the first set of numbers cells E12 - J12 on Green Taly 4 sheet

that my solver came up with

3 7 7 4 4 3

tell me if this works for you

IF these values are the values you were looking for, it would be possible to create a macro to do it for all rows for you at once. so you wouldn't have to run solver on each row.

There would be an additional charge for this if you want it.

But I know it works, and those numbers that came back are all integers

Thanks, XXXXX XXXXX 377443 are the ones that don't work. Let's just leave it as is and close the case. Thanks anyway for trying to help. I just don't want to put any more of my personal money into it since I was trying to come up with something to provide a "service" to one of my customers.

Wish I could do more. If you do come up with a more definitive way of doing what you need, for example if you tell me that every board on green will be exactly 7% more than the others. I could work out a formula with that. But the solver is the only way to do this guessing scenario, if that did not work for you, then I am truely sorry!

The problem is that in actuality, any board between 4.5" and 5.49" is counted as 5", 5.5" and 6.49" as 6" and so on. Each board shrinks 7% but in shrinking 7% doesn't always loose a full inch in measure. We have to work in averages to cover our loss in measure between net and green. We buy the lumber in green and dry it. Most customers buy on net tally measure and then it's no problem as we calculate the loss into the price. Some customers buy on green tally and still no problem, except in the case where a customer wants it on a green tally as though the boards were measured before kiln drying and shrinking.

Not really. The numbers shown and calculated results always have to be whole numbers.

I was trying to explain that a 5" board (for example) could stay a 5" (4.75/0.93=5.11 which is a 5" board) or it could be a 6" (5.25/0.93=5.65" which is a 6" board), etc.

Since widths entered on the net tally sheet are whole numbers and the numbers on the green tally sheet (piece count in widths: columns) must be whole numbers, it's a matter of spreading out the widths on the green tally sheet so that the end result in the board feet calculation equals the net/0.93.

LOL! I understand. I've been working in the lumber business for 41 years and still it's hard to explain to a novice. But then sometimes I have a hard time understanding computer speak.