Could someone give me some pointers

I am writing a workbook that can calculate the weights of steel. There are space issues with the workbook. It will be used in our shop and it needs to stay simple.

I got some good advise from some folks on here. They told me it was a bad idea to use merged cells.

I am trying to write a formula for a trapezoid. I am really bad at using the left and right cell functions. I really want to use these. The way I got the trapezoid formula was to add columns and cells. If I can use a formula that uses the right and left formulas I would prefer this. Here is the formula that I was able to come up with. =IF(LEFT(G22,3)="TPL",((O22*12+Q22 + S22*12+U22)/2*(W22*12+Y22))*VLOOKUP(G22,Sheet2!A:B,2,FALSE)/144*D22,((W22*12)+Y22)*(VLOOKUP(G22,Sheet2!A:B,2,FALSE)/12)*D22)

I have posted the information on my web page for people to look at to verify what I am talking about. Let me know if you have any questions or comments. WWW.MYKALTX.COM

mykaltx wrote...
>I am trying to write a formula for a trapezoid. I am really bad at
>using the left and right cell functions. I really want to use these.

Why? What does your data look like that you believe you need to use
the LEFT and RIGHT functions?

>The way I got the trapezoid formula was to add columns and cells. If I
>can use a formula that uses the right and left formulas I would prefer
>this. Here is the formula that I was able to come up with.

>=IF(LEFT(G22,3)="TPL",((O22*12+Q22+S22*12+U22)/2
>*(W22*12+Y22))*VLOOKUP(G22,Sheet2!A:B,2,FALSE)/144*D22,
>((W22*12)+Y22)*(VLOOKUP(G22,Sheet2!A:B,2,FALSE)/12)*D22)

You could shorten this by removing common terms outside the IF call.

=IF(LEFT(G22,3)="TPL",(O22*12+Q22+S22*12+U22)/24,1)
*(W22*12+Y22)*VLOOKUP(G22,Sheet2!A:B,2,FALSE)/12*D22

If you have data in G22 that you want to parse, then extract substrings
between spaces. E.g., to extract 1 23 and 345 from

abc 1 def 23 ghi 456 xyz

if you know that all 'fields' are separated by spaces and only the even
index fields (2nd, 4th and 6th) are of interest,

2nd field (array formula):
=MID(x,SMALL(IF(MID(TRIM(x),ROW(INDIRECT("1:1024")),1)=" ",
ROW(INDIRECT("1:1024"))),1)+1,
SMALL(IF(MID(TRIM(x),ROW(INDIRECT("1:1024")),1)=" ",
ROW(INDIRECT("1:1024"))),2)
-SMALL(IF(MID(TRIM(x),ROW(INDIRECT("1:1024")),1)=" ",
ROW(INDIRECT("1:1024"))),1))

4th field (array formula):
=MID(x,SMALL(IF(MID(TRIM(x),ROW(INDIRECT("1:1024")),1)=" ",
ROW(INDIRECT("1:1024"))),3)+1,
SMALL(IF(MID(TRIM(x),ROW(INDIRECT("1:1024")),1)=" ",
ROW(INDIRECT("1:1024"))),4)
-SMALL(IF(MID(TRIM(x),ROW(INDIRECT("1:1024")),1)=" ",
ROW(INDIRECT("1:1024"))),3))

6th field (array formula):
=MID(x,SMALL(IF(MID(TRIM(x),ROW(INDIRECT("1:1024")),1)=" ",
ROW(INDIRECT("1:1024"))),5)+1,
SMALL(IF(MID(TRIM(x),ROW(INDIRECT("1:1024")),1)=" ",
ROW(INDIRECT("1:1024"))),6)
-SMALL(IF(MID(TRIM(x),ROW(INDIRECT("1:1024")),1)=" ",
ROW(INDIRECT("1:1024"))),5))

And, no, I'm not going to go check your web page. If you want
help in the newsgroups, post all relevant details in the
newsgroup.