Could someone give me some pointers

[mykaltx]

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

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

I am trying to write a formula for a trapezoid. I am really bad a
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
can use a formula that uses the right and left formulas I would prefe
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 t
verify what I am talking about. Let me know if you have any question
or comments. WWW.MYKALTX.CO

 

[Harlan Grove]

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.

 

[swatsp0p]

Mykaltx: You also shouldn't multipost. This topic was also addressed
in another thread.