Hello,

By using the following conditional formula, I am able to get the category needed based on the sales. The sales are mutually exclusive. In other words, if there is a value under CAD SALES then USD SALES & USD FX will be either zero or blank. And if the USD SALES is filled then CAD SALES will be either zero or blank. And if all three sales fields are empty then the category should be "NOD".

I would like to incorporate this mutually exclusiveness in my formula. Please see EXAMPLE 2, customer 'B', though this is a USD customer but because the there is a value under CAD SALES, it is giving an incorrect category.

IF(C20,"CAD",IF(D20,"USD","NOD"))

EXAMPLE 1
CUSTOMER CATEGORY CAD SALES USD SALES USD FX
A CAD 100.00 - -
B USD 200.00 20.00
C NOD - - -


EXAMPLE 2
CUSTOMER CATEGORY CAD SALES USD SALES USD FX
A CAD 100.00 - -
B CAD 1.00 200.00 20.00
C NOD - - -


Thanks
SJ

Can you just reverse the order?

IF(D20,"USD",IF(C20,"CAD","NOD"))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"SSJ" wrote in message ...
Hello,

By using the following conditional formula, I am able to get the category needed based on the sales. The sales are mutually exclusive. In other words, if there is a value under CAD SALES then USD SALES & USD FX will be either zero or blank. And if the USD SALES is filled then CAD SALES will be either zero or blank. And if all three sales fields are empty then the category should be "NOD".

I would like to incorporate this mutually exclusiveness in my formula. Please see EXAMPLE 2, customer 'B', though this is a USD customer but because the there is a value under CAD SALES, it is giving an incorrect category.

IF(C20,"CAD",IF(D20,"USD","NOD"))

EXAMPLE 1
CUSTOMER CATEGORY CAD SALES USD SALES USD FX
A CAD 100.00 - -
B USD 200.00 20.00
C NOD - - -


EXAMPLE 2
CUSTOMER CATEGORY CAD SALES USD SALES USD FX
A CAD 100.00 - -
B CAD 1.00 200.00 20.00
C NOD - - -


Thanks
SJ

If you have a customer with sales in both CAD and USD, then clearly the sales
are not mutually exclusive. I would suggest that if you want 'category' to be
based on where most sales are, then the formula you need would probably be
something like IF(C2=D2,"CAD","USD").

Otherwise you may need to reexamine the sales entries - i.e. whether you
wish to allow sales in CAD to be entered at all. You could use conditionals
in the sales columns if necessary, or Data Validation perhaps, to prohibit
entries being made in both USD sales and CAD sales.

Good luck
Geoff
--
There are 10 types of people in the world - those who understand binary and
those who don''t.


"SSJ" wrote:

Hello,

By using the following conditional formula, I am able to get the category needed based on the sales. The sales are mutually exclusive. In other words, if there is a value under CAD SALES then USD SALES & USD FX will be either zero or blank. And if the USD SALES is filled then CAD SALES will be either zero or blank. And if all three sales fields are empty then the category should be "NOD".

I would like to incorporate this mutually exclusiveness in my formula. Please see EXAMPLE 2, customer 'B', though this is a USD customer but because the there is a value under CAD SALES, it is giving an incorrect category.

IF(C20,"CAD",IF(D20,"USD","NOD"))

EXAMPLE 1
CUSTOMER CATEGORY CAD SALES USD SALES USD FX
A CAD 100.00 - -
B USD 200.00 20.00
C NOD - - -


EXAMPLE 2
CUSTOMER CATEGORY CAD SALES USD SALES USD FX
A CAD 100.00 - -
B CAD 1.00 200.00 20.00
C NOD - - -


Thanks
SJ

Thank you Bob & Geoff

The formula is working, however, I was not able to attain the desired results due to other variables that I did not consider and put in my explanation. I will soon post another explanation and if you gentlemen could review it.

Thanks
SJ
"SSJ" wrote in message ...
Hello,

By using the following conditional formula, I am able to get the category needed based on the sales. The sales are mutually exclusive. In other words, if there is a value under CAD SALES then USD SALES & USD FX will be either zero or blank. And if the USD SALES is filled then CAD SALES will be either zero or blank. And if all three sales fields are empty then the category should be "NOD".

I would like to incorporate this mutually exclusiveness in my formula. Please see EXAMPLE 2, customer 'B', though this is a USD customer but because the there is a value under CAD SALES, it is giving an incorrect category.

IF(C20,"CAD",IF(D20,"USD","NOD"))

EXAMPLE 1
CUSTOMER CATEGORY CAD SALES USD SALES USD FX
A CAD 100.00 - -
B USD 200.00 20.00
C NOD - - -


EXAMPLE 2
CUSTOMER CATEGORY CAD SALES USD SALES USD FX
A CAD 100.00 - -
B CAD 1.00 200.00 20.00
C NOD - - -


Thanks
SJ

Hello once again.

The table shown below is an amalgamation of data from two different tables, namely, billing and cost. Therefore, the lines containing the sales value would not have the cost of sales showing on the same line and vice versa.

I need to get a formula for the field 'CATEGORY'. The formula IF(D20,"Customer billed in USD",IF(C20,"Customer billed in CAD","Customer not billed")) works well when considering only the sales fields such as CAD SALES and USD SALES. However, I am not sure what to add in the formula to count the lines with only the cost of sales.

Explanation:
Invoice # 300 for customer ABC against work order # 29775 is clearly a Canadian dollar invoice, so the category will be 'Customer billed in CAD'.
Invoice # 320 for customer EFG against work order # 25000 is clearly a USD invoice, so the category will be 'Customer billed in USD'.
Cost of sales for work order 26000 for customer XYZ should show 'Customer not billed' as there is cost value but no billing as yet.
Cost of sales for work order 30000 for customer PQR should show 'Customer not billed' as there NO cost and NO billing as yet.
Cost of sales for work orders 29775 and 29800 for customer ABC should show 'Customer billed in CAD' because up above customer ABC has been assigned a category due to billing.
Cost of sales for work order 27000 for customer EFG should show 'Customer billed in USD', because up above the customer has been assigned a category due to billing.

In a nut shell, if a customer has been been billed, in which case the formual would assign a category, then all subsequent transactions for that customer should have the same category. And if a customer has only or not cost but no billing, then the category should be "Customer not billed'.

BEFORE
CUSTOMER WORK ORDER# INVOICE # CATEGORY CAD SALES USD SALES USD FX COST OF SALES
ABC 29775 300 100.00
EFG 25000 320 200.00 20.00
XYZ 26000 20.00
PQR 30000
ABC 29775 40.00
EFG 27000 150.00
ABC 29800 40.00


AFTER
CUSTOMER WORK ORDER# INVOICE # CATEGORY CAD SALES USD SALES USD FX COST OF SALES
ABC 29775 300 Customer billed in CAD 100.00
EFG 25000 310 Customer billed in USD 200.00 20.00
XYZ 26000 Customer not billed 20.00
PQR 30000 Customer not billed
ABC 29775 Customer billed in CAD 40.00
EFG 27000 Customer billed in USD 150.00
ABC 29800 Customer billed in CAD 40.00


Thanks in advance
SJ
"SSJ" wrote in message ...
Hello,

By using the following conditional formula, I am able to get the category needed based on the sales. The sales are mutually exclusive. In other words, if there is a value under CAD SALES then USD SALES & USD FX will be either zero or blank. And if the USD SALES is filled then CAD SALES will be either zero or blank. And if all three sales fields are empty then the category should be "NOD".

I would like to incorporate this mutually exclusiveness in my formula. Please see EXAMPLE 2, customer 'B', though this is a USD customer but because the there is a value under CAD SALES, it is giving an incorrect category.

IF(C20,"CAD",IF(D20,"USD","NOD"))

EXAMPLE 1
CUSTOMER CATEGORY CAD SALES USD SALES USD FX
A CAD 100.00 - -
B USD 200.00 20.00
C NOD - - -


EXAMPLE 2
CUSTOMER CATEGORY CAD SALES USD SALES USD FX
A CAD 100.00 - -
B CAD 1.00 200.00 20.00
C NOD - - -


Thanks
SJ

How about this

=(IF($E30,"Customer billed in CAD",IF($F30,"Customer billed in USD",
IF(ISNUMBER(MATCH($A3,$A$2:$A2,0)),INDEX($D$2:$D2, MATCH($A3,$A$2:$A2,0)),"Customer not billed"))))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"SSJ" wrote in message ...
Hello once again.

The table shown below is an amalgamation of data from two different tables, namely, billing and cost. Therefore, the lines containing the sales value would not have the cost of sales showing on the same line and vice versa.

I need to get a formula for the field 'CATEGORY'. The formula IF(D20,"Customer billed in USD",IF(C20,"Customer billed in CAD","Customer not billed")) works well when considering only the sales fields such as CAD SALES and USD SALES. However, I am not sure what to add in the formula to count the lines with only the cost of sales.

Explanation:
Invoice # 300 for customer ABC against work order # 29775 is clearly a Canadian dollar invoice, so the category will be 'Customer billed in CAD'.
Invoice # 320 for customer EFG against work order # 25000 is clearly a USD invoice, so the category will be 'Customer billed in USD'.
Cost of sales for work order 26000 for customer XYZ should show 'Customer not billed' as there is cost value but no billing as yet.
Cost of sales for work order 30000 for customer PQR should show 'Customer not billed' as there NO cost and NO billing as yet.
Cost of sales for work orders 29775 and 29800 for customer ABC should show 'Customer billed in CAD' because up above customer ABC has been assigned a category due to billing.
Cost of sales for work order 27000 for customer EFG should show 'Customer billed in USD', because up above the customer has been assigned a category due to billing.

In a nut shell, if a customer has been been billed, in which case the formual would assign a category, then all subsequent transactions for that customer should have the same category. And if a customer has only or not cost but no billing, then the category should be "Customer not billed'.

BEFORE
CUSTOMER WORK ORDER# INVOICE # CATEGORY CAD SALES USD SALES USD FX COST OF SALES
ABC 29775 300 100.00
EFG 25000 320 200.00 20.00
XYZ 26000 20.00
PQR 30000
ABC 29775 40.00
EFG 27000 150.00
ABC 29800 40.00


AFTER
CUSTOMER WORK ORDER# INVOICE # CATEGORY CAD SALES USD SALES USD FX COST OF SALES
ABC 29775 300 Customer billed in CAD 100.00
EFG 25000 310 Customer billed in USD 200.00 20.00
XYZ 26000 Customer not billed 20.00
PQR 30000 Customer not billed
ABC 29775 Customer billed in CAD 40.00
EFG 27000 Customer billed in USD 150.00
ABC 29800 Customer billed in CAD 40.00


Thanks in advance
SJ
"SSJ" wrote in message ...
Hello,

By using the following conditional formula, I am able to get the category needed based on the sales. The sales are mutually exclusive. In other words, if there is a value under CAD SALES then USD SALES & USD FX will be either zero or blank. And if the USD SALES is filled then CAD SALES will be either zero or blank. And if all three sales fields are empty then the category should be "NOD".

I would like to incorporate this mutually exclusiveness in my formula. Please see EXAMPLE 2, customer 'B', though this is a USD customer but because the there is a value under CAD SALES, it is giving an incorrect category.

IF(C20,"CAD",IF(D20,"USD","NOD"))

EXAMPLE 1
CUSTOMER CATEGORY CAD SALES USD SALES USD FX
A CAD 100.00 - -
B USD 200.00 20.00
C NOD - - -


EXAMPLE 2
CUSTOMER CATEGORY CAD SALES USD SALES USD FX
A CAD 100.00 - -
B CAD 1.00 200.00 20.00
C NOD - - -


Thanks
SJ

Bob,

Thanks for the response.

Based on your formula from yesterday, =(IF($E30,"CAD",IF($F30,"USD",IF(ISNUMBER(MATCH( $A3,$A$2:$A2,0)),INDEX($D$2:$D2,MATCH($A3,$A$2:$A2 ,0)),"NOD")))) , following is the outcome, which did not come out correct.

I shortned the category text to CAD, USD & NOD to make it easy for now.

CUSTOMER WORK ORDER# INVOICE # CATEGORY CAD SALES USD SALES USD FX COST OF SALES
ABC 29775 300 USD 100
EFG 25000 320 NOD 200 20
XYZ 26000 NOD 20
PQR 30000 USD
ABC 29775 NOD 40
EFG 27000 USD 150
ABC 29800 NOD 40


SJ
"Bob Phillips" wrote in message ...
How about this

=(IF($E30,"Customer billed in CAD",IF($F30,"Customer billed in USD",
IF(ISNUMBER(MATCH($A3,$A$2:$A2,0)),INDEX($D$2:$D2, MATCH($A3,$A$2:$A2,0)),"Customer not billed"))))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

I think that you didn't adjust the line number. Try

=(IF($E20,"CAD",IF($F20,"USD",IF(ISNUMBER(MATCH( $A2,$A$1:$A1,0)),INDEX($D$1:$D1,MATCH($A2,$A$1:$A1 ,0)),"NOD"))))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"SSJ" wrote in message ...
Bob,

Thanks for the response.

Based on your formula from yesterday, =(IF($E30,"CAD",IF($F30,"USD",IF(ISNUMBER(MATCH( $A3,$A$2:$A2,0)),INDEX($D$2:$D2,MATCH($A3,$A$2:$A2 ,0)),"NOD")))) , following is the outcome, which did not come out correct.

I shortned the category text to CAD, USD & NOD to make it easy for now.

CUSTOMER WORK ORDER# INVOICE # CATEGORY CAD SALES USD SALES USD FX COST OF SALES
ABC 29775 300 USD 100
EFG 25000 320 NOD 200 20
XYZ 26000 NOD 20
PQR 30000 USD
ABC 29775 NOD 40
EFG 27000 USD 150
ABC 29800 NOD 40


SJ
"Bob Phillips" wrote in message ...
How about this

=(IF($E30,"Customer billed in CAD",IF($F30,"Customer billed in USD",
IF(ISNUMBER(MATCH($A3,$A$2:$A2,0)),INDEX($D$2:$D2, MATCH($A3,$A$2:$A2,0)),"Customer not billed"))))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)