IF Formula OR + AND

[gazmoz17]

Hi, been trying this for hours!!

Can you write OR then AND or do you write it AND then OR. The logic to me in this instance to return correct val would be OR then AND.

Trying Translate Sage (Accountancy software) syntax into excel IF formula so can pull it direct via a query instead of exporting from Sage. Report and IF forumlas work in Sage.

Sage (working expression) ;green



“?” Then

“:” Else



INVOICE_ITEM.UNIT_OF_SALE="EA" OR INVOICE_ITEM.UNIT_OF_SALE="EACH" OR INVOICE_ITEM.UNIT_OF_SALE="Each"OR INVOICE_ITEM.UNIT_OF_SALE="EA"OR INVOICE_ITEM.UNIT_OF_SALE="EA"OR INVOICE_ITEM.UNIT_OF_SALE="ea"?INVOICE_ITEM.QUANTITY:
STOCK.STOCK_CODE="4116" OR STOCK.STOCK_CODE="4117"?INVOICE_ITEM.QUANTITY/STOCK.UNIT_WEIGHT:INVOICE_ITEM.UNIT_OF_SALE="KG"OR INVOICE_ITEM.UNIT_OF_SALE="Kg" OR INVOICE_ITEM.UNIT_OF_SALE="kg" AND STOCK.UNIT_WEIGHT="0.000"? INVOICE_ITEM.QUANTITY/30:INVOICE_ITEM.QUANTITY/STOCK.UNIT_WEIGHT



Broke it down into sections (then tested formula works for section before combining overall).

*Cant get section 3 to work?....the AND OR combined.

Blue = excel.



1. INVOICE_ITEM.UNIT_OF_SALE="EA" OR INVOICE_ITEM.UNIT_OF_SALE="EACH" OR INVOICE_ITEM.UNIT_OF_SALE="Each"OR INVOICE_ITEM.UNIT_OF_SALE="EA"OR INVOICE_ITEM.UNIT_OF_SALE="EA"OR INVOICE_ITEM.UNIT_OF_SALE="ea"?INVOICE_ITEM.QUANTITY

=IF(OR(INVOICE_ITEM.UNIT_OF_SALE="EA",INVOICE_ITEM.UNIT_OF_SALE="ea",INVOICE_ITEM.UNIT_OF_SALE="Ea",INVOICE_ITEM.UNIT_OF_SALE="each",INVOICE_ITEM.UNIT_OF_SALE="EACH",INVOICE_ITEM.UNIT_OF_SALE="Each"), INVOICE_ITEM.QUANTITY,"")

2. :STOCK.STOCK_CODE="4116" OR STOCK.STOCK_CODE="4117"?INVOICE_ITEM.QUANTITY/STOCK.UNIT_WEIGHT:

=IF(OR(STOCK.STOCK_CODE="4116",STOCK.STOCK_CODE="4117"),(INVOICE_ITEM.QUANTITY*STOCK.UNIT_WEIGHT),"")

3. INVOICE_ITEM.UNIT_OF_SALE="KG"OR INVOICE_ITEM.UNIT_OF_SALE="Kg" OR INVOICE_ITEM.UNIT_OF_SALE="kg" AND STOCK.UNIT_WEIGHT="0.000"? INVOICE_ITEM.QUANTITY/30:

INVOICE_ITEM.UNIT_OF_SALE="KG"OR INVOICE_ITEM.UNIT_OF_SALE="Kg" OR INVOICE_ITEM.UNIT_OF_SALE="kg" AND STOCK.UNIT_WEIGHT="0.000"? INVOICE_ITEM.QUANTITY/30
=IF(AND([@[UNIT_WEIGHT]]="0",OR([@[UNIT_OF_SALE]]="KG",[@[UNIT_OF_SALE]]="kg",[@[UNIT_OF_SALE]]="Kg",[@[UNIT_OF_SALE]]="kG")), [@QUANTITY]/30, "")

; not returning

4. INVOICE_ITEM.QUANTITY/STOCK.UNIT_WEIGHT


(The fields in my table are diff to the data fields in sage but I’ve just matched them (find/replace for understanding….if anyone is willing to help ?) Will prob need help combining all sections as well if possible!



Many Thanks

 

[GlennUK]

It would be easier to help if you translated what the Sage means into English. But your tests of OR for different character cases is not necessary in Excel:

Book1 (version 2).xlsb
	A	B
1	String	TEST KG
2	kg	TRUE
3	Kg	TRUE
4	KG	TRUE
5	kG	TRUE
Sheet11

Cell Formulas
Range	Formula
B2:B5	B2	=A2="KG"