I have a table with amongst others the fields "Type" (which contains amongst others the entries 'Piece' and 'Hours'), "Payroll" (which contains 'Hourly' and 'Monthly') "Account" (which contains 'F040', 'F0950', 'N0100' amongst others), "Value" (which contains a value), "Postings" (which contains a number value), "Pay" which contains hourly payment.
I am trying to build a logic like this: IF ([Type] = 'Piece' , [Posting]*[Value], IF=[Type] = 'Hours', IF([Payroll]='Monthly' AND Match([Account],'F040', 'F0950', 'N0100'),0,[Posting]*[Value]*[Pay]) ,0) )
Basically if Type = Piece, just calulate Posting with Value. IF Type = 'Hours' it needs to first check if Payroll is 'Monthly' and then set it to 0 if the account matches the list given. For the rest of the type 'Hours' it will calculate as per the forumla.
If type is not 'Piece' or 'Hours' it should set value to 0.
I first tried using OR instead of the MATCH, but figured that might be wrong.
The error I keep getting is "Missing right paranthesis"...
If I use the forumla as a master item (measure) in a sheet it works fine, but if I try to create a calculated field it does not work (it seems to hang up on the "AND" statement and gives the paranthesis error)