Calculate Surface Area

[stevekirk]

HI,

in column a are sizes of wood, in column b are the side i have to stick on tape

if we look at the frst example the tape required would be 2 * 1200 =2400
the reason for this is that 2L means apply tape to the long sides
the next would be 1500
the nest would be 20 because 1S = one lenght of tape for the short side
is it possible for a formular to look at column b look for the letter L OR S
times it by the number infront of the letter. then apply it to the size of the board . the formular would have to know that long = the highest number

COLUMN A COLUMNB

1200x20 2L
1500x50 1L
1200x20 1S
150x2000 2S
1500x50 2L/2S

thanks steve

[Bryan Hessey]

HI,

in column a are sizes of wood, in column b are the side i have to stick on tape

if we look at the frst example the tape required would be 2 * 1200 =2400
the reason for this is that 2L means apply tape to the long sides
the next would be 1500
the nest would be 20 because 1S = one lenght of tape for the short side
is it possible for a formular to look at column b look for the letter L OR S
times it by the number infront of the letter. then apply it to the size of the board . the formular would have to know that long = the highest number

COLUMN A COLUMNB

1200x20 2L
1500x50 1L
1200x20 1S
150x2000 2S
1500x50 2L/2S

thanks steve

Using 7 helper columns (for ease of understanding)

in C1 =LEFT(A1,(FIND("x",A1)-1))

in D1 =MID(A1,FIND("x",A1)+1,999)

in E1 =IF(C1<D1,C1,D1)

in F1 =IF(C1<D1,D1,C1)

in G1 =IF(ISERROR(FIND("/",B1)),"0s",MID(B1,FIND("/",B1)+1,999))

in H1 =IF(G1="0s",B1,LEFT(B1,FIND("/",B1)-1))

in I1 =IF(G1="0s",0,LEFT(G1,1)*(IF(RIGHT(G1,1)="l",E1,F1)))+LEFT(H1,1)*(IF(RIGHT(H1,1)="l",E1,F1))

and formula-fill down as required.


note, for practical purposes you could hide columns C to H

----

[stevekirk]

hi, bryan

thanks works great

i think you will know about what i do for a living than me
i assume my boss will want more solutions with this data

thanks
steve

[Bryan Hessey]

hi, bryan

thanks works great

i think you will know about what i do for a living than me
i assume my boss will want more solutions with this data

thanks
steve

Good to see it worked for you, and I'm sure you will achieve in the future too.

[stevekirk]

hi bryan

seems to be a problem with the results could you please look at the spreadsheet for me and adjust to suit

thansk steve

[Bryan Hessey]

hi bryan

seems to be a problem with the results could you please look at the spreadsheet for me and adjust to suit

thansk steve

Steve, in I1, the formula

=IF(G1="0s",0,LEFT(G1,1)*(IF(RIGHT(G1,1)="l",E1,F1 )))+LEFT(H1,1)*(IF(RIGHT(H1,1)="l",E1,F1))

is incorrect and should be

=IF(G1="0s",0,LEFT(G1,1)*(IF(RIGHT(G1,1)="l",F1,E1 )))+LEFT(H1,1)*(IF(RIGHT(H1,1)="l",F1,E1))

(ie, if 'long' select the larger size in F1, if 'short' use E1 and not vica verca).

hth
---

[stevekirk]

hi bryan

once again thanks

steve

[stevekirk]

hi, bryan,

please see attached file

the small sample is working ok

the large example is my copy of some rell data as you will see the 1L OR THE 2L does not work any and cannot see why as i copied all the fomulars from the small sample


could you please see why and modify

i have just type in the numbers into g and h instead of the formulars and it works ok. i think it is because the numbers we are using are part of a string that has been extracted they are in column o but i copied it to f else thwe formular did not work


thanks
steve

[stevekirk]

hi, bryan

just modified the last attachment

could you pleaae check the file out

thanks steve

[Bryan Hessey]

hi, bryan

just modified the last attachment

could you pleaae check the file out

thanks steve

There was a space added to the 211 number, use Value(g7) to overcome this

=IF(VALUE(G7)<VALUE(H7),G7,H7)

=IF(VALUE(G7)<VALUE(H7),H7,G7)

for the two columns

---

[stevekirk]

thanks bryan

steve