If then function
 

I want to calculate the square foot price using two columns from a database
but the export is as follows:

Width Length Price SQ FT
24 in 82 ft 107 ?
36 in 82 ft 79 ?
60 in 70 ft 350 ?
7 ft 60 ft 220 ?

They column includes the word "in" for Width and "ft" for Length.

Is there a way i could say the following:
Price/(if width contains "in" then divide by 12 and multiple by Length)

I can't figure out how to strip the "in" and "ft" to multiple.

Can someone please help.

thank you,

Pete Petersen

Here is a calculation from one I am working on for my builder
Feet Inches Feet Inches Sq Ft Price

=(C6+IF(D6<" ",D6/12,0))*(E6+IF(F6<" ",F6/12,0))

--
Don Guillett
SalesAid Software

"Pete Petersen" wrote in message
...
I want to calculate the square foot price using two columns from a
database
but the export is as follows:

Width Length Price SQ FT
24 in 82 ft 107 ?
36 in 82 ft 79 ?
60 in 70 ft 350 ?
7 ft 60 ft 220 ?

They column includes the word "in" for Width and "ft" for Length.

Is there a way i could say the following:
Price/(if width contains "in" then divide by 12 and multiple by Length)

I can't figure out how to strip the "in" and "ft" to multiple.

Can someone please help.

thank you,

Pete Petersen

Hi
not fully tested but try the following formula in D2.
=C2/(LEFT(B2,FIND(" ",B2)-1)*(LEFT(A2,FIND("
",A2)-1)/(1+11*(RIGHT(A2,2)="in"))))

--
Regards
Frank Kabel
Frankfurt, Germany

Pete Petersen wrote:
I want to calculate the square foot price using two columns from a
database but the export is as follows:

Width Length Price SQ FT
24 in 82 ft 107 ?
36 in 82 ft 79 ?
60 in 70 ft 350 ?
7 ft 60 ft 220 ?

They column includes the word "in" for Width and "ft" for Length.

Is there a way i could say the following:
Price/(if width contains "in" then divide by 12 and multiple by
Length)

I can't figure out how to strip the "in" and "ft" to multiple.

Can someone please help.

thank you,

Pete Petersen

One way, assume the data 24 in starts in A2, in D2 put

=IF(ISNUMBER(FIND("in",A2)),(SUBSTITUTE(A2,"in","" )/12)*SUBSTITUTE(B2,"ft",""),SUBSTITUTE(A2,"ft","")* SUBSTITUTE(B2,"ft",""))

copy down

note that both FIND and SUBSTITUTE are case sensitive so if you can have IN
or FT
in the import as well you must use this

=IF(ISNUMBER(SEARCH("in",A2)),(SUBSTITUTE(LOWER(A2 ),"in","")/12)*SUBSTITUTE(LOWER(B2),"ft",""),SUBSTITUTE(LOWER (A2),"ft","")*SUBSTITUTE(LOWER(B2),"ft",""))


Regards,

Peo Sjoblom

"Pete Petersen" wrote:

I want to calculate the square foot price using two columns from a database
but the export is as follows:

Width Length Price SQ FT
24 in 82 ft 107 ?
36 in 82 ft 79 ?
60 in 70 ft 350 ?
7 ft 60 ft 220 ?

They column includes the word "in" for Width and "ft" for Length.

Is there a way i could say the following:
Price/(if width contains "in" then divide by 12 and multiple by Length)

I can't figure out how to strip the "in" and "ft" to multiple.

Can someone please help.

thank you,

Pete Petersen

Thank you all for the quick response. I ran into a brick wall when I looked
further down into my 60,000 SKU's and found that there is another variable to
this. I am so sorry to ask for more help but if you are willing i will be
very very thankful.

Here is a sample data set.

Width Length Price Units Per Units
72 in 120 ft 135 1 Roll (3" Core)
54 in 120 ft 69 1 Roll (3" Core)
N/A N/A 182 1 three-shelf
N/A N/A 182 1 three-shelf
20 ft 20 ft 8700 1 Kit
13 in 50 ft 44 1 Roll (2" Core)
5 in 7 in 14 6 Cards
17 in 100 ft 80 1 Roll (2" Core)
24 in 100 ft 105 1 Roll (2" Core)
44 in 100 ft 173 1 Roll (2" Core)
17 in 22 in 62 25 Sheets
8.5 in 11 in 22 300 Sheets
11 in 17 in 36 6 Sheets

There are two formulas to check against:
The regualar sq. ft which you have already solved and the second. Take this
line for example

Width Length Price Units Per Units
8.5 in 11 in 22 300 Sheets

This would be =Price/(((Width*Length)/144)*Units Per)
This is the square footage price for cut sheets of material.

Again I thank you for any help that you can give.

Thank you,

Pete Petersen





"Peo Sjoblom" wrote:

One way, assume the data 24 in starts in A2, in D2 put

=IF(ISNUMBER(FIND("in",A2)),(SUBSTITUTE(A2,"in","" )/12)*SUBSTITUTE(B2,"ft",""),SUBSTITUTE(A2,"ft","")* SUBSTITUTE(B2,"ft",""))

copy down

note that both FIND and SUBSTITUTE are case sensitive so if you can have IN
or FT
in the import as well you must use this

=IF(ISNUMBER(SEARCH("in",A2)),(SUBSTITUTE(LOWER(A2 ),"in","")/12)*SUBSTITUTE(LOWER(B2),"ft",""),SUBSTITUTE(LOWER (A2),"ft","")*SUBSTITUTE(LOWER(B2),"ft",""))


Regards,

Peo Sjoblom

"Pete Petersen" wrote:

I want to calculate the square foot price using two columns from a database
but the export is as follows:

Width Length Price SQ FT
24 in 82 ft 107 ?
36 in 82 ft 79 ?
60 in 70 ft 350 ?
7 ft 60 ft 220 ?

They column includes the word "in" for Width and "ft" for Length.

Is there a way i could say the following:
Price/(if width contains "in" then divide by 12 and multiple by Length)

I can't figure out how to strip the "in" and "ft" to multiple.

Can someone please help.

thank you,

Pete Petersen