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