Formula Assistance

Community Boss

Re: Formula Assistance

First impression here is that you formula is far to long. Break out your formula across more line items.

Secondly, you are using hard coded numbers which really should be coming from an input module or a list. From the nature of what you appear to be trying to do the Dept Code should be a list. This should either be a property of the employee list or a list formatted line item in a system module for employee details. Create an input module containing the department code list as a dimension and benefit rate as a line item. In your target module which should be dimensioned by the employee list use LOOKUP to pull the rate through per employee dependent on the department. The LOOKUP should reference the department property of the employee list or list formatted line item of the employee details module.

There are a few steps required but in summary;

1 Create a new list of department codes.
2 Create a property of the employee list or list formatted line item in an employees details system module.
3 Create a new module containing the departments list containing one line item contain the fringe benefit rate
4 Use LOOKUP in your target module to take the rate relevant to the employees department querying the employee list property or list formnatted line item

Where you are trying to build a formula with more than two queries this should raise alarm bells and prompt you to seek out a simpler solution.

Hope this helps.

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA