array using contains
 

I have a formula below that calculates raw materials needed. My problem is
that now we have started receiving orders in pairs and eaches and all come
into my sytem as one or the other (ex it will say 2 EA or 2 PR). When I
export them into my spreadsheet I need to be able to double my need
calculated by the formula below. The only thing I have in my spreadsheet to
distinguish is a description which contains 2PK. Could you tell me how to
rewrite this formula to say if cell C3 contains "2PK" in the description then
multiply the E3 qty by 2? E3 represents the units on order.

=IF(K3+L3-E3+F3<0,K3+L3-E3+F3,"")

array using contains
 

This is a bit long winded by it may work.
=IF(C3="2PK",IF(K3+L3-(E3*2)+F3<0,K3+L3-(E3*2)+F3,""),IF(K3+L3-E3+F3<0,K3+L3-E3+F3,""))
--
Regards
Warren
Excel Novice
Addiewell, Scotland.

If this helps please click the Yes button.


"VANewbieNeedsHelp" wrote:

I have a formula below that calculates raw materials needed. My problem is
that now we have started receiving orders in pairs and eaches and all come
into my sytem as one or the other (ex it will say 2 EA or 2 PR). When I
export them into my spreadsheet I need to be able to double my need
calculated by the formula below. The only thing I have in my spreadsheet to
distinguish is a description which contains 2PK. Could you tell me how to
rewrite this formula to say if cell C3 contains "2PK" in the description then
multiply the E3 qty by 2? E3 represents the units on order.

=IF(K3+L3-E3+F3<0,K3+L3-E3+F3,"")

array using contains
 

I got the same result but is it because the formula says C3="2PK"? The C3
value is the description of the item and the description starts off with
2PK(EX "2PK,ProdABC").

"Warren Easton" wrote:

This is a bit long winded by it may work.
=IF(C3="2PK",IF(K3+L3-(E3*2)+F3<0,K3+L3-(E3*2)+F3,""),IF(K3+L3-E3+F3<0,K3+L3-E3+F3,""))
--
Regards
Warren
Excel Novice
Addiewell, Scotland.

If this helps please click the Yes button.


"VANewbieNeedsHelp" wrote:

I have a formula below that calculates raw materials needed. My problem is
that now we have started receiving orders in pairs and eaches and all come
into my sytem as one or the other (ex it will say 2 EA or 2 PR). When I
export them into my spreadsheet I need to be able to double my need
calculated by the formula below. The only thing I have in my spreadsheet to
distinguish is a description which contains 2PK. Could you tell me how to
rewrite this formula to say if cell C3 contains "2PK" in the description then
multiply the E3 qty by 2? E3 represents the units on order.

=IF(K3+L3-E3+F3<0,K3+L3-E3+F3,"")

array using contains
 

Try
=IF(LEFT(C3,3)="2PK",IF(K3+L3-(E3*2)+F3<0,K3+L3-(E3*2)+F3,""),IF(K3+L3-E3+F3<0,K3+L3-E3+F3,""))
--
Regards
Warren
Excel Novice
Addiewell, Scotland.

If this helps please click the Yes button.


"VANewbieNeedsHelp" wrote:

I got the same result but is it because the formula says C3="2PK"? The C3
value is the description of the item and the description starts off with
2PK(EX "2PK,ProdABC").

"Warren Easton" wrote:

This is a bit long winded by it may work.
=IF(C3="2PK",IF(K3+L3-(E3*2)+F3<0,K3+L3-(E3*2)+F3,""),IF(K3+L3-E3+F3<0,K3+L3-E3+F3,""))
--
Regards
Warren
Excel Novice
Addiewell, Scotland.

If this helps please click the Yes button.


"VANewbieNeedsHelp" wrote:

I have a formula below that calculates raw materials needed. My problem is
that now we have started receiving orders in pairs and eaches and all come
into my sytem as one or the other (ex it will say 2 EA or 2 PR). When I
export them into my spreadsheet I need to be able to double my need
calculated by the formula below. The only thing I have in my spreadsheet to
distinguish is a description which contains 2PK. Could you tell me how to
rewrite this formula to say if cell C3 contains "2PK" in the description then
multiply the E3 qty by 2? E3 represents the units on order.

=IF(K3+L3-E3+F3<0,K3+L3-E3+F3,"")

array using contains
 

Yes, that worked. Thanks so much for your help!

"Warren Easton" wrote:

Try
=IF(LEFT(C3,3)="2PK",IF(K3+L3-(E3*2)+F3<0,K3+L3-(E3*2)+F3,""),IF(K3+L3-E3+F3<0,K3+L3-E3+F3,""))
--
Regards
Warren
Excel Novice
Addiewell, Scotland.

If this helps please click the Yes button.


"VANewbieNeedsHelp" wrote:

I got the same result but is it because the formula says C3="2PK"? The C3
value is the description of the item and the description starts off with
2PK(EX "2PK,ProdABC").

"Warren Easton" wrote:

This is a bit long winded by it may work.
=IF(C3="2PK",IF(K3+L3-(E3*2)+F3<0,K3+L3-(E3*2)+F3,""),IF(K3+L3-E3+F3<0,K3+L3-E3+F3,""))
--
Regards
Warren
Excel Novice
Addiewell, Scotland.

If this helps please click the Yes button.


"VANewbieNeedsHelp" wrote:

I have a formula below that calculates raw materials needed. My problem is
that now we have started receiving orders in pairs and eaches and all come
into my sytem as one or the other (ex it will say 2 EA or 2 PR). When I
export them into my spreadsheet I need to be able to double my need
calculated by the formula below. The only thing I have in my spreadsheet to
distinguish is a description which contains 2PK. Could you tell me how to
rewrite this formula to say if cell C3 contains "2PK" in the description then
multiply the E3 qty by 2? E3 represents the units on order.

=IF(K3+L3-E3+F3<0,K3+L3-E3+F3,"")